Solved Populating Combobox with a lot of data. (1 Viewer)

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:49
Joined
May 21, 2018
Messages
5,690
MajP, i'm trying. Sorry, this is a new concept for me.
No big deal I just found it strange that you changed it after I put a big foot stomping comment.
Code:
Const CharacterMin = 3
'MUST Be a module level variable as per Allen Browne
Dim sProductStub As String

How big is big? Lets say this list is less than 10000, then it may be simpler to just do a straight Filter/find as you type. I use a class module, but you can write some simple code. It is not an efficient method if you have 100k or more records or if the backend is on SQL. You would want to do something more like this where you first apply the filter before pulling any records vs starting with all the records and then narrowing it down.
See new subform
The reason for the class module is reuseability. Once you import the class module you never change the code, you just instantiate the class. Here is the entire code on the form to make this work. It would be the same for any other combo. Always just one line of code. Also you can change it to search anywhere in the string or any number of fields by changing the arguments.

Code:
Private FAYT_Part As New FindAsYouTypeCombo

Private Sub Form_Load()
  FAYT_Part.InitalizeFilterCombo Me.cmboProductID, "PM_Part_Number", FromBeginning
End Sub
 

Attachments

  • Quotes_Test 3.accdb
    948 KB · Views: 25

HillTJ

Registered User.
Local time
Today, 06:49
Joined
Apr 1, 2019
Messages
517
MajP, code works great on your demo. Exactly what I want. But I cannot get it to work on my form. I have copied the Class module to my database & confirm the Module name. The combobox of focus is on a sub form 'frmQuote_Fittings_Subform' & my combo is called 'ProductID' .

I have;
Private FAYT_Part As New FindAsYouTypeCombo under option explicit on the subform &
FAYT_Part.InitalizeFilterCombo Me.ProductID, "PM_Part_Number", FromBeginning on the form load event.

Exactly as in your demo & all compiles.

I note that the above is different to the notes in the class module.

When I open the main form 'I get the error message '7965 The object you entered is not a valid recordset property. In initializefiltercombo'. If I select OK then my form opens & displays the ProductID only. if I attempt to make a change by typing in some characters I get error '91 Object variable or With block variable not set in Filterlist.

Debugging what I've done is way above my head. What do I do next. Appreciate the assistance.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:49
Joined
May 21, 2018
Messages
5,690
My guess it does not like the rowsource of the combobox. Can you post it? Because you code was changing it back and forth there may be something saved that does not allow it to easily create a recordset. In fact your recordsource might have nothing in it since you start with an empty rowsource and then you build it.

I replaced the rowsource with the following and you should do the same..

Code:
SELECT TBL_Fittings_Products.ProductID, TBL_Fittings_Products.PM_Part_Number FROM TBL_Fittings_Products ORDER BY TBL_Fittings_Products.PM_Part_Number;
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:49
Joined
May 21, 2018
Messages
5,690
I like Class modules because I build the code once and can reuse it anywhere and in most cases requires only a couple lines of code. However, if you wanted to you could build this functionality relatively easily. Here is another example where they use a common method.

 

HillTJ

Registered User.
Local time
Today, 06:49
Joined
Apr 1, 2019
Messages
517
MajP. Yep it works!. Really appreciate your generosity. That code is epic. I hope other members have gotten something from this thread.
 

HillTJ

Registered User.
Local time
Today, 06:49
Joined
Apr 1, 2019
Messages
517
Friends, got home tonight & was thinking of my project. In particular, the phenomenal resource that the forum provides. I'd be dead in the water, often, if you people did not throw me a regular lifeline. It's gratifying to know that you (generic) help me. One side of me asks why? I wish i was in a position to give back, but my ability is pathetic compared to yourselves. Having said that, my ability has improved in leaps & bounds, and funnily enough, seems to take quantum leaps when something gels!

My current project was not initiated by me & was requested by my boss & involves a bit of a rewrite of a primative(when i look at it now) quoting system. Although primative, it's been in almost constant use since i wrote it a couple of years back, so i guess thats one mark of success!.

I'd love to know "back stories" relating to how you (generic again) got involved in the forum, gained youre experience etc.

May make a good thread?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:49
Joined
May 21, 2018
Messages
5,690
The reasons why Access forums are usually so helpful can be found in this interesting article

The article sums up what is pretty unique about the Access population
The Access audience is a special crowd that’s rarely targeted these days: technical people who aren’t serious coders. They may know their way around an Office macro, but they don’t have a formal programming background. Not so long ago, we called this kind of a person a “power user.”

Power users can be a dangerous group to help. With a little knowledge, you can make a very powerful weapon for shooting yourself in the foot. But there is a serious untapped potential here. Give a technical person a way to solve their problems that doesn’t involve writing pages of code, and they can make a difference — automating small tasks, managing their own islands of data, and helping to keep their local environment organized and effective.
So it is like asking your crazy uncle to tell you about his stamp collection, the classic car he is rebuilding on his own, or his beer making hobby. You are going to get a lot of free information and maybe more than you were hoping for.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:49
Joined
Feb 28, 2001
Messages
21,193
@HillTJ - I've told this story before, so I'll try to summarize.

I "accidentally" volunteered to take over an Access project when a colleague changed jobs and an important DB became "fatherless." The accident was accidentally admitting to my boss that I knew something about Access. I took over the fatherless child (and yes, it WAS a bastard of a DB) but had so much trouble that I had to find help and the forum was the answer. The departing colleague had obviously learned about Access from an Excel pathway because normalization was non-existent. Other things he did were not so advanced, either. After a ton of work, I learned enough about Access to start helping. In the summer of 2007, I was awarded an MVP (Microsoft Valued Professional) certificate because I had become very helpful to others.
 

HillTJ

Registered User.
Local time
Today, 06:49
Joined
Apr 1, 2019
Messages
517
Gents, very cool. The_Doc_Man, your intro is a bit like my own. As elaborated on in a different thread I was the 'last man standing' when maintaining our Q&A database in the early/mid '90's. I'm glad for the experience because when Access came into my life I was not 'biased' by the use of excel & understood the importance of normalisation (well mostly I get it right). MajP that article hits the spot!, and the big weapon bit ..yep. Cheers Guys.
 

Users who are viewing this thread

Top Bottom