Control is null in VBA

[Epax]

I have a combobox with a OnChange event and im calling the following code:


Private Sub cbCompanyName_Change()
Dim cbo As ComboBox

Dim sText As String

cbo = Me.Controls("cbCompanyName")

'Set cbo = Me.cbCompanyName


sText = cbo.Value

'sText = cbo.Text
'sText = cbo.Column(1)

Select Case sText
Case " "
cbo = Null
Exit Sub
Case Else
Call ReLoadCustomers(sText, cbo)
End Select

Set cbo = Nothing

If Not Me.ChkCustomer Then
Me.ChkCustomer = True
Me.Form.Repaint
End If
End Sub


In either call to set the cbo variable it is still null.


Thank you for your time.

 

[Epax]

To note:
It is almost like the form is disassociated from the module. Me does not report as null in development but once i run the code it looks null.

Argh?

 

[boblarson]

You need the AfterUpdate event. If it hasn't updated yet, it won't have a value.

 

[pbaldy]

Or use the .Text property, but I don't see anything there that needs to be done in the change event, so I agree with Bob.

 

[Epax]

Bob Thanks for the quick reply.

But, I'm not sure I follow. What should I have to put into the OnAfterUpdate.

Here is a screen shot of what the debugger is telling me:

 

[boblarson]

Why are you trying to create a new combo box object and then setting it to be the same one you are actually in the event for? I'm not understanding exactly what you are trying to accomplish.

 

[Epax]

pbaldy:
Notice in the code i have commented out the .Text

this was tried. It seems like the control is not being passed back to the event.

Again thanks for your time.

 

[Epax]

Well, I pull this code from an example and left it as such. I agree not needed.

To note: I have this exact same code in another form and it works.

Let me provide you with a bit of background to what Im trying to do.
I want to have the user type in char on the 3rd char i do a search and populate the combobox.

 

[Epax]

Found the problem:
The ComboBox is Unbound. Anytime I put a query in the Record Source of the Form the combobox is unknown or null and is not seen in the OnChange event.

 

[Epax]

Here is the solution:
Place a subform in your main form. In your main form add something to the recordsource otherwise you can not access the recordsource of the subform. After you have tested the code you can take out whatever you have place in the main form's recordsource.

 

[modest]

I just wanted to reiterate what pbaldy said earlier. You are using double the memory for the combo box by creating a new object. Insstead of declaring the "cbo" variable, just reference the combo box on the form.

So, remove "Dim cbo As Object" and "cbo = Me.Controls("dbCompanyName")"

And change "sText = cbo.Name" to "sText = Me.dbCompanyName.Name"