I have a db that has a form that allows the users to do a search. The user can enter various criteria, the form then generates a WHERE string, and opens a report with this filter. The form has various criteria on it, but what this post is concerned with is a "Search All" field. There's a textbox that a user can enter in any phrase to search on. I use the following code to achieve this part of it:
Dim frm As Form, ctl As Control, varItm As Variant, strTemp As String, _
i As Integer, intMax As Integer, blnAnySelected As Boolean
Set frm = Forms!Frm_Search
'build WHERE string for report
Dim strWHERE As String
strWHERE = ""
Set ctl = frm!TxtBox_All_Text
If Nz(ctl, "") <> "" Then
If strWHERE <> "" Then strWHERE = strWHERE & " AND "
strWHERE = strWHERE & "(" & _
"(TblMain.[FldMainKey]) Like '*" & ctl & "*'" & _
" OR (TblMain.[Fld2]) Like '*" & ctl & "*'" & _
" OR (TblMain.[Fld3]) Like '*" & ctl & "*'" & _
" OR (TblMain.[Fld4]) Like '*" & ctl & "*'" & _
" OR (TblMain.[Fld5]) Like '*" & ctl & "*'" & _
This works fine.
However, what I'd like to do involves a sub table of this main table (with a one-to-many relationship on the starred fields), which have the following layouts:
Is it possible to search on fields FldA and FldB for the same phrase I'm using for the main table, and include this in the filter?