Combo Box woes (1 Viewer)

NauticalGent

Ignore List Poster Boy
Local time
Today, 17:22
Joined
Apr 27, 2015
Messages
6,286
Good morning everyone,

I downloaded a file from a Gov website that contains 3,577,514 records. By far the largest table I have ever worked with. I need to use this table as a record source for a combo-box but the 65,536 limit is a real buzz-kill.

Dr. Google led me to Allen Browne's solution, but it is a little dated and I wondered if there was something a little more current that might take advantage of the <sarcasm> "MANY New and Improved features that MS have included with Access over the years" </sarcasm>

I did find this link from Mike Wolfe, but it appears I missed the webinar and I have not found the video yet in the AUG archives. I will continue to search but in the meantime I thought I would ask and take advantage of this forum's expertise.

Thanks in advance!

Edit: I did in fact try to search AWF, but the search feature is all wonky again...
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 21:22
Joined
Jan 14, 2017
Messages
18,186
Individual combo boxes become unwieldy with more than say 200 records or so.
The cascading combos example in the link given above is a cut down version from my UK postcodes app.
In the full version it is used to select down 2.6 million records down to one using 5 cascading combos.

There is another example on the FMS site Microsoft Access Form Cascading Combo Boxes (fmsinc.com).
I've never tried it though someone who did so told me there are some flaws with it.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 17:22
Joined
Apr 27, 2015
Messages
6,286
I've never tried it though someone who did so told me there are some flaws with it.
I have tried their method before on a different project. I didn't notice any flaws but it did seem like a LOT of effort for something that isn't all that complicated. Like using a bazooka to kill a mosquito...
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:22
Joined
Sep 12, 2006
Messages
15,614
I would have a form with a few unbound filters (combo boxes) to reduce the table (query) count to manageable numbers, and then requery the form based on the selections. If there's more than a few hundred I would probably make the user pick more filters to make it user friendly.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 17:22
Joined
Apr 27, 2015
Messages
6,286
That is pretty much the method the two links I posted use... I THINK the one on the NoLongerSet site uses a class module - which means I am already intrigued!
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 17:22
Joined
Apr 27, 2015
Messages
6,286
Well Mike is on here, why not PM him?
Lol. I actually started to do just that but I wanted to make sure future viewers would be able to take advantage of this thread. I tagged him in a thinly veiled attempt to "bring him out" - but now you have blown my cover. Thanks "Team-Player"...
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:22
Joined
May 21, 2018
Messages
8,463
That is pretty much the method the two links I posted use... I THINK the one on the NoLongerSet site uses a class module - which means I am already intrigued!
Maybe I am missing something, but those are just find as you type examples. There are plenty of examples on this forum. The design of the code for that many records would have to be focused on efficiency. I have demoed multiple FAYT in class modules, but those are designed for ease of use and reusability. I doubt you want to filter immediately after each letter and you do not want to Filter down. You want to start with nothing and filter up. I would think you allow the user to control when to search. They type in N letters then hit the search button to populate the list. Or some kind of cascade or other control. What kind of info is the list (addresses, names, numerics)?
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 17:22
Joined
Apr 27, 2015
Messages
6,286
Maybe I am missing something
No sir, you nailed it and I did try to find some of your examples too, but the @##$@! search thingy is down. Paul gave me a cool Google thingy to try but the people who pay me actually want me to work today - they got SOME nerve....
 

isladogs

MVP / VIP
Local time
Today, 21:22
Joined
Jan 14, 2017
Messages
18,186
My example works perfectly for a similar purpose to yours.
Whether you use a series of separate combos as GTH suggested or cascading combos, the result for the user is much the same.

However, if you want to see more examples using a series of separate combos / textboxes, have a look at
Multiple Group & Filter - Mendip Data Systems

FAYT methods also work well for data where the first few characters filter the dataset down to a manageable level. However, it wouldn't work well for e.g. postcodes of 7 or 8 characters where typing in the first 3 characters could still return around 10,000 results
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:22
Joined
May 21, 2018
Messages
8,463
I would not use my FAYT. These are written in an extremely inefficient way, but focused on ease of use and flexibility. They are very flexible because it requires no knowledge of the SQL string and can be implemented with a single short line of code. They work fine on a local Access db, but not designed to pull large amounts of data from an enterprise back end where you need to limit your calls to the backend and return as few records as possible.

I am actually surprised they work as well as they do. They do a very inefficient method of filtering the recordset. I think I demoed on 100k records without issue.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:22
Joined
May 21, 2018
Messages
8,463
I might do and interface something like this.
 

Attachments

  • Very Large Combo.accdb
    2.4 MB · Views: 198

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 21:22
Joined
Jul 9, 2003
Messages
16,245
Not sure if this will work with the number of records you have, would like to know!

I have created a pop-up form which extract the row source of the Combobox, uses that to identify how many record you have beginning with A, B, C, D etc. Highlights the buttons to show you which are available. Let's say the "P" button is one of the enabled buttons. Click on the "P" button and the combo-box (on the pop-up form) will list items beginning with "P". The Popup Form also incorporates the ability to Custom Search.



Demo YouTube Here:- Combo-box Alpha List



More Info on Nifty Access HERE:- Combo-box Alpha List:-


Download here:-

 
Last edited:

Users who are viewing this thread

Top Bottom