Wrox Programmer Forums
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old September 4th, 2003, 04:58 AM
Authorized User
Join Date: Jun 2003
Posts: 47
Thanks: 0
Thanked 0 Times in 0 Posts
Default best search method

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.


Tim Johnson
Old September 4th, 2003, 09:20 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts

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.



Old September 4th, 2003, 01:04 PM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts

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 & "*' "
        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.



Similar Threads
Thread Thread Starter Forum Replies Last Post
New text search doesn't preselect search string planoie Visual Studio 2005 0 July 23rd, 2007 06:47 AM
File Search / Indexing Search with .net 2.0 maulik77 ASP.NET 1.0 and 1.1 Basics 2 March 15th, 2007 12:45 AM
file search method esfaryn Excel VBA 0 November 4th, 2005 04:14 AM
Search Engine for Full-text Search Kala ASP.NET 1.0 and 1.1 Professional 2 August 29th, 2004 02:16 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.