Typically, a subform has only the records that match the record in the main
form. Therefore record you are searching for is probably not even present in
the subform. You therefore have to find the parent record in the main form,
and then find the record you are after in the subform.
The following example assumes:
- A main form bound to a invoice table.
Primary key = InvoiceID.
- A subform showing the line items for the invoice.
Primary key is InvoiceDetailID.
Foreign key is InvoiceID.
- The main form has a text box where you enter the InvoiceDetailID that you
want to find in the subform.
The code fires as soon as you entere the InvoiceDetailID number you want.
It performs 3 steps:
1) Look up the InvoiceID.
2) Fetch that record in the main form, so the record loads into the subform.
3) Highlight the desired record in the subform.
------------------------code starts---------------------------
Private Sub txtFindInvoiceDetail_AfterUpdate()
Dim strWhere As String
Dim varResult As Variant
Dim rs As DAO.Recordset
If not IsNull(Me.txtFindInvoiceDetail) Then
' 1). Lookup the InvoiceID that matches this InvoiceDetailID.
strWhere = "InvoiceDetailID = " & Me.txtFindInvoiceDetail
varResult = DLookup("InvoiceID", "tblInvoiceDetail", strWhere)
If IsNull(varResult) Then
MsgBox "No such invoice detail number."
Else
' 2). Find the record in the main form
Set rs = Me.RecordsetClone
rs.FindFirst "InvoiceID = " & varResult
If rs.NoMatch Then
MsgBox "Not found. Main form filtered?"
Else
Me.Bookmark = rs.Bookmark
' 3). Find the record in the subform
Set rs = Nothing
Set rs = Me.[frmInvoiceDetail].Form.RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not found. Subform filtered?"
Else
Me.[frmInvoiceDetail].Form.Bookmark = rs.Bookmark
End If
End If
End If
End If
Set rs = Nothing
End Sub
------------------------code ends---------------------------
