Access form's Recordsource

Question

I'm passing in an Openargs and trying to use that as a filter for the recordsource for the form. This works fine on other forms with the same setup. For some reason though, this isn't working on this one.

Private Sub Form_Load()
Dim sSQL As String

  sSQL = "SELECT * " & _
         "FROM tblUserList " & _
         "WHERE NTID='" & OpenArgs & "';"
  RecordSource = sSQL

  If IsNull(Manager) Then
    MsgBox "Returned 0 records", vbCritical, "Problem"
    Exit Sub
  End If
End Sub

If I step through the code, the Openargs is passed in correctly...the SQL string is created correctly, I can run the string from the Query builder and I get a single record returned...as I would expect.

However, when the form loads, it returns 0 records and obviously that's a problem...I put in the IF to catch it before it hits the rest of my code.

RecordSource = SQL. That works like that? I always thought you had to specify Me.RecordSource. Try specifying Me.RecordSource. Also, you don't need the semicolon in a VBA string. — Johnny Bones
You don't need the 'me.' it just makes it easier to type with the intellisense. While the ; isn't needed...it works great with or without it in the query builder. I just can't figure out what is wrong with this. — Rdster
Only other things I can think of are that OpenArgs is coming in with leading/trailing spaces (Trim() would fix that), or NTID is an Integer (in which case, remove the single quotes surrounding OpenArgs). Other than that, it looks right to me. — Johnny Bones
It is cleaner to write Me.RecordSource, because everyone knows at once that it is the form property and not some variable. — Andre

Rdster Rdster · Accepted Answer · 2016-01-25T17:56:58

I was unable to figure out what exactly was wrong with the above form...everything should have worked. As was mentioned, that exact same setup is in use in other areas of the DB.

Ultimately I ended up creating a brand new form, and started with the code above to filter the recordset for the form and it worked just fine. I've completely rebuilt the form from scratch, and it is working as would be expected.

My best guess as to what happened is that somehow Access corrupted something in the invisible portion of the form??

When dealing with Access and random, funky issues...start over.

Access form's Recordsource

1 Answers