Go to a specific record on a different form

[gundie]

I have a main form (Form A), and I want to create a Search form (Form B). The Search form functions like a filter, that lets me filter down to a subset of records, based on criteria, that displays on Form B in a table via a subform. After I found the record that I'm looking for in Form B, I want to be able to double-click on the record, and the corresponding record is displayed in Form A.

How can I accomplish that? What do I need in the double-click event? Hope I'm making sense...

 

[Remou]

You can use the recordset.

[tt]Forms!frmA.Recordset.FindFirst "UniqueNumericID=" & Me.UniqueNumericID[/tt]

If you have a text identifier, you will need to use single quotes.

 

[gundie]

Tried your suggestion, but got the following message:

Object doesn't support this property or method.

 

[Remou]

Try the recordsetclone:

'Needs reference to the Microsoft DAO 3.x Object Library

Dim rs As DAO.Recordset
Set rs = Forms!frmA.RecordsetClone

'Me!UniqueNumericID is the name of the field or control 
'on form B
rs.FindFirst "UniqueNumericID=" & Me!UniqueNumericID

If Not rs.NoMatch Then
    'Found
    Forms!frmA.Bookmark = rs.Bookmark
End If

 

[PHV]

Object doesn't support this property or method
Seems like your formA isn't bound ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bubba100]

Take a look at thread702-1427250 and faq702-777.

 

[gundie]

The table is not bound to Form A, but it's bound to another form (Form Rec), and this Form Rec is a subform within Form A.

Still can't get it work...

 

[BJZeak]

I use the following method for filtering:

I add a Double click sub or an ok button in the Subform of Form B that calls a public sub/function in Form A ... called with:

Me.Parent.<your form a public sub/function name> (<Primary key(s) of selected record>)

Add a public Sub/function in Form A that takes the values you have passed and refreshes Form A

example I have a GetDate button on Form A that opens up Form B ... The Date is returned to Form A by calling Me.Parent.subRefreshDate(daDate)

Public Sub subRefreshDate(daDate as Date)
Me!txtDate.value = format(daDate,"dddd - mmmm d, yyyy")
Me.requery
Me!sbfMain.requery ' if you have a subform on Form A
end Sub

 

[Remou]

Are you saying that orm A does not have any recordset? If so, you will need to set the recordsource based on the selection in Form B.