I have a large database with 30,000 clients listed.
I have a large complex form with several subforms. At the top of the form is a combox. The combobox is populated by a query which selects all clients and their countries (join query). It then lists the client and country in two seperate columns.
The user can type into the combobx or click on the arrow. It will the query and they can select a client.
This works fine, the only problem is that there is a few second delay while the combobox is populating. I need to spped it up. I beleive the reason for the delay is just becuase of the size (30,000) so I need to do something about that.
My thinking is that when the user typeds in, it'll query all clients starting with that letter or letters. This is where I am stuck. I know how to perform the query but I am having some other issues.
I set the query. Now the user types in T, the combobox automatically fills it with the first client starting with a T and then that's the value typed in so it only queries that client instead of all clients starting with T.
I tried to do it on change of the combobox and that is not working. I tried setting a tag to the value and the querying the value but I am getting a null value so that's not working either.
Any suggestions?
Thanks

This is an unbound combo box?

Clever idea to use the OnChange to reset the query.

Just a quick check... are you using SQL to populate the combo box? If so, you might try using a passthru query to get the data. That may fetch the information more quickly.

Wish I had more time to try all this out. But here's an idea. You said the OnChange isn't working. But you didn't say what is not working. I think it will work if you do all of the proper things.

In the OnChange, check for the length of the value in the combo box Len(Me.Combobox). If 1, then build/change the query to find records "WHERE [Name] like """ & me.combobox & "*""".

After you change the RowSource, requery the combo box to pick up the new list of values (the names that begin with the letter typed).

Also, you'll probably want to set the combo box properties SelStart to 1 and SelLength to len(combobox) so when the user keeps typing they'll overtype the name that gets displayed in the combo box after your requery.

Now, since I don't have time to try it, I'm not sure all of that will work. But it may give you some ideas of things to try.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

Instead of one combobox with two columns, person & country, have TWO comboboxes.

The first one lists only the distinct countries (e.g. 15 of them); the second will not be populated yet (zero). This will be MUCH shorter than 30,000 records so it will load and operate faster.

When you select the country from the first combobox, it runs a query to populate the second combobox to only include names if they're from THAT country. This will be only be a subset of the 30,000 records and will be shorter. When you place your cursor in this combobox and start to type or pick, the lag will be slower because the list is shorter (e.g. only 576 people are from your selected country).





Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Sorry, I was out for the weekend. Thanks for all your replies.
BrianWren, It is unbound.
rjweers, I am using sql, I'm sorry what's a passthru?
I'm sorry but I did not explain the delay explicitly. I think that the combobox queries on focus because the delay is when the user types in. When he types it takes a couple of seconds for the character to display in the combox. So at that point it is too late.
SerranoG, That solution won't work, the clients need to all be listed.
Thanks again!

The combobox is such a bizarre control! I have never played with it so extensively before. Thake a look at this code:

Private Sub Combo4_Change()
Dim val As String
val = Me!Combo4.Text
Me!Combo4.RowSource = ""
Me!Combo4.RowSource = "SELECT Company.Com_Name, c.nc, Company.Country_ID FROM c INNER JOIN Company ON c.c = Company.Country_ID WHERE Company.Com_Name Like '" & val & "*" & "' ORDER BY Company.Com_Name"
Me!Combo4.Dropdown
End Sub

Let's say I type in A, so it drops down all the A's. Then I type in s (so that it's AS). It only drops down one client, It sets the value to the first client with AS and then it queries so that it only finds one! If I then type in o so that it's aso, then it drops down all companies starting with ASO! I need it always to do that! It doesn't help me if it only drops down one company! The user needs to be able to scroll.

rjweers, thanks! I used yours and I got it. I'm just not sure about the selstart and sellength properties. What are those doing?
Thanks so much!

Glad the solution helped.

Passthru query: a query that you set as a "Pass-Through" query. While editing the query, select Query | SQL Specific | Pass-Through from the menu. Purpose: if you are using SQL [u]Server</u> (I didn't mention the Server part in my first post, sorry) the query is sent to SQL Server without Jet interfering. SQL Server does all of the work selecting the appropriate records and sorting them. I'm told this improves performance significantly because SQL Server is faster than Jet and because the local machine isn't trying to fetch ALL of the data from SQL Server, bring it back and then decide what to do with it.

SelStart: indicates the starting position of the selection in the field. If SelLength is zero, SelStart is where the cursor will display.

SelLength: tells how many characters to select after SelStart. If the number is larger than the number of characters to the right of SelStart, Access doesn't care.

The reason I chose SelStart=1 and SelLength=Len(combobox) was to mimic the functionality of a "regular" combobox. I figured there was one of three reasons your OnChange wasn't working. Either you didn't Requery the combobox to get the fresh list; or your OnChange kept looping back and refetching the data because the whole field was selected when the new list was built so when the user kept typing the list started over; or you didn't like the way the user had to use the updated list because they had to go through gyrations to select the text that came up.

BTW, thanks for trying it out. That's one more thing I can put in my bag of Access Tips and Techniques.

Thanks so much for the explanation, I now understand.
You should definitely put it in tips and techniques. I searched around for a solution before posting and really found nothing except like a txtbox with a button and a combobox. Type in txt, click enter and the combobox queires.

I tested the whole thing in a form I created and it worked great. I then tried to incorporate the code with my large form and it didn't mesh so well with all the other code. I've just changed lots of my code and now I'm gonna try it again, I'll let you know how it goes.

One more question.
When it requeries, it puts the cursor back to the begining of the combobx. So that if the user typed in P, the cursor is before P instead of after. This can be very annoying for the user because they will continue to type after it has queried. Is there any setting to put the cursor at the end, after whatever was typed already? Thanks