Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    form/Subforms linking (Access 2003 SP2)

    Edited by HansV to replace zipped attachment with .png version of the picture it contained.

    I am having trouble with a form and 2 subforms.
    I have:
    1. a main form (let's call it A), which is linked to table A1. It's based upon table A1 has a PK of TID (this is a student ID).

    2. a subform (let's call it [img]/forums/images/smilies/cool.gif[/img] located on the main form A. It's based upon table B1 which has fields TID and QID (has values of 1 or 2) which are the PK. The main form A has a text field (called txtQID) that has value of =B.form!QID, this relates to the 2nd subform.

    3. a subform (let's call it C) located on the main form A. It's based upon table C1 which has fields TID, QID and LID (has values 1 to 4) which are the primary key. The text field QID on the main form is used by the 3rd subform in it's MasterLink field (TID;txtQID), the 3rd subform's ChildLink field is also (TID;txtQID). If I change the ChildLink to TID;QID I get an error as attached.

    Subform B works fine and shows the correct values per TID, however the subform C shows records for the TID and takes no notice of the txtQID field. I have had a look at other apps I have written and one is like this one and it works, I'm nonplussed.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: form/Subforms linking (Access 2003 SP2)

    Could you put subform C on subform B? That way, the linking would be straightforward.

    Otherwise, could you post a stripped down copy of the database?

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: form/Subforms linking (Access 2003 SP2)

    I don't want to put subform C on subform B, it would not look good, although I may have to go this way. Funny thing is I created a completely new database with the 3 tables and main form and 2 sub forms and it works beautifully. AGGH. I may have to start afresh with the form and subforms.

    I cannot send the database, it's got sensitive info.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: form/Subforms linking (Access 2003 SP2)

    Here is a cutdown version of the database.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: form/Subforms linking (Access 2003 SP2)

    My guess is that there is some kind of corruption. I can't make it work either with the existing forms, but if I create the forms anew, it works without a problem. Creating all three from scratch is probably the safest option.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: form/Subforms linking (Access 2003 SP2)

    I will try that again in a new database and import the rest of the stuff. I'll let you know how I go.

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: form/Subforms linking (Access 2003 SP2)

    Did you create the forms anew in the same database I sent?

    I have tried that on 3 databases so far and it's crook in each one of them.

    Is this worth doing it afresh in the same database or a complete new one?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: form/Subforms linking (Access 2003 SP2)

    I created new forms A, B and C in the database you posted, put B and C as subforms on A and linked them the same way as your forms. It worked right away.
    But it's safest to create a new database, import the tables and create the forms from scratch.

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: form/Subforms linking (Access 2003 SP2)

    When you created the forms, did you use all the fields in the table behind each form. I used only the fields I needed although all fields were in the SELECT behind the form.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: form/Subforms linking (Access 2003 SP2)

    I used AutoForms, so they automatically used all fields from the tables.

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: form/Subforms linking (Access 2003 SP2)

    Where do you find Autoforms?

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: form/Subforms linking (Access 2003 SP2)

    I clicked New in the Forms section of the database window. See screenshot.

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: form/Subforms linking (Access 2003 SP2)

    I use those all the time. I thought it was something that would guide you though setting a form and subform.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: form/Subforms linking (Access 2003 SP2)

    In fact, you can use the Form Wizard:
    - Select fields from the three tables
    - Select the option to create subforms.
    The Wizard wil set up the main and subforms for you, with the correct links.

  15. #15
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: form/Subforms linking (Access 2003 SP2)

    That's what I used once and it's absolutely great.

    Bad news I imported the 3 tables and a couple of lookup tables from the other database and created the 3 forms from scratch and I still have the problem.
    Where to go from here? Although I will try the form wizard and see what happens there.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •