Combo box with hundreds of values

[skydivetom]

Experts:

One of my tables contains several thousand values for a particular field. I'd like to create a more "user-friendly" combo box which allows me to type in a few characters and narrow down any matching options (vs. having to scroll down several hundred/thousand records before finding it in the combo).

I tried to locate a sample db with this feature but didn't find a working example db. I then found an example (not coded though) on: http://allenbrowne.com/ser-32.html



I attempted to replicate his recommended process. Unfortunately, I was not successful in doing so.

Attached is my "crack" at his example. The table includes 8-digit random data in ASC order. At this time, I don't care about the values per se... I merely would like the combo to work, so if I were to type in, e.g., "115", my combo displays "11539061, 11543440, 11558562, 11583758, 11586094".

Now, once I select any of these five values, I would need that value to be stored in that particular record.

Note -- I don't have to have Allen Browne's example... if you know of another solution, I'd welcome if you could post it. Otherwise, I'd appreciate anyone helping me to get Allen's example to work. Pls post database.

Thank you,
EEH

[June7]

Don't bind the combobox to Suburb field. That is item 1 in Allen's instructions.
1. Leave the combo's RowSource property blank.

Then call function in events identified in item 3.
3. Call this function in the combo's Change event, and the form's Current event.

Can't use Me. in event property, only in VBA procedure.

Private Sub Form_Current()
Call ReloadSuburb(Nz(Me.Suburb, ""))
End Sub

The AfterUpdate event is useless because you don't have state and postcode data. Remove that code for now.

The combobox shows value when 4th character is typed.

You need two tables.

One table (PostCodes) is lookup source for combobox and needs to be populated with Suburb, State, Postcode values. There might be on-line sources for downloading postcode data.

Second table (possibly CustomerAddresses) is to save combobox selected value into. Bind form to this table. Then you can bind combobox to field of this table. Now can use AfterUpdate event. Actually, don't really need to save the state and postcode into CustomerAddresses. Can just save Suburb then retrieve state and postcode in query joining tables.

I tested a regular combobox setup with your 5000-record table and it executes very fast. However, not pulling over a network. Don't have to scroll. AutoExpand set to yes and typing in combobox fills with matching value, from first character entered. I really don't see advantage to the coded combobox. I would use GotFocus event with this normal combobox (doesn't seem to work with the coded version).

Private Sub Combo7_GotFocus()
Me.Combo7.Dropdown
End Sub

[mike60smart]

Hi EEH

What is wrong with using the standard method without any code as shown in the attached?

[skydivetom]

Thank you both for chiming in... I very much appreciate your continued assistance.
I did some more research on this subject and I realized (at least I believe) that I need a lookup table from which the, e.g., 5000 records. Then, the chosen value (e.g., #3547) should be placed in my data storage table.
Break//break
Both of you have contributed to previous posts of mine, so you're very much in tune w/ my overarching structure.
The "5000 records" will be the equivalent to my, e.g., the "office locations". So, I'm not really trying to find a numeric value. Instead, I want to show the various office locations in the drop-down.
An if someone picks office "San Diego Zip Code 12345" (amongsts San Diego 12345, San Diego 24589, San Diego 89634, etc.), I must store the record's autoID in my junction table.
I'm heading in a meeting in a moment. I will post another example w/ some **sample data** later on.
Again, thank you for the help in advance.
Cheers,
Tom

[June7]

I doubt posting another example will revise comments already provided.

[isladogs]

The standard method works fine if the combo row source doesn't contain tens of thousands or even millions of records.
However if you have a very large dataset, the combo would become unwieldy to use.

In a similar case using 2.6 million UK postcodes, I break it down using cascading combo's
Have a look at this example app and see whether the idea could be adapted for your situation.
See http://www.mendipdatasystems.co.uk/c...xes/4594455723

[skydivetom]

Mike60Smart, June 7:

I have not been able to get Allen Browne's function to work yet.

I decided to post a few selected elements with actual forms but with "dummy" data (due to proprietary restrictions). The dummy data does mimic (at least to some degree) the challenge based on the actual data.

So, as June7 and mike60smart are already familiar w/ the table setup, I won't go into further details. However, I'd like to further explain the forms (this is where I need some help):

1. Form "F05_Organization":
- Upon opening the form, the combo (on top on dark blue background) for the "Select Office" combo.
- Most (if not all) users identify their office location by the 7-digit code (e.g., "1115111"). While the value "Office Location 268" is helpful information, the location name is not the primary search method for the users.
- That said, for this form, I'd like to **start typing** the first three codes "112" and the available options should be reduced from 300 available values down to 10 values.
- If so, the user would see office locations ending with location ID "268, 273, 247, ..., ..., 246" and so forth.

2. Form "F10_StaffMemberToOffice":
- The same as in "F05_Organization" applies here for the "Select Office" combo which contains up to 300 values.
- In additon though, on the subform "F10_StaffMemberToOffice_Subform", the user can select "billets" from up to 2000 values.
- Just like in the other example, I don't want the user to drill down several hundred values before they find, e.g., "2310 -- Financial Analyst IV".
- Instead, I'd like them to starting typing, e.g., "231" and only 10 positions (2310 through 2319) will now be displayed in the combo.
- Then, user can pick one of the 10 values and the record is stored accordingly.

As you are aware, the forms are set up where I'm storing the auto ID number in the 1st column (with column width = 0). I'm not sure but maybe the auto ID could be moved in the last column position (w/o displaying it) and the "bound columm reference" is changed from 1 to 3. Thus far, I've not been successfully figuring this out.

How can this combo filtering be accomplished with the least amount of VBA?

Thank you,
EEH

[June7]

Allen's function worked with the simple edits I described in post 2.

But, again don't think need to bother with Allen's code. Not seeing it in this latest version of db.

Did you try normal combobox with code suggested in post 2? If you only want user to see 10 rows at a time then set ListRows property.

[skydivetom]

Good morning, June7. I'm sure Allen's function works just fine; however, I was unable to get it to work. If you could post the sample db that works for you, that would be great.

And, yes, since I was not able to get it to work in the demo version, I did not bother attempted to put the code into actual db. Doesn't mean I'm opposed to using it. Just need a working sample so I can replicate all steps.

"If you only want user to see 10 rows at a time"... that's not what I want to accomplish. Reference to "10 records" were only used to illustrate the sample data. So, instead, as the user keeps typing characters, the number of matching items in the combo box should continuously shrink. Once a more manageable # of items is displayed in listbox, user will then select the desired value. Conceptually, that's it. I know there's much more going on in the combo's background.

Again, if you could post Allen's sample db, it would go a long way. Thank you.

[mike60smart]

Hi

Your combobox for "Form "F05_Organization" seems to work just as it should.

I modified your Combobox for Form "F10_StaffMemberToOffice"

See if this suits ?

[skydivetom]

mike60smart:

Thank you for the continued assistance!

So, yes, I can start typing the ID number and the "first match" pops up. That works ok; however, I was wondering if all other "non-matching" values could be hidden from the combo?

//

How can I apply "finding 1st matching record" to the listbox on form "F50_BilletsToWorkingGroups"? Here, I still I have scroll down to find a matching value.

Thank you for any additional advice to a) limiting the combo choices for only matching records and b) potential solution to modify the Working's listbox to also drill down on matching records.

[June7]

Instructions in my earlier post to fix the original Postcodes example seem quite clear. Three simple edits and the combobox worked.

Even with Allen's code, combobox won't show any more in list than the ListRows property is set for.

[skydivetom]

June7 -- I gave it another shot... making some progress on Allen's example (v02 is attached). However, when selecting a value from the combo, I get an error (see attached snapshot).

[skydivetom]

Ok... I figured out the error.

Still, I'm not sure if this method will work for me. Here's why... once I value has been selected from the combo and the value is stored, I want the value to show up again when I return to the form. In this case, given the combo is bound to the lookup table and not the data storage table, I think that's why the values disappear. That won't work for me.

Ultimately, my process seems simple:
- Open up the form. If on main form, select value from combo with filtering option.
- Same for subform.
- Upon returning to the record, I should not have "lost" the value from the view... right now, that's what happening. See attached V03.

[mike60smart]

Hi

A Little puzzled by your statement "once I value has been selected from the combo and the value is stored, "

Normally the Combobox is used to View a specific Record which is already currently stored in the table?