Change a form's recordset when command button clicked Access 2013 vba

Question

I've not done any vba for years and totally forgotten how to do this - it's like learning vba all over again!

I have a form which has an "on open" event to populate data from an ado recordset.

What I am trying to do is provide some text boxes at the top to allow the user to enter some criteria and change the recordset they are viewing.

Here's my "on open" code;

Private Sub Form_Open(Cancel As Integer)
qString = "SELECT tblPerson.personID, [personLName]+', '+[personFName] AS fullName, tblFamily.familyAFCID, tblAddress.addressLine1, " & _
        " tblFamily.personAFCID, refSource.sourceDescription AS personSource " & _
        " FROM refSource RIGHT JOIN (((tblPerson LEFT JOIN tblFamily ON tblPerson.personID = tblFamily.personID) " & _
        " LEFT JOIN lnkAddressPerson ON tblPerson.personID = lnkAddressPerson.personID) " & _
        " LEFT JOIN tblAddress ON lnkAddressPerson.addressID = tblAddress.addressID) ON refSource.sourceID = tblPerson.personSource " & _
        " WHERE lnkAddressPerson.addresslinkend is null "
qStringAppend = " order by personLName, personFName;"

bindFormData qString, qStringAppend, "frmPerson"

End Sub

And bindFormData sub is;

Public Sub bindFormData(ByVal qString As String, ByVal qStringAppend As String, ByVal formName As String)

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = CurrentProject.AccessConnection
Set rs = New ADODB.Recordset
With rs
 Set .ActiveConnection = cn
     .Source = qString + qStringAppend
     .LockType = adLockBatchOptimistic
     .CursorType = adOpenKeyset
     .CursorLocation = adUseClient

     .Open
End With
Set Forms(formName).Recordset = rs

Set rs = Nothing
Set cn = Nothing

End Sub

Here's what I am trying to do on the command button;

Private Sub cmdApplyFilter_Click()

Dim qStringAppend As String, qString As String

qStringAppend = ""

If IsNull(Me.txtNameFilter) = False Then
qStringAppend = qStringAppend + " and (lcase(personFname) like '*" & LCase(Me.txtNameFilter) & "*' or lcase(personLName) like '*" & LCase(Me.txtNameFilter) & "*' )"

End If

qString = "SELECT tblPerson.personID, [personLName]+', '+[personFName] AS fullName, tblFamily.familyAFCID, tblAddress.addressLine1, " & _
        " tblFamily.personAFCID, refSource.sourceDescription AS personSource " & _
        " FROM refSource RIGHT JOIN (((tblPerson LEFT JOIN tblFamily ON tblPerson.personID = tblFamily.personID) " & _
        " LEFT JOIN lnkAddressPerson ON tblPerson.personID = lnkAddressPerson.personID) " & _
        " LEFT JOIN tblAddress ON lnkAddressPerson.addressID = tblAddress.addressID) ON refSource.sourceID = tblPerson.personSource " & _
        " WHERE lnkAddressPerson.addresslinkend is null "

qStringAppend = qStringAppend + " order by personLName, personFName;"
bindFormData qString, qStringAppend, "frmPerson"

Me.Repaint

End Sub

My form populates correctly on the open event, but returns zero records on the command button click. I have debug.print the sql on the command button click and verified that it returns records in a query.

Any help appreciated.

Answer 1

Urrgh, how annoying - in VBA with an ADO recordset I needed to use % rather than * as a wildcard...... simple as that!

Answer 2

Some thoughts

Changes:

Public Sub bindFormData(ByVal qString As String, ByVal qStringAppend As String, ByVal formName As String)
to
Public Sub bindFormData(ByVal qString As String, ByVal qStringAppend As String, ByVal form as Form)

Set Forms(formName).Recordset = rs
to
Set form.Recordset = rs

bindFormData qString, qStringAppend, "frmPerson"
to 
bindFormData qString, qStringAppend, me.form 

OR if you keep your existing code use this (in case you rename the form) 
bindFormData qString, qStringAppend, me.form.Name 

Write a function to build and return the SQL text that is common between the command button and the open form (which is most!).

AND use the technique I outline in the answer here to make the SQL more readable.

here's your SQL read to use:

Public Sub aa(ByRef a As String, ByVal b As String)
    ' This is deliberately not a function, although it behaves a bit like one
    a = a & vbCrLf & b

End Sub


' The function is called in code like this:

Dim a as string 

a = ""
aa a,  "    SELECT tblPerson.personID "
aa a,  "         , [personLName]+', '+[personFName] AS fullName "
aa a,  "         , tblFamily.familyAFCID "
aa a,  "         , tblAddress.addressLine1 "
aa a,  "         , tblFamily.personAFCID "
aa a,  "         , refSource.sourceDescription AS personSource "
aa a,  "      FROM refSource "
aa a,  "           RIGHT JOIN (((tblPerson "
aa a,  "                       LEFT JOIN tblFamily "
aa a,  "                              ON tblPerson.personID = tblFamily.personID) "
aa a,  "                       LEFT JOIN lnkAddressPerson "
aa a,  "                              ON tblPerson.personID = lnkAddressPerson.personID) "
aa a,  "                       LEFT JOIN tblAddress "
aa a,  "                              ON lnkAddressPerson.addressID = tblAddress.addressID) "
aa a,  "                   ON refSource.sourceID         = tblPerson.personSource "
aa a,  "     WHERE lnkAddressPerson.addresslinkend IS NULL"