Button to open form and find record

[adams937]

Hello,
I'm relatively new to access/VBA, and am trying to use a button to open a new form (Form2). When the new form is opened, I'd like to find the same record that was displayed on the original form (Form1). Both Form1 and Form2 use a combo box with form/subform to select records. The VBA for this combo box is below.

I'm currently using a control button with embedded macro to open Form2, but am having trouble setting the control to also find the appropriate record.

Here's the VBA used on the combo box for Form1 and Form2:
Private Sub Combo165_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![Combo165], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Combo165 = Null
End Sub


Thanks for any suggestions!

 

[adams937]

Thanks Bob. I now have my combo box working properly to select records, but am still not clear on how to set the "On click" property of a button to open Form2 and find the same record that was displayed on Form1.

I currently have an embedded macro to open the new form "on click", but could use a good example of how to find the desired record.

Thanks everyone.

 

[adams937]

Almost there....

This now navigates to the new form and finds the record as desired. However, the combo box no longer works to select records. It appears the ID field is being over-ridden by the previous script. Any ideas what's going on?

 

[adams937]

Here's an attempt at describing the forms.

Form1:
--Has a combo box to select records from Source A (index = "storeID") and displays a subform connected to the same datasource (index = "storeID")
--Has a button to open Form2

Form2:
--Has a combo box to select records from Source A (index = "storeID") and displays a subform.
--Subform is a different dataset with (child index = "customerID"). The parent/child relationship is storeID joined to CustomerID.

In the script above, I have set:

DoCmd.OpenForm "Form2", acNormal, , "[StoreID] = " & Me!StoreID

 

[adams937]

That did the trick! Thanks for the great link.

 

[ngsabath93]

I am experiencing the same problem. How would I follow that link if my ID is text, not numeric?