While viewing a form, you can turn any form into a way cool query form.
While looking at the form, go
Records:->Filter-->Filter By Form
The above turns a form into a very nice search form. Many fields will even
become combo boxes.
The above is quite fancy, and nice...and takes no code.
The other thing to try is placing combo boxes on the top of your form. The
wizard has a nice search feature. Have you tried the wizard to build a
search combo box, and why not?
However, if you are bent on writing your form, then it is not too hard. I
would play with the above ideas first.
Basically, you can just create a un-bound form. That is a form that is NOT
attached to a table. You then place your 3 boxes on the screen, and allow
the user to enter values into those boxes.
The search button code can then look like:
dim strWhere as string
if isnull(me.txtFirstName) = false then
strWhere = "FirstName like '" & me.FirstTextBox & "*'"
endif
if isull(me.txtLastName) = false then
if strWhere <> "" then
strWhere = "(" & strWhere & ") and "
endif
strWhere = strWhere & "LastName like '" & me.txtLast& "*'"
endif
You can continue the above code for as many fields/conditions you place on
the form.
So, if are willing to write a bit of code, then the above will work quite
well.
' at this point, you can then open the main edit form with the resulting
records.
You can use:
docmd.OpenForm "main form",,,strWhere
However, instead of opening a main form, I often place a sub-form on the
form to display the "hit" list. The user can then select the particular
record to edit, and open the form.
strSql = "select * from tblCustomers where " & strWhere
me.MySubForm.Form.RecordSource = strSql
Here is some screen shots that uses the above code:
http://www.attcanada.net/~kallal.msn/Search/index.html