I'm trying to build a search button where the form would be populated based on a textbox value.
Why isn't my recordset.find (line 15) and then populate form (line 22) code working?
When i run the macro, there are no errors, but the form does not get populated with the recordset that was found. When i debug a couple of the fields on the form, it seems to be correct according to the search field ID.
Your help is appreciated.
Many thanks in advance.
Private Sub cmdFind_Click()
Dim rsContacts As ADODB.Recordset
'create a new instance of a recordset
Set rsContacts = New ADODB.Recordset
'set various properties of the recordset
.CursorType = adOpenStatic
'open the recordset based on tblContacts table using the existing connection
.Open "tblContacts", CurrentProject.Connection
'find a contact with the intContactId value of 2
rsContacts.Find "[intContactId] = " & Me.txtFilterId
'output a message to the Immediate Window regarding find results
If rsContacts.EOF Then
Debug.Print "Specified record not found"
'record was found - display some info
'Debug.Print "Contact Id: " & rsContacts!intContactId & _
' " Last Name: " & rsContacts!txtLastName & _
' " First Name: " & rsContacts!txtFirstName
'close the recordset
'set the recordset and connection to nothing
Set rsContacts = Nothing
Public Sub PopulateControlsOnForm()
'Populate the controls on the form with the values of the
'current record in the local disconnected recordset.
'Use the same field names as the tblContacts table from
'which it was generated.
If Not rsContacts.BOF And Not rsContacts.EOF Then
Me.txtLastName = rsContacts!txtLastName
Me.txtFirstName = rsContacts!txtFirstName
Me.txtMiddleName = rsContacts!txtMiddleName
Me.txtTitle = rsContacts!txtTitle
Me.txtAddress1 = rsContacts!txtAddress1
Me.txtAddress2 = rsContacts!txtAddress2
Me.txtCity = rsContacts!txtCity
Me.txtState = rsContacts!txtState
Me.txtZip = rsContacts!txtZip
Me.txtWorkPhone = rsContacts!txtWorkPhone
Me.txtHomePhone = rsContacts!txtHomePhone
Me.txtCellPhone = rsContacts!txtCellPhone
ElseIf rsContacts.BOF Then
'past beginning of recordset so move to next record
ElseIf rsContacts.EOF Then
'past end of recordset so move back to previous record