Hi Everyone,

I need help with my application speed. I have a form that first asks for last name, then goes thru 400,000 records to search all first names in the database with that last name. Then, i search again thru the 400,000 records to get all the states with that first and last name combination and then i search again to get all the cities with that first name, last name and state combination. This takes a LONG time! And is not very efficient, especially when doing multiple searches. Can anyone suggest how I can make it faster/more efficient?

Thanks!!
-Mays

Hi Mays,

You could build a composite (multiple-field) index that includes your FirstName, LastName, City, and State fields, 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

The relevant code behind the display form that opens the search form is:

Private Sub cmdSearch_Click()

    On Error GoTo ErrorHandler

    If Me!cmdSearch.Caption = "&Show All" Then
        ' Save any changes first
        If Me.Dirty Then
            DoCmd.RunCommand acCmdSaveRecord
        End If
        DoCmd.ShowAllRecords
        Me!txtFirstName.SetFocus
        Me.cmdSearch.Caption = "&Search"

        ' Open the search form
        DoCmd.OpenForm "frmCustomerSearch"
    End If

ExitHere:
    Exit Sub

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

Bob

Hi Mays,

Final thought:

If you were going to require all 4 parameters for every search, you could of course simply write the WhereCondition string as:

strTest = "[FirstName] Like '" & Me!txtFirstName & "'" & " AND " & _
          "[LastName] Like '" & Me!txtLastName & "'" & " AND " & _
          "[City] Like '" & Me!txtCity & "'" & " AND " & _
          "[State] Like '" & Me!txtState & "'"

The advantage of bulding the string dynamically is that your search criteria become a lot more flexible.

You can:

1. Supply all four parameters, or supply any combination of parameters (e.g., leaving some null if you like)

2. Use the wildcard character.

Bob

Typo:

On the four lines of code that append the wildcard character, I referenced Me!txtFirstName all four times (cutting and pasting oversight). The line is:

If Right$(Me!txtFirstName, 1) = "*" Then

Just change Me!txtFirstName to the appropriate input control for each parameter (Me!txtLastName, Me!txtCity, Me!txtState).

Bob

Thanks so much Bob! I will give this a try. In my form, I have 4 fields----last_name, state, first_name, and city. Only one field is active each time. For example, when the form is loaded, the last name drop down list is active. Once the last_name is selected(say 'Smith'), then a "Get First Name" button becomes active and the last_name field becomes disabled and a populated first_name list appears. The first_name list is all the first names with the last name 'Smith.' Once the first name is selected, a "Get state" button is enabled and the first_name list becomes disabled and so on---I do this with all 4 fields. I did it this way to minimize user error and many times, the user only knows the last name and state, so I filter out all other records for them---so the user only selects from what's given to him/her. The code you posted looks like it lets the user input all 4 fields. Do you think that is a better way to handle the search?

Thanks again,
-Mays

Hi Mays,

One tecnique to accomplish what you are describing is to use cascading combo boxes. I had mentioned using unbound text box controls on the form that sets your search parameters. Using combo boxes instead would enable you to set the row source for each combo box based on the value selected in a previous combo box.

For example, if you want the first name list to be filtered by the last name you have selected, use a query as your first name combobox's rowsource and add the folloiwng selection criteria to the query:

=Forms![Formname]![cboLastName]

HTH,

Bob