I have a database of documents with title, reference number etc. I want to generate a search from the input form on any one of a number of fields. Currently I have to have a number of boxes on the form, one for each field then pass this into VB in the following format

Dim searchstring As String

searchstring = ""

TxtTitle.SetFocus
If TxtTitle.Text <> "" Then
searchstring = "(DrawingTitle Like ""*" & TxtTitle.Text & "*"")"
End If

TxtRefNumber.SetFocus
If TxtRefNumber.Text <> "" Then
If searchstring <> "" Then searchstring = searchstring & " And "
searchstring = searchstring & "(RefNumber like ""*" & TxtRefNumber.Text & "*"")"
End If

I then pass this to a report with the following

DoCmd.OpenReport "Search Query Results", acViewPreview, , searchstring

Problem is the more fields I want to search on the bigger the form and the longer the VBA.

How can I have one search box which checks against title and/or number and/or any other fields I add.

Well, you could enter the name of the field in the tag of the control.

Then, you could create a loop that would go through all controls on your form, check them for text, and if found compile your string. Something like this:

Dim Ctl as Control

For each Ctl in Me.Controls

If Ctl.Value <> "" Then
searchstring = searchstring & "(" & ctl.Tag & " like ""*" & ctl.Value & "*"") AND "
End If

Next Ctl

searchstring = left(searchstring, len(searchstring) - 5) 'Remove the last ' AND '


Note: This is untested.

Here's a sample of how to search on as many fields as you like by typing criteria in just one field.

My thanks to the unknown author, this was posted before we had the virus attack.

Col

Oops - just re-read your post. My method would work if you want the user to be able to search mulitple fields for different text...

Colin's solution is perfect to allow searching for multiple fields for the same text.

Also, the default for a text box is .Text so you do not have to specify that, which means you don't need to set the focus if you use:
So, instead of:

TxtRefNumber.SetFocus
If TxtRefNumber.Text <> "" Then


Use this instead:

If Forms!MyFormNameHere.TxtRefNumber<> "" Then


If you are referring to the text box from a different form or different module, then use:

If Forms!MyFormNameHere.TxtRefNumber <> "" Then