Lookup Querey within subform of a subform

[Bob Richardson]

I have a Combobox table lookup query that works when I look at "FormA". The
Combo box is on a subform of Form A.

SELECT Classes.ClassCode FROM Classes
WHERE ((Classes.EvCode)=Forms!FormA!EvCode)
ORDER BY Classes.ClassCode;

When I then make Form A a subform of the Main form, the above query no
longer works.

MainForm
FormA
SubForm

I am not using a situation where there are two subforms of a main form,
which would look like:

MainForm
Subform1
Subform2

I never want to just look at FormA by itself. It should always be a subform
of the main form. I just noticed that it worked when it wasn't a subform.

How should I change the query to work when FormA is a subform.

 

[Douglas J. Steele]

WHERE ((Classes.EvCode)=Forms!MainForm!Subform.Form!EvCode)

"Subform" is the name of the contain where FormA resides on MainForm. If you
simply dragged FormA onto MainForm, it'll likely be named FormA, but double
check to be sure.

 

[Bob Richardson]

Still have a problem. It's asking for the parameter value of the right side
of the equation. Here's an exact copy:

WHERE ((Classes.EvCode)=Forms!MainForm!Subform.FormA!EvCode)

I noticed in your reply that you used "Form!EvCode" I tried it that way and
also "FormA!EvCode" but neither worked.

 

[Douglas J. Steele]

It's quite unlikely that Subform is the correct keyword to use, unless
that's what you named the control. You need to determine the name of the
subform control in which FormA has been defined is named, and use that name.

Once you've got that control name correct, it's definitely Form, not FormA
that you want there.

As I indicated below, if you simply dragged FormA onto MainForm, the control
will likely be named FormA. If that's the case, you want:

WHERE ((Classes.EvCode)=Forms!MainForm!FormA.Form!EvCode)


You might find http://www.mvps.org/access/forms/frm0031.htm at "The Access
Web" to be a useful reference.

 

[Bob Richardson]

This picture is worth 1,000 words!. I've just clicked the combo box "1St" in
the Class Choices subform. You can see the main form with it's subform,
EventSignUp, which also has a subform (sub2) called Class Choices. When I
enter "SW06" in the Parameter box, I get the correct result. I'm trying to
pick up "SW06" from the EventSignUp form. Something's wrong with my
RowSource Query.

http://www.pbase.com/image/52138756.jpg

 

[Douglas J. Steele]

Parent is a VBA concept, and SQL doesn't know anything about VBA.

Rather than Me.Parent!EvCode, you need to use the actual form name. Assuming
that EvCode is on the main form, you need Forms!<name of main form>!EvCode.
If EvCode is on a subform, you need to use Forms!<name of main form>!<name
of control containing the subform>.Form!EvCode

<name of control containing the subform> needs a little explanation.
Subforms don't actual exist on parent forms. Rather, there's a subform
control on the main form that's set to use whatever form you created to be
used as a subform. If you select a subform and look in the Properties box,
you'll see that there's a Name property and a Source Object property. The
Source Object is the name of the form that's being used as the subform. Now,
normally the Name property will also be the name of the subform, but not
always. It's whatever's in the Name property that you need to put where I
put <name of control containing the subform> above.

You may find http://www.mvps.org/access/forms/frm0031.htm at "The Access
Web" useful reading.

 

[Bob Richardson]

Thank you ENORMOUSLY! Yes, the name of the subform was slightly different.
The form name was EventSignUp, but the name of the subform control was
"Event Sign Up". The syntax seems a bit daunting but I hope I'm gradually
getting it. Thanks again.