I used an AfterUpdate Procedure shown below:
Code:
Private Sub CboFilter_AfterUpdate()
On Error GoTo Err_cboFilter_AfterUpdate
' Purpose: Change the form's RecordSource to only products from this supplier.
Dim strSQL As String
Dim bWasFilterOn As Boolean
' Save the FilterOn state. (It's lost during RecordSource change.)
bWasFilterOn = Me.FilterOn
' Change the RecordSource.
If IsNull(Me.CboFilter) Then
' If the combo is Null, use the whole table as the RecordSource.
Me.RecordSource = "Orders"
Else
strSQL = "SELECT DISTINCTROW Orders.* FROM Orders " & _
"INNER JOIN [Order Details] ON " & _
"[Orders].[Order ID] = [Order Details].[Order ID] " & _
"WHERE [Order Details].[Section] = " & Me.CboFilter & ";"
Me.RecordSource = strSQL
End If
' Apply the filter again, if it was on.
If bWasFilterOn And Not Me.FilterOn Then
Me.FilterOn = True
End If
Exit_cboFilter_AfterUpdate:
Exit Sub
Err_cboFilter_AfterUpdate:
MsgBox Err.Number & " : " & Err.Description, vbInformation, _
Me.Module.Name & ".cboFilter_AfterUpdate"
Resume Exit_cboFilter_AfterUpdate
End Sub