Return/display multiple mainform records after search in subform?

R

Ricter

I've adapted the following code, copied from Allen Browne's reply to this
thread, "Best way to handle Find in Subform", to my database:

Private Sub txtFindOrder_AfterUpdate()
Dim rs As DAO.Recordset
Dim strWhere As String
Dim varResult As Variant

If Not IsNull(Me.txtFindOrder) Then
If Me.Dirty Then 'Save first
Me.Dirty = False
End If
'Lookup the customer for this order.
strWhere = "OrderID = " & Me.txtFindOrder
varResult = DLookup("CustomerID", "Orders", strWhere)
If IsNull(varResult) Then
MsgBox "No such order."
Else

'Find the record in the main form.
strWhere = "CustomerID = """ & varResult & """"
Set rs = Me.RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Customer not found. Is form filtered?"
Else
Me.Bookmark = rs.Bookmark
Set rs = Nothing

'Now find in the subform.
strWhere = "OrderID = " & Me.txtFindOrder
With Me.[Customer Orders Subform1].Form
Set rs = .RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not found in subform"
Else
.Bookmark = rs.Bookmark
End If
End With
End If
End If
End If
Set rs = Nothing
End Sub


However, this is only taking me to one record associated with the subform
field value (the first), even though there are multiple records associated
with the subform field value.

How can/should I return all records associated with a search in the subform?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top