Hi all,
I have a car sales dbase and need to conduct multiple searches for a car in a form. This then displays all the matching cars.
e.g. On form have fields: "MAKE", "MILEAGE", "COLOUR". Input "TOYOTA" ">80000" "BLUE"
And it shows all Toyotas that are less than 80000 miles and are blue.
Can you please let me know if this is possible + any ways it can be done
Many thanks, a_wahab

Yes it is possible.

you need to create the query based on the selection criteria and get the records.

Om Prakash

I have done something similar. I have a form with a subform. The subform is used for the results, and what I do is have text fields and / or combo boxes to select my criteria. I then have an event when the search button is pressed which builds a SQL string based on values in the various controls and set the recordsource of the subform to that SQL string. I would be happy to elaborate if you need some more help.

Mike

Mike
EchoVue.com

I posted this once before, but can;t find the thread. Its some code to build a SQL string for a multi-parameter search using unbound controls.

You could build a composite (multiple-field) index that includes your FirstName, LastName, City, and State fields, for example, then...

Create a search form that allows users to enter multiple search parameters through unbound data entry controls, have code behind the search form that dynamically builds a WhereCondition string by concatenating the parameters together, then display a form filtered by your new WhereCondition string.

The code below might give you a rough idea of how to build a multiple-parameter WhereCondition string in code for a search form that includes unbound FirstName, LastName, City, and State textboxes, then opens (or filters) a serach results form. The code allows you to use the "*" wildcard character in your search criteria input, and builds a WhereCondition string that looks like:

[FirstName] Like "John" AND [LastName] Like "Doe" AND [City] Like "New York" AND [State] Like "NY"

**Code*********

Private Sub cmdSearch_Click()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strWhereCondition As String
    Dim lngCount As Long

    On Error GoTo ErrorHandler

    ' Clear the filter string
    strWhereCondition = ""

    '-----------------------------------------------------------------
    ' 1. Build FirstName parameter
    '-----------------------------------------------------------------

    If Not IsNothing(Me!txtFirstName) Then
        If IsNothing(strWhereCondition) Then
            strWhereCondition = "[FirstName] Like " & Chr$(34) & Me!txtFirstName
        Else
            strWhereCondition = strWhereCondition & " AND [FirstName] Like " & _
                Chr$(34) & Me!txtFirstName
        End If
        If Right$(Me!txtFirstName, 1) = "*" Then
            strWhereCondition = strWhereCondition & "*" & Chr$(34)
        Else
            strWhereCondition = strWhereCondition & Chr$(34)
        End If
    End If

    '-----------------------------------------------------------------
    ' 2. Build LastName parameter
    '-----------------------------------------------------------------

    If Not IsNothing(Me!txtLastName) Then
        If IsNothing(strWhereCondition) Then
            strWhereCondition = "[LastName] Like " & Chr$(34) & Me!txtLastName
        Else
            strWhereCondition = strWhereCondition & " AND [LastName] Like " & _
                Chr$(34) & Me!txtLastName
        End If
        If Right$(Me!txtFirstName, 1) = "*" Then
            strWhereCondition = strWhereCondition & "*" & Chr$(34)
        Else
            strWhereCondition = strWhereCondition & Chr$(34)
        End If
    End If

    '-----------------------------------------------------------------
    ' 3. Build City parameter
    '-----------------------------------------------------------------

    If Not IsNothing(Me!txtCity) Then
        If IsNothing(strWhereCondition) Then
            strWhereCondition = "[City] Like " & Chr$(34) & Me!txtCity
        Else
            strWhereCondition = strWhereCondition & " AND [City] Like " & _
                Chr$(34) & Me!txtCity
        End If
        If Right$(Me!txtFirstName, 1) = "*" Then
            strWhereCondition = strWhereCondition & "*" & Chr$(34)
        Else
            strWhereCondition = strWhereCondition & Chr$(34)
        End If
    End If

    '-----------------------------------------------------------------
    ' 4. Build State parameter
    '-----------------------------------------------------------------

    If Not IsNothing(Me!txtState) Then
        If IsNothing(strWhereCondition) Then
            strWhereCondition = "[State] Like " & Chr$(34) & Me!txtState
        Else
            strWhereCondition = strWhereCondition & " AND [State] Like " & _
                Chr$(34) & Me!txtState
        End If
        If Right$(Me!txtFirstName, 1) = "*" Then
            strWhereCondition = strWhereCondition & "*" & Chr$(34)
        Else
            strWhereCondition = strWhereCondition & Chr$(34)
        End If
    End If

    '-----------------------------------------------------------------
    ' f. Filter frmCustomers with new WhereCondition string
    '-----------------------------------------------------------------

    If IsNothing(strWhereCondition) Then
        MsgBox "No criteria specified.", vbExclamation, "Search Results"
        Exit Sub
    End If

    Me.Visible = False
    DoCmd.Hourglass True
    If IsLoaded("frmCustomers") Then
        Forms!frmcustomers.SetFocus
        DoCmd.ApplyFilter , WhereCondition:=strWhereCondition
        Forms!frmcustomers!cmdSearch.Caption = "&Show All"
        If Forms!frmcustomers.RecordsetClone.RecordCount = 0 Then
            DoCmd.Hourglass False
            MsgBox "No customers meet your criteria", vbExclamation, "Search Results"
            DoCmd.ShowAllRecords
            Forms!frmcustomers!cmdSearch.Caption = "&Search"
            Me.Visible = True
            Exit Sub
        End If
        DoCmd.Hourglass False
    Else
        ' Find out if any customers satisfy the WhereCondition
        Set db = CurrentDb
        Set rst = db.OpenRecordset("SELECT DISTINCTROW " & _
            "tblCustomers.CustomerID " & _
            "FROM tblCustomers" & _
            " WHERE " & strWhereCondition & ";")

        If rst.RecordCount = 0 Then
            DoCmd.Hourglass False
            MsgBox "No customers meet your criteria", vbExclamation, "Search Results"
            strWhereCondition = ""
            Me.Visible = True
            rst.Close
            Exit Sub
        End If

        ' Move to last row to get an accurate record count
        rst.MoveLast
        lngCount = rst.RecordCount
        DoCmd.Hourglass False

        DoCmd.OpenForm FormName:="frmCustomers", WhereCondition:=strWhereCondition
    End If

    DoCmd.Close acForm, Me.Name

