Open subform to specific record

[Sally]

My database has TblCustomers - CustomerID, Name, etc and TblOrders - OrderID,
CustomerID, OrderDate, etc. I have a form/subform based on TblCustomers and
TblOrders.

I also have a continuous form that lists the orders for a selected customer.
This form has the CustomerID and the OrderID for each order displayed. I want to
be able to click on an order and open the form/subform to that order. I can use
the Where parameter in the OpenForm method to open the main form to the customer
but how do I get the subform to open to the order I clicked on?

Thanks!

Sally

 

[Allen Browne]

Search the RecordsetClone of the subform for the order.
Then set the form's bookmark to that of the clone set to display it.
For JET tables, this code needs a reference to the DAO library.

Dim frm As Form 'Reference to the subform.

DoCmd.OpenForm "frmCustomer", _
WhereCondition:= "CustomerID = " & Me.CustomerID

Set frm = Forms!frmCustomer!frmOrder.Form
With frm.RecordsetClone
.FindFirst "OrderID = " & Me.OrderID
If .NoMatch Then
MsgBox "not found"
Else
frm.Bookmark = .Bookmark
End If
End With
Set frm = Nothing