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

 

[Uncle Gizmo]

Found it!

That's excellent thanks John. I've added it to my website!

 

[NauticalGent]

Quite welcome. I haven't had a chance to watch it yet...priorities. I did look at Col's and MajP's inputs (MUCH Appreciated) and both will do the trick although there are bells and whistles that I do not need, but easy enough to get what I need.

I will watch the video sometime today (hopefully) and sift through it all and decide which way to go. And once it all clears, I'll use your combo box with the large table and let you know how it goes.

Thanks all, this current project has me chasing my tail...

 

[NoLongerSet]

Here's a link to my article that has a downloadable sample database with all the code that I demonstrate in the video: Advanced Combo Box Techniques

 

[NoLongerSet]

Here are the steps to import the "lazy-loading" combo box feature:

1. Import the weComboLookup class module code from this article
2. Add the following to the form's code-behind module:

Private ComboLookup As New weComboLookup
Private Sub Form_Load()
    Const MinimumFilterTextLength As Integer = 3

    ComboLookup.Initialize Me.Combo0, MinimumFilterTextLength
    ComboLookup.UnfilteredRowSource = _
        "SELECT CustomerID, CustomerName " & _
        "FROM tblCustomer " & _
        "WHERE CustomerName Like '**' " & _
        "ORDER BY CustomerName; "
End Sub

To adjust the performance, you can play with the value of the MinimumFilterTextLength constant. The higher the number, the more characters the user will need to enter before filtering occurs. You'll need to strike a balance between performance and user-friendliness.

 

[Gasman]

Here's a link to my article that has a downloadable sample database with all the code that I demonstrate in the video: Advanced Combo Box Techniques

Mike,

Does it work in 2007 please?

 

[NoLongerSet]

Gasman,

It should work in 2007 without any problems. Let me know if you run into any issues.

 

[Gasman]

Gasman,

It should work in 2007 without any problems. Let me know if you run into any issues.

Hi Mike,
I haven't fully investigated it yet, but it seems to work fine for me.

Thank you.

 

[GoodyGoody]

Hi Mike, this looks like a great tool but I am struggling to implement it. I used to be a programmer and have the logic skills but my Access skills are fairly rudimentary. I have a subform where I am adding runners' names to a race event so I can then upload their times at the end of the race. I have a field on a subform where the user enters the runner's name, selects the right one and away they go to the next line. Now, sometimes they hve the number and not the name so this utility is perfect. I have a created a new class module and then I used the sample text I found on your site (I can't seem to find it again) to put in the 'On Load' event of the subfrm but get compile errors.

It tells me that me.Combo0 doesn't exist and then I don't get how the ComboLookup control is associated with my cmbRaceRunner combo box.

Apologies for my ignorance but your help would be much appreciated.

Thanks
Stephen

 

[azhar2006]

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

I thank you very much for two examples. Greetings, I was happy to download them

 

[ADIGA88]

I have an idea but I didn't test it and it needs some work to execute, why not use a form(subform) and a wapper around it as a combo box, you can use the wapper for resizing, positioning, hiding, filtering, etc... of the subform and you can do any thing with it, it's a form at the end.

 

[NoLongerSet]

Sorry @GoodyGoody, I just saw your post:

It tells me that me.Combo0 doesn't exist and then I don't get how the ComboLookup control is associated with my cmbRaceRunner combo box.

Combo0 is the name of the combo box control on my sample form. You would need to replace that with your cmbRaceRunner combo box. So, your code would look something like this:

Private ComboLookup As New weComboLookup

Private Sub Form_Load()
    ComboLookup.Initialize Me.cmbRaceRunner
End Sub