Forcing the main form to go to a record on the subform 1

[rew2009]

I have a form “MainForm1” and an embedded subform “SubFormCities”. The subform cities is not linked to the Main except through command buttons to filter the display. I have the subform designed to look like a list box. They are both linked to the same table. When I click on a certain field “Manager_Owner” in the subform I want the main form to go to that record as the current record. How can I do that. I currently send the ID info to a text box on the main form by by clicking the Manager_Owner field on the Subform triggering the following code:

Private Sub Manager_Owner_DblClick(Cancel As Integer)
Forms![MainForm1]![Text24] = Me.Manager_Owner.VALUE
Forms![MainCitiesForm1]![Text26] = Me.ID.VALUE
End Sub

And this works to send the info to the text boxes on the main form.

And I could write code to trigger an event on change of info in the ID text box to force the MainForm1 to go to the record with that ID. But I don’t know what code to write. There is probably and easier way to do this by somehow linking my main form to the subform but when I link my subform to the main form it only shows the one current record of the main form. So I unlink them and do it this way.

Your Thoughts? Thanks

 

[MajP]

This is untested so it will probably have some errors

in the subforms on current event or some other event something like

private Form_OnCurrent()
dim rs as dao.recordset
set rs = me.parent.recordset
rs.findfirst "someMainFormField = " & me.SomeField
'if not numeric
'rs.fidfirst "someMainFormField = '" & me.SomeField & "'"
end sub

The find first automatically moves the forms recordset to the record you find.

 

[rew2009]

MajP, I couldn't believe it, it worked the first time. This was the code I used:

Private Sub OwnerName_DblClick(Cancel As Integer)
Dim rs As dao.Recordset
Set rs = Me.Parent.Recordset
' rs.FindFirst "someMainFormField = " & Me.SomeField
rs.FindFirst "ID = " & Me.ID

End Sub

Clicking on the field "OwnerName" in the subform and matching up the "ID" fields. I have been trying to figure this one out for a long time. Thanks.