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?
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?