Find as you Type Combobox Problem 1

[GregSchlemm]

Hi everyone, I want to say that I just joined here. Stumbled upon it through goggle and have been just browsing around trying to resolve some issues and incorporate new ideas. What a great place this is!!

I'm trying to incorporate MajP's Find As Your Type Combobox faq702-6304. Currently it's set up on a datasheet that is a subform. I'm using it to record daily sales of items, In which the main form contains the customer, date, and weather information, the subform contains the products and sales info. If I open a form that already contains data in the main form and subform, it works like a charm. If I begin a new sales day, I'm getting an error "91 Object variable or With block variable not set" upon opening the form and subsequently when I try and type in the combobox.

Any ideas??
Thank you
Greg

 

[MajP]

Do you have the line of code where it fails?
Most likely the code will not break in the class module, so it will only show the error in the code that uses the class. Force it to break in the class module by the following

http://www.cpearson.com/excel/BreakInClassModule.aspx

This will allow us to see where the error is.

 

[GregSchlemm]

It's breaking here:
End With
Set mRsOriginalList = mCombo.Recordset.Clone
Exit Sub

on the Set mRsOriginalList line

 

[GregSchlemm]

I do want to add that I am not referencing the DAO object library, but I am referencing the Office 14.0 Object Library and the Office 14.0 Access database engine Object Library if that's of any consideration.

 

[MajP]

So the class module has a class variable called mCombo. This is set to your combo box on the form. The error then appears to me as this thing never gets set for some reason.

With mCombo
     .SetFocus
     .AutoExpand = False
End With

The fact that it works when there is data probably has to do with where you initialize the class. Forms load from the inside out. So the subform exists before the main form exists. For example this code should fail in the subforms on open event.
dim prnt as access.form
set prnt = me.parent

Because this is trying to reference the main form, but it is not open yet. I think you have a similar issue here. When your subform opens it may be trying to do the class events prior to the initialization event.
Where do you instantiate the FAYT? Main form or subform? Post the code.

 

[GregSchlemm]

Here's the code that's called on the subform:

Option Compare Database
Option Explicit
Public faytProducts As New FindAsYouTypeCombo


Private Sub Form_Load()

faytProducts.InitalizeFilterCombo Me.cboProductID, "ProductName", False
End Sub

Thanks for the help!
Greg

 

[MajP]

Try moving that code to the on open event of the subform. Not sure, but if the subform has not data yet then the load event may not be firing. These are hard problems to track down, when they deal with order of events.

 

[GregSchlemm]

That appears to have done the trick.
You rock!

Thank you very much for your assistance!!

 

[MajP]

I updated the fact to use the on open event. That makes more sense anyways.