ExitHere:
    Exit Sub

ErrorHandler:
    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
    Resume ExitHere
End Sub

**End Code*********

The code calls two user-deined utility functions (IsLoaded and IsNothing) that I placed in a standard module:

Function IsLoaded(ByVal strFormName As String) As Integer
    IsLoaded = False
    Dim frm As Form
    For Each frm In Forms
      If frm.Name = strFormName Then
         IsLoaded = True
      End If
    Next frm

End Function

Function IsNothing(varToTest As Variant) As Integer
' Tests for a "logical" nothing based on data type
' Empty and Null = Nothing
' Number = 0 is Nothing
' Zero length string is Nothing
' Date/Time is never Nothing

    IsNothing = True

    Select Case VarType(varToTest)
        Case vbEmpty
            Exit Function
        Case vbNull
            Exit Function
        Case vbBoolean
            If varToTest Then IsNothing = False
        Case vbByte, vbInteger, vbLong, vbSingle, vbDouble, vbCurrency
            If varToTest <> 0 Then IsNothing = False
        Case vbDate
            IsNothing = False
        Case vbString
            If (Len(varToTest) <> 0 And varToTest <> " ") Then IsNothing = False
    End Select

End Function

HTH,

Bob

Hi,

   It sounds like you are new to things. Here is what I did:

I created the following tables -

tblCars
   PK CarID - Autonumber
   FK MakeID - look up to tblMake
   FK ColorID - look up to tblColor
   Num Mileage - number field

tblMake
   PK MakeID - Autonumber
   TX Make - Text Field

tblColor
   PK ColorID - Autonumber
   TX Color - Text Field

I created the Make and Color look up tables first, and then when I created the Car table, I used the look up wizard to look up the Make and Color. You can add many more fields to capture pertinent data, of course.

I then created the following Query in the Query Designer:

SELECT tblCars.CarID, tblMake.Make, tblColor.Color, tblCars.Mileage
FROM tblMake INNER JOIN (tblColor INNER JOIN tblCars ON tblColor.ColorID = tblCars.Color) ON tblMake.MakeID = tblCars.Make
WHERE (((tblCars.Mileage)<=[Enter Max Mileage:]));

You can then create a Report from this query using the Report Wizard.

Once you have done that, create an unbound form. On the form, using the Combo Box wizard, create a combo box that looks up the Make from the Make table, the Color from the Color table. Change the bound column on each combo box from 1 to 2.

Then put a button on the form using the button wizard that opens your Report.

On the button's On Click Event, insert the following code:

'==============================
    Dim stDocName As String
    Dim stMake As String
    Dim stColor As String
    Dim stLink As String

    stMake = Me.Combo0
    stColor = Me.Combo2

    stLink = "[Make] = " & "'" & stMake & "'" & " AND [Color] = " & "'" & stColor & "'"

    stDocName = "rptCars"
    DoCmd.OpenReport stDocName, acPreview, , stLink

'================================

When you click the button, the query asks for the Max mileage, and then reports all cars that meet the parameters.

You may want to add error checking in case someone doesn't enter Make or Color.

That's one way to do it.



mmcdonal

Thanks Bob, mmcdonal

You've given me the tools and the confidence to get rid of the horrible DoCmd.RunCommand acCmdFind

R

I have a similar form and would like to know how to get the filtered results on the subform.