All; using 2010. I have a unbound search form with a subform. I want to add another subform. I am having trouble linking the subform to the main subform. Here's my code:
Code:
Private Function SearchFilter() As Variant
Dim varWhere As Variant
varWhere = Null ' Main filter
' Check for LIKE Client Num
If Me.txtClientID > "" Then
varWhere = varWhere & "[client_num] LIKE """ & Me.txtClientNum& "*"" AND "
End If
' Check for LIKE Description
If Me.txtDesc > "" Then
varWhere = varWhere & "[bus_name1] LIKE """ & Me.txtDesc & "*"" AND "
End If
' Check for LIKE Company
If Me.txtCOM > "" Then
varWhere = varWhere & "[CO]LIKE """ & Me.txtCOM & "*"" AND "
End If
' Check for State
If Me.txtSTATE > "" Then
varWhere = varWhere & "[state] LIKE """ & Me.txtSTATE & "*"" AND "
End If
' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere
StringFilter = varWhere
End Function
This is my search button code:
Code:
Private Sub btnSearch_Click()
' Update the record source
Me.frmsubClients.Form.RecordSource = "SELECT * FROM qryClientData " & SearchFilter
' Requery the subform
Me.frmsubClients.Requery
End Sub
I want to have the second subform filter records based on the filtered main subform. Can I add code under the search button where I update the recordsource for the main subform? Just a thought. I have yet to make this work. Please help.