Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 4th, 2003, 04:58 AM
Authorized User
 
Join Date: Jun 2003
Location: , , .
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.

Thanks

Tim Johnson
Reply With Quote
  #2 (permalink)  
Old September 4th, 2003, 09:20 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

Reply With Quote
  #3 (permalink)  
Old September 4th, 2003, 01:04 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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


Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 05:11 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.