Ricky Nguyen
asked on
MS Access 2007 ADO Navigation Issue - Cannot move to required record (2)
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.
Rick
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.
Rick
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
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.
Hi,
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.
Regards,
Bill
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.
Regards,
Bill
ASKER
@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.
Thanks.
Rick
Bill: Thanks for your suggestion, but I'm trying to learn how an unbound form would work in a multi-user environment.
Thanks.
Rick
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks very much