You need to understand that a linked subform's recordset is not all rows from the table to which it is bound, but the subset of rows which reference the parent form's current record. Consequently if you search for an order which is not related to
the parent form's current record, the order will not be found. As I said in my earlier reply, the way to do it is to build a subquery in the code which returns a set of foreign key values where OrderNum matches the search criterion, and search for those
rows in the parent form's recordset whose primary key is in this subset of foreign key values.
If each row in the subform's recordset relates to only one row in the parent form's recordset, then you can navigate to that row by calling the FindFirst method of the parent form's recordset object. If each row in the subform's can relate to one or more
rows in the parent form's recordset, i.e. the subform's table is modelling a many-to-many relationship type, then you need to filter the parent form to those rows whose keys are in the subset of rows returned by the subquery. My FindRecord demo, to which
I referred you earlier, does the latter, though it would also work in the former scenario, where each row in the subform's recordset relates to only one row in the parent form's recordset. The code in my demo to filter only the parent form is:
Const MESSAGETEXT = "No matching records found."
Dim ctrl As Control
Dim strFilter As String
Set ctrl = Me.ActiveControl
strFilter = "ProjectID IN(SELECT ProjectID " & _
"FROM ContactProjects WHERE ContactID = " & ctrl & ")"
If Nz(ctrl, 0) = 0 Then
' turn off filter
Me.FilterOn = False
Else
If Not IsNull(DLookup("ContactID", "ContactProjects", "ContactID = " & ctrl)) Then
' filter form to name selected in combo box
Me.Filter = strFilter
Me.FilterOn = True
Else
' inform user if no matching records found and show all records
MsgBox MESSAGETEXT, vbInformation, "Warning"
Me.FilterOn = False
Me.Requery
End If
End If
Additionally the following code in the parent form's Current event procedure moves the record pointer in the subform to the matching record if the parent form's unbound cboLastname control, in which the search criterion is selected, is not Null:
' select contact in subform if combo box is not Null
If Not IsNull(Me.cboLastname) Then
With Me.fsubContacts.Form
.RecordsetClone.FindFirst "ContactID = " & Me.cboLastname
If Not .RecordsetClone.NoMatch Then
.Bookmark = .RecordsetClone.Bookmark
End If
End With
End If
The code which also filters the subform to the matching record is:
Const MESSAGETEXT = "No matching records found."
Dim ctrl As Control
Dim strFilter As String
Set ctrl = Me.ActiveControl
strFilter = "ProjectID IN(SELECT ProjectID " & _
"FROM ContactProjects WHERE ContactID = " & ctrl & ")"
If Nz(ctrl, 0) = 0 Then
' turn off filter
Me.FilterOn = False
Me.fsubContacts.Form.FilterOn = False
Else
If Not IsNull(DLookup("ContactID", "ContactProjects", "ContactID = " & ctrl)) Then
' filter form to name selected in combo box
Me.Filter = strFilter
Me.FilterOn = True
' filter subform to selected contact
Me.fsubContacts.Form.Filter = "ContactID = " & ctrl
Me.fsubContacts.Form.FilterOn = True
Else
' inform user if no matching records found and show all records
MsgBox MESSAGETEXT, vbInformation, "Warning"
Me.FilterOn = False
Me.Requery
Me.fsubContacts.Form.FilterOn = False
End If
End If
In this case no code is needed in the parent form's Current event procedure to move the record pointer of course, as the subform will be filtered to only one row.
Ken Sheridan, Stafford, England
