Get value combobox/textbox that does not have focus

[calcrisk33]

I am trying to get the value of a combobox after selecting an "OK" button but get "You can't reference a property or method for a control unless the control has the focus."

I understand what this means, but how to I get around it?

Most would probably say use .Value but when I press "OK" I am prompted for a value, or as in another case get Null. Again, I understand why I am getting these results but need a workaround.

I tried setting the focus prior to the violating line of code, but no go.

Example:

Private Sub viewByRegionOKButton_Click()
    DoCmd.OpenReport "byRegionReport", acViewPreview, , "RegionName = '" & regionComboBox.Text & "'"
End Sub

Thanks in advance.

 

[boblarson]

What that error means is DON'T USE the .TEXT part. Just drop that and it will use the default of .Value which is what you want 99% of the time anyway.

 

[calcrisk33]

Thanks for the quick response.

I've tried that as well but I am getting prompted to enter a value for "RegionName" when the user has already selected "RegionName" from the combo box on the form. This is the value I am trying to pass into the OpenReport WHERE condition.

Correct me if I'm wrong but .Value gets the stored value, no? .Text gets the displayed value. If that's the case then the value I am passing with the onClick will be Null until I provide the value, which it is prompting me for when I DO use .Value as suggested.

Any suggestions?

 

[boblarson]

If the combo is showing the name but has the ID as the bound field, then you would want to use this:

Private Sub viewByRegionOKButton_Click()
    DoCmd.OpenReport "byRegionReport", acViewPreview, , "RegionName = '" & [B][COLOR=red]Me.[/COLOR][/B]regionComboBox[B][COLOR=red].Column(1)[/COLOR][/B] & "'"
End Sub

Column(1) means the SECOND column (as they are zero based). So, if your description is the third column in the combo's row source then it would be .Column(2). But you also need to check to see that the combo's COLUMN COUNT property is set to the number of fields in its rowsource.

 

[calcrisk33]

I'm somewhat new to coding in this environment (Access and VBA) but does the combobox need to be Bound? I just populated it with SQL statement in Row Source.