Multi Search

[encore0000]

I want to have a multiple search functionality to search for 2-3 fields and display the results from a query which matches the words which were entered into the 2-3 textboxes.

 

[SteveR77]

Take a look at the following information on query by form.

http://support.microsoft.com/?kbid=209645

 

[encore0000]

Thanks alot Steve. I tried that method and am getting an Action Failed ...

I am using the following parameter in Criteria
Forms![QBF_Form]![WhatMachine] Or Forms![QBF_Form]![WhatUser] Is Null

 

[SteveR77]

Try this instead -

Like Forms![QBForm]![WhatMachine] Or Is Null


HTH,

Steve

 

[encore0000]

I tried another method i am getting the following error in the vb code
ERROR Userdefined-type not define

Option Compare Database

Private Sub cmdSearch_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database -> ERROR
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'Constant Select statement for the RowSource
strSQL = "SELECT tblTicketInfo.Machine_Number, tblTicketInfo.user_ID, tblTicketInfo.ticketStatus_ID, tblTicketInfo.location_name " & _
"FROM tblTicketInfo"

strWhere = "WHERE"

strOrder = "ORDER BY tblTicketInfo.Machine_Number;"


'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form
If Not IsNull(Me.txtMachine) Then '<--If the textbox txtMachine contains no data THEN do nothing
strWhere = strWhere & " (tblTicketInfo.Machine_Number) Like '*" & Me.txtMachine & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.txtUser) Then
strWhere = strWhere & " (tblTicketInfo.user_ID) Like '*" & Me.txtUser & "*' AND"
End If

If Not IsNull(Me.txtCity) Then
strWhere = strWhere & " (tblInfo.City) Like '*" & Me.txtCity & "*' AND"
End If

If Not IsNull(Me.txtStatus) Then
strWhere = strWhere & " (tblTicketInfo.ticketStatus_ID) Like '*" & Me.txtStatus & "*' AND"
End If

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the SQL to the RowSource of the listbox

Me.lstCustInfo.RowSource = strSQL & " " & strWhere & "" & strOrder

End Sub



Private Sub lstCustInfo_DblClick(Cancel As Integer)
'Open frmCustomer based on the ID from lstCustInfo listbox

DoCmd.OpenForm "frmCustomer", , , "[Machine_Number] = " & Me.lstCustInfo, , acDialog

End Sub