To do the search on more than one field, I would recommend moving the code
to another location. Set up combo boxes or text boxes, etc. to select or
enter the search parameters. Add a button to be clicked when all desired
selections have been made. Do the search in the Click event of the button.
This allow the user to choose one or more fields to search on and you'll
know that they're done making their selection(s) when they click the button.
Another option is to use combo boxes that have an "All" option added to
them. Require a selection in all of the combo boxes, either "All" or a
limiting selection. Enable each combo box as the user makes a selection in
the previous one and place the search code in the AfterUpdate event of the
last combo box. I still think the button option is easier though. It allows
the user to change their mind on one or more of the selections (including
the last one) before the search actually occurs. You may still want to add
the "All" option though, even when using a button. To add an "All" option to
a combo box, see this link:
http://www.mvps.org/access/forms/frm0043.htm
To then modify the code, you would add additional fields to the WHERE
clause. They would probably be concatenated together with And instead of Or,
since you'll have an option to allow All to be chosen. Using the Like
operator, as you're already doing, will select all values for that field
when you concatenate Null between the two *'s.
Example:
"WHERE FName Like ""*" & _
Me!cboCombo1 & "*"" AND " & _
"LName Like ""*" & Me!cboCombo2 & _
"*"" AND 3rdField Like ""*" & _
Me!cboCombo3 & "*"" AND 4thField Like " & _
"""*" & Me!cboCombo4 & "*"""
Just keep adding fields until you get as many as you need. When you're done,
you would still assign this to strQuery and use it as the Row Source of the
listbox. I've used combo boxes but you could also continue to use textboxes.
Leaving one blank would mean that you want no restrictions on that field.
This Null (blank) value would still concatenate in just as it does above.
Continuing with the example you already have and assuming that PostTitle is
a textbox then (the current code looks for a First or Last name that matches
the entry in the textbox (1 textbox, not 1 for FName and 1 for LName). I
will leave that as is and add the additional field, assuming the additional
field is in the same table. This will not "show" the PostTitle field, but
will sort on it. To "show" the PostTitle field, you would need to add it to
the SELECT portion of the query also and increase the number of columns in
the listbox. Note the parentheses added to group the OR statement.):
strQuery = "SELECT NameID, " & _
"FName & "" "" & LName AS FullName " & _
"FROM tblNames " & _
"WHERE (FName Like ""*" & _
Me!txtString.Value & "*"" OR " & _
"LName Like ""*" & Me!txtString.Value & _
"*"") AND PostTitle Like ""*" & Me.txtPostTitle & _
"*"" ORDER BY LName;"
