Finding a record on a form using a value from a subform

[John Baker]

I would like to be able to find a record on a form using a value from a
subform.
The sub form contains records consisting of amongst other things a list of
primary key values of the main form. I would like to select a primary key
value from the subform and have the main form go to that record.
Preferably I would like to put the code behind a command button located in
the detail section of each record in the subform ( or perhaps to be able to
dbl click the textbox containing the primary key value)


Any help greatly appreciated.

John Baker
(e-mail address removed)

 

[tina]

-----Original Message-----
I would like to be able to find a record on a form using a value from a
subform.
The sub form contains records consisting of amongst other things a list of
primary key values of the main form. I would like to select a primary key
value from the subform and have the main form go to that record.
Preferably I would like to put the code behind a command button located in
the detail section of each record in the subform ( or perhaps to be able to
dbl click the textbox containing the primary key value)


Any help greatly appreciated.

John Baker
(e-mail address removed)


.
i had to do the very same thing just 2 months ago. the

code below works when the value you're searching on is an
integer. for text, you'll have to change the variable to a
string, and change the Rst.FindFirst syntax to handle text.


Private Sub NameA_Click()

' click in the subform Name control to find the main
' form's record that matches the control.

On Error GoTo HANDLE_ERROR

Dim Rst As Object, intSelection As Integer

intSelection = Me!SubFormValue

With MainForm.Form
Set Rst = .Recordset.Clone
Rst.FindFirst "MainFormPrimaryKey = " &
intSelection
.Bookmark = Rst.Bookmark
End With

LAST_EXIT:
Exit Sub

HANDLE_ERROR:
If Err.Number = 94 Then
Resume LAST_EXIT
Else
Beep
MsgBox Err.Number & " " & Err.Description
Resume LAST_EXIT
End If

End Sub

 

[John Baker]

Thankyou very much for your help Tina. I got it working.

John.