asked on

MS Access 2007 ADO Navigation Issue - Cannot move to required record

Hi Experts,
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.
Rick

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

Open in new window

ASKER CERTIFIED SOLUTION

Leigh Purvis

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Leigh Purvis

Grrr I wish EE would allow editing posts. I dislike imperfection being left forever!

    .Open "SELECT * FROM tblContacts WHERE [intContactId] = " & Me.txtFilterId, CurrentProject.Connection

Open in new window

Ramanhp

well, the only missing thing is that you require to write code to populate the records once again after you have written the code for moveprevioius once you reach eof.

Ricky Nguyen

ASKER

Thank you very much LPurvis!! I will put through the changes.