Trying to toggle combo box rowsource

  • Thread starter Thread starter Duck
  • Start date Start date
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
 
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.
 
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
__________________________________________
 
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!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Back
Top