Lookup Querey within subform of a subform

#1 (**permalink**) 12-Nov-2005, 22:40 PM

I have a Combobox table lookup query that works when I look at "FormA". The
Combo box is on a subform of Form A.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/7260-lookup-querey-within-subform-of-subform.html

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7260
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.

#2 (**permalink**) 12-Nov-2005, 22:40 PM

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

"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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Bob Richardson" wrote in message
news:qbKdnZzJrstg2-jenZ2dnUVZ_t6dnZ2d@whidbeytel.com...
>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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7260
> 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:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7260
>
> 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.
>

#3 (**permalink**) 12-Nov-2005, 22:40 PM

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!EvC ode)

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" wrote in message
news:eD3cApy5FHA.3984@TK2MSFTNGP10.phx.gbl...
> WHERE ((Classes.EvCode)=Forms!MainForm!Subform.Form!EvCo de)
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7260
>
> "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.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
>
> "Bob Richardson" wrote in message
> news:qbKdnZzJrstg2-jenZ2dnUVZ_t6dnZ2d@whidbeytel.com...
>>I have a Combobox table lookup query that works when I look at "FormA".
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7260
>>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.
>>
>
>

#4 (**permalink**) 12-Nov-2005, 22:40 PM

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Bob Richardson" wrote in message
news:as-dnR1ku-rkPOjeRVn-gQ@whidbeytel.com...
> 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!EvC ode)
>
> 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" wrote in message
> news:eD3cApy5FHA.3984@TK2MSFTNGP10.phx.gbl...
>> WHERE ((Classes.EvCode)=Forms!MainForm!Subform.Form!EvCo de)
>>
>> "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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7260
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>>
>> "Bob Richardson" wrote in message
>> news:qbKdnZzJrstg2-jenZ2dnUVZ_t6dnZ2d@whidbeytel.com...
>>>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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7260
>>>
>>
>>
>
>

#5 (**permalink**) 12-Nov-2005, 22:40 PM

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,
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7260
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7260
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

#6 (**permalink**) 13-Nov-2005, 17:32 PM

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!!EvCode.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7260
If EvCode is on a subform, you need to use Forms!! of control containing the subform>.Form!EvCode

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7260
always. It's whatever's in the Name property that you need to put where I
put above.

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Bob Richardson" wrote in message
news:FqmdnWXGMfBqjuveRVn-gg@whidbeytel.com...
> 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
>

#7 (**permalink**) 13-Nov-2005, 17:33 PM

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.

"Douglas J. Steele" wrote in message
news:OWcqwu65FHA.3312@TK2MSFTNGP15.phx.gbl...
> 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! > form>!EvCode. If EvCode is on a subform, you need to use Forms! > main form>!.Form!EvCode
>
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7260
> 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 above.
>
> You may find http://www.mvps.org/access/forms/frm0031.htm at "The Access
> Web" useful reading.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
>
> "Bob Richardson" wrote in message
> news:FqmdnWXGMfBqjuveRVn-gg@whidbeytel.com...
>> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7260
>> 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
>>
>
>