Trying to toggle combo box rowsource

[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

 

[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.

 

[Guest]

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
__________________________________________

 

[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 -

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