Subform requery not working from Main form

[pl626]

In Access 2007, my subform has 3 controls that filter data from queries based on user selections from the preceding controls using the requery macro. It works fine when I have only the subform open.

However, when I try to run it from the main form, the parameters do not pass to the next control. Instead, I get a dialog box to input the value. How do I get the subform to work from the main form? Thx.

[ADezii]

@pl626

my subform has 3 controls that filter data from queries based on user selections from the preceding controls using the requery macro

Are you referring to a Combo Box on a Sub-Form? If so (Full Syntax):

Forms!<Main Form>!<Sub-Form Name>.Form!<Combo Box name>.Requery

[ajalwaysus]

You need to give a more technically detailed explanation on what it is you are doing. But if I were to guess, sounds like you may have a master-child linking issue, from the main form to sub form.

-AJ

[pl626]

@ADezii
Yes, I have 3 Combo Boxes in the Sub-form. As I understand it, that code should be on the main form correct? For which event should this code be assigned, 'On Got Focus'? Thx.

[pl626]

@ajalwaysus
AJ-thx for the quick response. My main form captures data for the header record of the parent table and the subform feeds a child table. The linkages are fine. There is no data that is being passed from the main form to the subform for these 3 controls. These 3 controls apply only to the subform data. The first control is a Combo-Box with values the user selects. This value is used to filter results for selection in the second combo box, which acts as a filter for the third. In the subform, i'm using the requery macro for this and it works fine when only the subform is open.

I've tried using 'Forms![main form![subform.Form![CNX Secondary].Requery' with various events and can't figure out how to get it to work.

[ajalwaysus]

First off, maybe you mistyped but your code should look like this:

Forms![main form]![subform].Form![CNX Secondary].Requery
 

and just to cover the bases, just to make sure it was done, you replaced the [main form] and [subform] values with the actual names of your form, correct?

If this is still an issue, another option you could do, is to set the after update of each combo box to re-query the next combo box in the subforms VBA code.

Eaxmple:

Sub ComboBox1_AfterUpdate()
    ComboBox2.requery
End sub
 
Sub ComboBox2_AfterUpdate()
    ComboBox3.requery
End sub
 

-AJ

[pl626]

AJ,

i did mistype in my response, the syntax i used is:

Private Sub Form_Load()
      Forms![MainFormName]![subFormName].Form![controlname].Requery
End Sub

Is there are different event on the main form I should be using?

[ajalwaysus]

If all 3 of your combo boxes exist on the subform then you should code the After update code I provided above, directly in subFormName 's VBA code.

When coding on the same form as where the controls exists, you then don't have to worry where you place that subform, because the code is native to that form.

-AJ

[NeoPa]

@pl626
From this it seems you have omitted to change the text (MainFormName) to the actual name of your form. That is, unless it is actually called MainFormName, but that would be a very bad idea, so I'm sure you haven't. The same goes for the names of other objects in the reference of course.

Perhaps if you shared the names of all the object references involved on your form we could determine the full code reference for you if you need us to.

[pl626]

I updated the code in the subform and it works fine in the subform, but now when I run the subform from the main form, I get a "Run-time error '2450' " stating it can't find my subform. Do I need to add code in the main form to reference the subform?