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?

[NeoPa]

I thought that was already discussed, but maybe I'm losing my comprehension here of what's been covered and what's not.

May I suggest you have a play with Example Filtering on a Form. This may help you to do what you want to do without some of the referencing issues.

If you need, we can look again at exactly what you need in your referencing code, but hopefully this link will help you do the whole thing more simply. Let us know how you get on.

[pl626]

@NeoPa
Thx NeoPa, unfortunately, I need to use the subform as it populates the child table. The main form has bound objects that populate the header table. I'm certain my problem is referencing the subform from the main form, but I'm not certain from which event to code the subform requery or do I need to declare some variables?

[ajalwaysus]

Could you post a screen shot of your main form with the subform, and perhaps the code on both as it stands now, because I am having a hard time understanding why you can't place after update code on your subform.

-AJ

[pl626]

@ajalwaysus
I just sent you a PM. I can't shrink down the screenshots without rendering them useless, but I can e-mail you. BTW, the code you suggested no longer results in the '2450' error, but it behaves just like the macro does, i.e., I am prompted for the parm value for the first control when I execute the second control. I also added a macro to open the subform, which kind of fixes the problem, but not quite. The parm prompt goes away, but the bound query doesn't really execute.

I'm certain the issue is a referencing issue, I just can't figure out what it is.

[pl626]

AJ,

No worries, the problem was with the syntax in the bound queries. Once I appended the SQL with the fully qualified name, [Forms]![mainform]![subform], everything works perfectly. Thx for your support on this.

[ajalwaysus]

No problem, ADezii deserves most credit for providing the platform to get this started, good luck in the future.

-AJ