Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2011-10-24
Medium Priority
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. Below is a snippet of my overall codes. Please ignore best practices. I'm just learning how to navigate using ADO and its interaction with objects.

Why isn't my recordset.find (line 21) and then populate form  (line 28) 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.
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"
'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 

Toggle HighlightingOpen in New Window

Open in new window

  • 2
  • 2
LVL 77

Expert Comment

ID: 37022903
Using ADO for practice is fine.  But be aware that ADO became a 'dormant' technology in Access with the release of Access 2007. Anything you could do with ADO before A2007 is still available of course but the new features of A2007 and A2010 are only available via DAO.

LVL 14

Expert Comment

by:Bill Ross
ID: 37023530

I think you should use a bound form here.  There is no need to use ADO or DAO to get data from the curretn db.  Pelase post your db and I'll show you how.  It's way simpler.



Author Comment

by:Ronniel Allan Castanito
ID: 37028818
@pete57r - Huh? I thought DAO was the redundant one and everything was moving on to ADO? Did they change it back?

Bill: Thanks for your suggestion, but I'm trying to learn how an unbound form would work in a multi-user environment.

LVL 77

Accepted Solution

peter57r earned 2000 total points
ID: 37029580
ADO was pushed as a replacement for DAO by MS when Access2000 was released.  It never found favour amongst Access developers for dealing with native Access/JET data although it is used for programatically  handling other data sources within an Access  application.  MS gradually shifted back to DAO and A2003 re-instated DAO as the default technology.  There have always been a couple of SQL commands which can only be actioned via ADO so ADO might still appear in some applications.

The new features of A2007, such as recordset2 to handle multi-valued fields are only implemented in DAO which is now part of the 'Access Datatabase Engine' object library.  So for A2007/2010 there is no longer a separate reference for DAO as there was in previous versions.

Author Closing Comment

by:Ronniel Allan Castanito
ID: 37060740
Thanks very much

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

810 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