VBA Requery in a subform

[lfarnsworth]

Hello,

I'm relatively new to VBA programming. I'm trying to do somethingthing relatively simple, that isn't working and I have no idea why.

I've got a form with a subform. Within the subform, I've got two combo boxes: Department and Training. What I want it to do is this: I select a Department and the Training combo box shows me only the training related to it. I've written the query to do that and it works. I've written the VBA code to requery the Training combo box every time I update the Dept combo box. I've written the requery code in the module for the subform. It works, when I run the subform by itself. It gives me a runtime error when I try to run it from the main form. Which, as I write this, I realize is probably what I am doing wrong.

Here is the error

Run time error '2450':

Microsoft Office Access can't find the form 'Training Plan Subform' referred to in a macro expression of Visual Basic code.

Here is the code:

Private Sub Department_AfterUpdate()
 Dim ctlCombo As Control
 
    ' Return Control object pointing to a combo box.
    Set ctlCombo = Forms![Training Plan Subform]![Training]
    ' Requery source of data for list box.
    ctlCombo.Requery
End Sub

Do I have the code in the wrong place? Is there something else I am doing wrong? And, of couse, how should I be doing it? Any assistance would be greatly appreciated.

Many thanks in advance,
Leo

 

[pbaldy]

Here's a good reference on the syntax:

http://www.mvps.org/access/forms/frm0031.htm

 

[lfarnsworth]

Thanks. Unfortunately, I'm still not getting it to work. Based on that resource I've changed the code to look like this:

Private Sub Department_AfterUpdate()
 Dim ctlCombo As Control
 
    ' Return Control object pointing to a combo box.
    Set ctlCombo = Me!Training_Plan_Subform1.Form.[Training]
    ' Requery source of data for list box.
    ctlCombo.Requery
End Sub

I don't get any error messages, but it doesn't do the requery.

 

[lfarnsworth]

Nevermind, I got it.

Thanks a bunch for the help.

 

[pbaldy]

No problem, glad you got it sorted out.