Unbound text box input really slow at times 2

[Eutychus]

An Access 2002 application I've been asked to look at has a form with a table as the Record Source. On the form is an unbound text box named txtLookup used to allow the user to enter a customer's name to search for. The user can enter the name one character at a time and a second unbound text box named 1stClient will display the list of names that match the characters entered so far. When the user enters a "J", the second text box displays every name starting with "J." When he next types an "o" the second text box displays all names starting with "Jo" and so forth. If the user has typed in "Jones" then all the customers whose last name is Jones display in the second text box.

The code behind the On Change event of the first text box is as follows:
Private Sub txtLookup_Change()
Dim MySql As String
txtFirstName.SetFocus
txtlookup.SetFocus
txtlookup.SelStart = 100
MySql = "SELECT tblClient.pkClient, [Last Name] & ', ' & [First Name] AS Name, tblClient.[Last Name] " & _
"FROM tblClient " & _
"WHERE (((tblClient.[Last Name]) Like '" & txtlookup & "*')) " & _
"ORDER BY [First Name];"
lstClient.RowSource = MySql
End Sub

txtFirstName is the name of a bound field on the form for the first (not last) name.
When the user sees the name he wants to select in the second text box named 1stClient, he can click on the name and the record with that last name will be displayed on the form. The code in the AfterUpdate event of the 1stClient textbox is as follows:
Private Sub lstClient_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[pkClient] = " & Str(Nz(Me![lstClient], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Now here is the problem. At times when the user enters the name to search for in the first text box the system takes maybe 20 - 40 seconds to display each successive letter keyed in. E.g., the user types Jones. The txtLookup text box displays the "J," then "thinks" (searches) for 30 seconds, then displays the "o" and thinks another 30 seconds, then displays the "n" and so on. Normally, the name in txtLookup displays about as fast as the user types and the list of names displayed in the second text box 1stClent displays in less than a second. The first time the user does this search, it seems to work at normal speed. But after updating a bound field on the form and then going back to the txtLookup text box to look for another customer, the system dramatically slows down.

The application has the tables linked to a backend Access database on another user's machine in a peer to peer connection. There are three machines that use the same backend database; each of these machines has the identical frontend database on its C drive. (A typical Access database setup).

Can anyone give me some ideas as to why the display of the letters in the name is soooo slow? I've seen something very similar occur in another environment where the backend is on a server and the frontend is on the users' C drive and there is a domain set up. In this other environment, there is no second text box displaying the list, but rather the list is displayed in the first text box. Yet, the same problem with slowness occurs.

Any help will be much appreciated!

 

[JoeAtWork]

I would re-think the strategy of requerying the database on every single keystroke. It will usually take a few characters of the name typed in before the search results are narrow enough for the user to find what he is looking for. So the searches on the first 2 or 3 characters are likely just a waste of time.

Instead of doing an automatic search every time a key is pressed, I would put a Search button next to the unbound textbox. Then it would be up to the user to choose when to retrieve the list of names.

e.g. The user types in "Jones" and then presses the Search button, which runs the code that is currently in the txtLookup_Change event procedure.



Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[TheAceMan1]

How are ya Eutychus . . .

Is there any reason why you can't use a combox in AutoExpand mode?

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

You may want to take a look at
Faq702-6304

I demo a class module to encapsulate this functionality for any combo to turn it into a find as you type combo. My strategy may speed it up, but it suffers from what Joe describes in that it requeries the data on each key stroke. However, I tested it with 10k names and no problem. There are some slight differences.

1) I us a stored query which will be faster then defining a sql string in code especially if the query is properly indexed.
2) I use the combos recordset and use a filter on the recordset, I think this is much faster then redefining the rowsource and requerying

 

[Eutychus]

Thanks to all of you--JoeAtWork, TheAceMan1, and MajP! In different ways your replies were all helpful. I will add that I did try various searches in and out of Tek-tips to see if someone had an answer to my problem and did not find one. I did not write this database, but inherited it. I will present some of the options y'all have given and hope the user will allow me to improve the present code.

What still puzzles me is why the "search" is so quick at times and then so slow at other times. I've tried to isolate the factors that slow it down but at this point only have guesses and haven't arrived at a "proven" theory. Since it is a multiuser environment, I thought perhaps there is another user in the middle of updating a record or doing something that is causing the query to have to wait. Another hindrance I have had is being able to reproduce the problem. When I try to make it happen on my test site, it works fine for me. And when I walk the user through it again, it works fine. I've asked them to pay attention to factors that are different when the problem occurs, but no one thinks they are doing anything different. I don't expect a response to this. I just wondered if anyone in Tek-tips had experienced the same inconsistent behavior on a search like this.

I looked at the threads TheAceMan1 listed. There was good advice there. Thanks for the links.

Again, thanks to you all!