Prevent combo box recordset = nothing on form open.

[BBQ Kittens]

Hopefully someone can help me since I have already spent far too much time trying on my own with little success.

I am using a module from tek-tips called "FindAsYouType" which allows my combo box to search Like *text* and not Like *text. Unfortunately when I open the form containing the combo box, I get 'run-time error 91 object variable or With block variable not set'. In order to avoid this I must first open the form in design and then normal to bypass this. My understanding is that when the form opens, the combo box recordset=nothing thus causing a conflict in the module on load. I have attempted to set the combo box recordset to anything other than nothing when the form loads and before the module is initialized, but I have had no success. what is the correct syntax for setting a combo box recordset?

 

[boblarson]

Why not just set the combo's recordset in design view so it has one.

You can use

SELECT 1 AS 1 FROM SomeExistingTableNameHere

 

[boblarson]

Can't find your code so I can't suggest the other option to have it not run on load.

 

[BBQ Kittens]

Unless recordset is the same as recordsource I am not sure where to do that. My recordsource is already set and i attempted to set it through vba with the 'on load' event thinking it wasnt triggering before the module but that was not the case. Unfortunately I need to leave my computer for a while but I will check back later today.

 

[boblarson]

Sorry I meant ROW source (sheesh, I can't even get it right when posting the response) . I would need to see what code you are using and why it is firing when the form loads.

 

[BBQ Kittens]

Here is a dumb'd down version of the database. Though the file type is mdb for the actual database. Youll see when you open the form that the form displays the error. If you open the form in design mode and then back to normal it works perfectly...Ill take another look at it but my brain is burnt out.

 

[BBQ Kittens]

Alright I managed to solve it with some trial and error. In case anyone else runs into this problem here was my solution.

Set Me.cboCompany.Recordset = Me.RecordsetClone

Using the class module found in the linked database above calls on the referenced combo box's recordset, but evidently, when you open a form, the combo box 'recordset=nothing' which in turn causes the run time error 91.

Simply add the code above with your combo box name before adding:

'Find as you type combo box
fayt.InitalizeFilterCombo Me.cboCompany, "Company", False

Hopefully this helps someone in the future and if my explanation above isn't accurate, meh.

 

[BBQ Kittens]

/facepalm alright the reason the recordset=nothing is because my combo box was unbound...this problem does not occur for bound combo boxs