Help creating a multiple field search form

[Barrelwaves]

Hello, i have a database that simply holds all the information on one table at the moment and what im trying to figure out is how to create a form that i can enter data onto that searches all the different fields at once, instead of one search for one thing and another for another. Some of the fileds are Headset number, gasket number, make, model, engine cc, etc.. so what i want to be able to do is have a form with a text box for each of these fields that then searches the table to narrow down the search results. at the moment i have a search for each, say for one i enter the headset number, it searches and pulls up all records with that headset number, then i have a search for engine cc, but what happens is it searches all records again for the engine cc not following on from the search for headset number so i end up back with a load of records with diferent headset numbers but with the searched engine cc. Basically the search form needs to take data entered into each text bow for each field then the combined results from each, narrowing down the results. can anyone help? in laymans terms!!
Thanks
Paul:

 

[Mikusss]

What you need to do here is to do a VBA code with dinamicly concotinated SQL query ...
and pass your input fields as a parameters for WHERE clause ...

Here is code example u can may be use, this one takes a values from the various fields on the form, build query and populate list box with the result.

Private Sub Filter_Click()

Dim strSel As String
Dim strFrom As String
Dim strWhere As String
Dim strFull As String

strSel = " SELECT Object.Address, Object.Area, Object.Room_num
strFrom = "FROM Object"
strWhere = " WHERE "

If Me.Area_min <> 0 Then
strWhere = strWhere & "[Object.Area] > " & Me.Area_min & " AND "
End If

If Me.Address.Value <> "" Then
strWhere = strWhere & "[Object.Address] like ('" & Me.Address.Value & "*') AND "
End If

If Me.Area_max <> 0 Then
strWhere = strWhere & "[Object.Area] < " & Me.Area_max & " AND "
End If

If Me.Room_min <> 0 Then
strWhere = strWhere & "[Object.Room_num] >= " & Me.Room_min & " AND "
End If

If Me.Room_max <> 0 Then
strWhere = strWhere & "[Object.Room_num] <= " & Me.Room_max & "
End If

strFull = strSel & strFrom & strWhere

With Object_listbox
.RowSource = strFull
.Requery
End With

End sub

 

[Barrelwaves]

Sorry could you put that in a little more laymans terms please, im a bit new to this whole access thing!

 

[Brianwarnock]

Firstly could you delete all of the duplicate entries of this thread. Click edit and then you will be given the option to delete.

Multiple criteria searches are not uncommon, simply reference the relevent textbox from the form in the criteria of the query. Say you have field fldcar_make and search text box txtcar_make
then in its criteria put forms!yourformname!txtcar_make
and repeat for all search fields

Brian

 

[Brianwarnock]

You might also like to read this in case it is applicable.

Brian

 

[Barrelwaves]

thank you Brian, seems to have done the trick. superb!!