I have a search form that has 3 text boxes. If a users type data in 2 boxes I want it to search the database for those two search fields. I want the user to be able to search 1,2, or 3 fields.
I have the following code. I think I need a AND instead of OR.
Private Sub cmdSearch_Click()
Dim strSQLSearch As String
strSQLSearch = "SELECT Inv_ID, StockNbr, Year, Make, Model, Color, UnitStatus FROM tblInventory WHERE make = '" & Me.txtMake & "' or model = '" & Me.txtModel & "' OR color = '" & Me.txtColor & "'"
Me.lstInventory.RowSource = strSQLSearch
Here is the other problem. If the listbox brings up 3 records, no matter which record I click on it brings up the first record.
Private Sub lstInventory_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmVehicle", acNormal, , "Inv_ID = " & Me.lstInventory.Column(0)
DoCmd.Close acForm, "frmInventorySearch"
I want it to be and but if the textbox is null then I don't want to include it in the search string. For example, the fields are make, model, and color. If I type Honda for make and Blue for color I want to populate the listbox with Blue Hondas.