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

  • Thread starter Thread starter John Baker
  • Start date Start date
J

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)
 
-----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
Click to expand...
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
 
Back
Top