Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.
Option Compare Database Option Explicit Dim rsContacts As ADODB.Recordset Dim cnCh5 As ADODB.Connection Dim strConnection As String 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 With rsContacts .CursorType = adOpenStatic 'open the recordset based on tblContacts table using the existing connection .Open "tblContacts", CurrentProject.Connection End With '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" Else 'record was found - display some info Call PopulateControlsOnForm 'Debug.Print "Contact Id: " & rsContacts!intContactId & _ ' " Last Name: " & rsContacts!txtLastName & _ ' " First Name: " & rsContacts!txtFirstName End If 'close the recordset rsContacts.Close 'set the recordset and connection to nothing Set rsContacts = Nothing End Sub 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 rsContacts.MoveNext ElseIf rsContacts.EOF Then 'past end of recordset so move back to previous record rsContacts.MovePrevious End If End Sub Toggle HighlightingOpen in New Window