Here is how to filter a subform and still have the record on main form

[PMrider]

In GENERAL code behind form put

Private Sub AddToWhere(FieldValue As Variant, FieldName As String, MyCriteria As String, ArgCount As Integer)

' Create criteria for WHERE clause.
If FieldValue <> &quot;&quot; Then
' Add &quot;and&quot; if other criterion exists.
If ArgCount > 0 Then
MyCriteria = MyCriteria & &quot; and &quot;
End If

' Append criterion to existing criteria.
' Enclose FieldValue and asterisk in quotation marks.
MyCriteria = (MyCriteria & FieldName & &quot; Like &quot; & Chr(39) & FieldValue & Chr(39))

' Increase argument count.
ArgCount = ArgCount + 1
End If

End Sub


Create unbound Combobox with what you want to filter.
on the on click procedure put this

Dim MySQL As String, MyCriteria As String, MyRecordSource As String
Dim ArgCount As Integer
Dim Tmp As Variant

' Initialize argument count.
ArgCount = 0

' Initialize SELECT statement.
MySQL = &quot;SELECT * FROM QueryName WHERE &quot;
MyCriteria = &quot;&quot;

' Use values entered in text boxes in form header to create criteria for WHERE clause.
AddToWhere [ComboName], &quot;[FieldName]&quot;, MyCriteria, ArgCount

' If no criterion specifed, return all records.
If MyCriteria = &quot;&quot; Then
MyCriteria = &quot;True&quot;
End If

' Create SELECT statement.
MyRecordSource = MySQL & MyCriteria

' Set RecordSource property of Subform.
Me.RecordSource = MyRecordSource

' If no records match criteria, display message.

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox &quot;No records match the criteria you entered.&quot;, 48, &quot;No Records Found&quot;

End If

End Sub


To Clear the above filter

' Clear controls in form header and remove records from subform.
'
Dim MySQL As String
Dim Tmp As Variant

MySQL = &quot;SELECT * FROM QueryName WHERE True&quot;

' Reset RecordSource property to remove records.
Me.RecordSource = MySQL



Good Luck

Alan