Combo Box woes

[NauticalGent]

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...

 

[Minty]

Have a look at @isladogs postcode app thingy.
It has a similar number of records and reduces it down to a few combo's - not sure of the technique used though.

Linky http://www.mendipdatasystems.co.uk/cascading-combo-boxes/4594455723

 

[NauticalGent]

Thanks Minty, I seem to remember now that he did something like this. Headed there now....

 

[Gasman]

Cascading combos?

 

[NauticalGent]

Cascading combos?

No sir, just a simple CB with a HUGE amount of records...

 

[isladogs]

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]

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]

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]

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!

 

[Gasman]

Well Mike is on here, why not PM him?

 

[NauticalGent]

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"...

 

[Gasman]

Sorry John,
Subtlety is not my strong point. You might have noticed?

 

[MajP]

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]

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]

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

 

[MajP]

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]

I might do and interface something like this.

 

[isladogs]

For anyone interested, I've attached a short video clip showing how my cascading combos example works:

 

[Uncle Gizmo]

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:-

Combo Box Alpha List - Nifty Access

Combo-box Alpha List Combo Box Alpha List (for MS Access) One of the problems you can run into when you have a combo-box which contains a lot of records, is it can be very difficult for the user to find a particular record. With the "Combo Box Alpha List" you…

www.niftyaccess.com

Download here:-

Nifty Combo Box Organiser - MS Access

One of the problems you can run into when you have a combo-box which contains a lot of records, is it can be difficult for the user to find a particular record. With the "Nifty Combo Box Organiser" you can offer your user a professional and sophisticated search function for your combo box.More...

niftyaccess.gumroad.com

 

[NauticalGent]

Found it!