I have a form named frmCustomer with customer information. The underlying table will eventuall get very large with over 50,000 names.

I need advise from someone who have experience with large db to suggest the best way to search the form to pull up customer records.

The first method I am thinking of is to put an unbound combobox on the form where the AutoExpand property is set to true so that as the user types in the first character it displays the customer name and gradually gets more selective as more characters are entered. The user can also click the combobox and search for the desired customer.

I would write codes to the AfterUpdateevent to display the entered customer

The second method I am thinking of is to have an unbound textbox on the form where the user enters at least the first character of the last name, click a button to return all records in a listbox with the letters entered on an unbound form. Naturally the more characters returned the more specific the search and less records are returned.

The user can double click ther desired record in the listbox on this unbound form to the selected record.

I am comfortable using either method but concerned about performance issues as the db gets big. I do not have the experience of large db and would like suggestion as to the better method or any other suggested method.

Thanks

Tim Johnson

Hi Tim,

I'd opt for method two. Combo boxes with many records are cumbersome, unweildy, annoying, difficult to use with multiple parameter searches.

I'd have the user enter at least the customers full first and last name in two unbound text boxes. You might want to have two list boxes for the search results: One for exact matches, and one for near matches if no exact matches are found. The list boxes can then display additional minimal information about each customer to assist in making the right selection. Double-clicking the list box opens a form displaying the selected customer info.

Place indexes on your search criteria fields and Access should't have any trouble searcing 50K records.

HTH,

Bob

Hi Tim,

Just wanted to add the thought that the near-match listbox can be an indispensible aid to preserving data integrity when searching with unbound controls. Alpha character strings (like names) are notoriously bad search criteria because they are so easy to misskey. If no exact-match record gets picked up on the first pass, that doesn't necessariily mean the customer isn't in the database. They may already be there with thier name spelled differently. Most large customer databases probably have a bunch of customers entered multiple times under a variety of (miss)spellings. Presenting the user with a near-match listbox gives them a list they can quickly scan to see if maybe they misskeyed something. Its also critical to add a few "numeric" search parameters if you can, like a phone number, or zip code for the same reasons. The near-match criteria might be something like the first three letters of the customers last name as follows:

Sub NearMatches()
    Dim strSQL As String
    Dim strCriteris As String

    strCriteria = Trim(Left(Nz(Me![txtLastName]), 3))

    If strCriteria <> "" Then
        strSQL = "SELECT lastname, firstname "
        strSQL = strSQL & "FROM Customers "
        strSQL = strSQL & "WHERE lastname Like '" & strCriteria & "*' "
    Else
        strSQL = "SELECT lastname, firstname FROM Customers"
    End If

     Me.lstNearMatches.RowSource = strSQL

End Sub

About combo boxes: I tend to limit their use to short lists of relatively static lookup values. They become unweildy with long lists of dynamic values.

About performance: Performance is not impacted by the selection of one of your options over other. Both will run the identical query. The only question is which control will that query recieve its parameter values from: text boxes or combo boxes. The former will likely serve you better.

HTH,

Bob