Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Lookup Querey within subform of a subform

Discussion in 'Information Technology' started by Bob Richardson, Nov 12, 2005.

  1. Bob Richardson

    Bob Richardson
    Expand Collapse
    Guest

    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.
     
  2. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    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.

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



    "Bob Richardson" <bobr at whidbey dot com> 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.
    >
     
  3. Bob Richardson

    Bob Richardson
    Expand Collapse
    Guest

    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" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:eD3cApy5FHA.3984@TK2MSFTNGP10.phx.gbl...
    > 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.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    >
    > "Bob Richardson" <bobr at whidbey dot com> 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.
    >>

    >
    >
     
  4. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    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" <bobr at whidbey dot com> 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!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" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    > news:eD3cApy5FHA.3984@TK2MSFTNGP10.phx.gbl...
    >> 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.
    >>
    >> --
    >> Doug Steele, Microsoft Access MVP
    >> http://I.Am/DougSteele
    >> (no e-mails, please!)
    >>
    >>
    >>
    >> "Bob Richardson" <bobr at whidbey dot com> 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.
    >>>

    >>
    >>

    >
    >
     
  5. Bob Richardson

    Bob Richardson
    Expand Collapse
    Guest

    More Query problems

    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
     
  6. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    Re: More Query problems

    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.

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



    "Bob Richardson" <bobr at whidbey dot com> 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. Bob Richardson

    Bob Richardson
    Expand Collapse
    Guest

    Re: More Query problems

    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" <NOSPAM_djsteele@NOSPAM_canada.com> 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!<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.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    >
    > "Bob Richardson" <bobr at whidbey dot com> 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
    >>

    >
    >
     

Share This Page