Trying to toggle combo box rowsource

D

Duck

I have a form on which I want the rowsource of a combo box to change
dependant on the status of a check box...so that if the check box is
checked the combo box gets its values from one table, and if not from
another table. Seems like it should be a simple matter but I keep
getting an "Object Required" error. I have the following code tied to
the GotFocus event of the combo box:

Private Sub cmbFocal_GotFocus()

On Error GoTo Err_cmbFocal_GotFocus

If chk.Prog = True Then
cmbFocal.RowSourceType = "Table/Query"
cmbFocal.RowSource = "SELECT tblLensProgressive.Type FROM
tblLensProgressive"
Else
cmbFocal.RowSourceType = "Table/Query"
cmbFocal.RowSource = "SELECT tblLensFocal.Focal FROM
tblLensFocal"
End If

Exit_cmbFocal_GotFocus:
Exit Sub

Err_cmbFocal_GotFocus:
MsgBox Err.Description
GoTo Exit_cmbFocal_GotFocus

End Sub
 
D

Douglas J. Steele

What is chk.Prog supposed to be? There's a period between chk and Prog, and
no object with which I'm familiar has a Prog property.

Incidentally, to refer to controls on the current form, you really should
use Me!cmbFocal (and Me!chkProg, assuming that's the name of the check box).
I think, too, you'd be better off putting that logic in the AfterUpdate
event of the check box: I'm sure that'll change far less frequently than
focus will be set on the combo box.
 
T

Tom Wickerath

Verify that the name of your check box (or Option Group) is chkProd. This
line of code appears to have an extra period in the name of the control:

If chk.Prog = True Then

Does your code module include the two very important words "Option Explicit"
as the second line of code? See this gem tip for more information:

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions

With Option Explicit include, does your code compile without an error? Click
on Debug > Compile ProjectName, where ProjectName is the name of your VBA
project. Correct any compile errors before attempting to fix anything else.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
D

Duck

What is chk.Prog supposed to be? There's a period between chk and Prog, and
no object with which I'm familiar has a Prog property.

Incidentally, to refer to controls on the current form, you really should
use Me!cmbFocal (and Me!chkProg, assuming that's the name of the check box).
I think, too, you'd be better off putting that logic in the AfterUpdate
event of the check box: I'm sure that'll change far less frequently than
focus will be set on the combo box.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)












- Show quoted text -
Click to expand...

Thank you so much Tom and Douglas...both of your responses got right
to the root of my problems, and were greatly appreciated!!
 
Top