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

Posted on 2011-10-24
Last Modified: 2012-05-12
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.
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"
'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

'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
    ElseIf rsContacts.EOF Then
        'past end of recordset so move back to previous record
    End If

End Sub

Open in new window

Question by:RiCzN
    LVL 44

    Accepted Solution


    In this case you really shouldn't be searching like this.
    Opening the table to then perform a Find is much too wasteful.  Select only what you need.
    Instead of
        .Open "tblContacts", CurrentProject.Connection
    End With
    'find a contact with the intContactId value of 2
    rsContacts.Find "[intContactId] = " & Me.txtFilterId

    Open in new window

    You'd have
        .Open "SELECT * FROM tblContacts WHERE "[intContactId] = " & Me.txtFilterId, CurrentProject.Connection
    End With

    Open in new window

    Your BOF/EOF checking would still be more or less right, but there's no need for the MoveNext/MovePrevious calls in your assignment procedure. (They do nothing as it is anyway.)

    Then, of course, the root of your problem is two fold.  Firstly, you don't have Option Explicit in the header of your module.  Otherwise this issue would have come to light for you already.  Secondly - you need to make rsContacts declared at the module level (in the declarations section - beneath the newly entered Option Explicit ;-) or pass it as a parameter to the PopulateControlsOnForm procedure.

    LVL 44

    Expert Comment

    by: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

    LVL 9

    Expert Comment

    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.

    Author Closing Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now