Open subform to specific record

  • Thread starter Thread starter Sally
  • Start date Start date
S

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
 
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
 
Back
Top