Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Star Lounger
    Join Date
    Feb 2003
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Subform within subform (2003)

    Is there any way to have a subform within a subform in which both tables are the "many" side? I have a table for Corporation , then I have a table for Officers (there is a long list of those) and then I have a table for Titles (in which each officer could have more than one title and there is a "begin date" and "end date". Is there a way to make Corporation the main form, then a subform with Officers, then within that a subform with titles so that each officer can have his list of titles by his name. I can't get a continous form if I put a subform within a subform and that's where the problem is. Should I set it up diffently? Thanks.

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

    Re: Subform within subform (2003)

    You can have a subsubform within a subform within a main form, but only the subsubform can be a continuous form. You cannot put a subform in the detail section of a continuous form.

  3. #3
    Star Lounger
    Join Date
    Feb 2003
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform within subform (2003)

    Any ideas on how I could show all three things?

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

    Re: Subform within subform (2003)

    You can have a main form displaying corporations, a subform displaying officers for that corporation, and a continuous subform of the subform displaying titles of the officer.

    If that's not what you want, please explain.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Subform within subform (2003)

    I assume from your oiginal question that an officer could have more that one job title...is that the correct assumption? If so does that mean they could also be working for other departments between specific dates such as secondments acting up etc
    Jerry

  6. #6
    Star Lounger
    Join Date
    Feb 2003
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform within subform (2003)

    Yes, an officer could have more than one job title with different dates or more than one job title with the same dates- for example he could be the CEO and the President during the same dates, but that would be two entries in the titles table. What I originally wanted to do was have the Corporation info be the main form. Then a subform listing the officers and then next to the officers a list of their job titles. I guess that wouldn't work. I think a list of the officers would be the most important to see right away and somehow I have to let the user be able to see the titles.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Subform within subform (2003)

    I asked first as I was involved in a Staff Tracking system for my Directorate that tracked staff movements, secondments training, appraisals and Annual reviews. Although this system had a web front end the methodology for the db structure should be roughly the same.

    I am assuming from your first message in the thread that you have a 1 to many relationship from from the corporate table to staff and also a 1 to many relationship between the corporate table to Job Title table? (correct me if I am wrong)

    What I would suggest is to review your table structures.

    I would say that the main entity of the db is the staff member(tblStaff), next is the department(tblDept), JobTitle(tblJob).

    I can see there is a many to many relationship between tblStaff and tblDept ( as a department can have more that one staff member and a member of staff could in theory work in more than one department) so a bridging table will be required and I would suggest their start and end dates are placed in here)

    There is also a relationship between tblDept(1) to tblJob(many)

    and a relationship from tblStaff(1) to tblJob(many)

    Now what I suggest you do is have a continuous form like you already have of the staff members but on each record place a command button that when clicked opens a pop up form showing the staff member and a Job history.

    So in brief you have one main form for the corporation(master) showing a subform with all the staff(child). linking to ---->

    A Form showing that staff member(master) showing a subform of all their current assignments or historical assignments(child)

    This may mean a major rethink and redesign job but it really does make a little sense as subforms within subforms are a real pain and can be confusing to the user if not designed correctly....anyway some ideas for you to ponder on <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Jerry

  8. #8
    Star Lounger
    Join Date
    Feb 2003
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform within subform (2003)

    Thanks for the detailed reply. I really tried to apply something like that to my database but I just don't know what to do.
    Here are my tables:

    CorpInfotbl
    ---------------
    PrimaryCorpNames - KEY
    DateofIncorp
    StateofIncorp
    CorpSeal
    LocBks

    OfficerNamestbl
    -----------------------

    PrimaryOfficers -- KEY
    PrimaryCorpNames ---FKey
    OfficerNames
    DatebeganOfficers
    DateendedOfficers

    OfficerTitlestbl
    --------------------

    PrimaryTitles -- KEY
    OfficerTitles
    DatebeginOffTitles
    DateendedOffTitles


    CorpInfotbl and OfficersNamestbl have a one to many relationship. Then OfficersNamestbl and OfficerTitlestbl have a one to many relationship. Is there to bring it together like that? It's not really that any of them have a many to many relationship.

  9. #9
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Subform within subform (2003)

    Hi there

    This my starter for 10. I have created 5 tables Have a look at the database attached and if you have got any questions can you get back to me or one of the admin who may also assist you. I hope this helps?
    Jerry

  10. #10
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Subform within subform (2003)

    I posted the wrong attachment , so if you have downloaded it before you receive this message delete it and download the other again. I added a training diary
    Jerry

  11. #11
    Star Lounger
    Join Date
    Feb 2003
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform within subform (2003)

    Thank you for making me the example. It helps so much to see what you mean. I used the database you sent and tried to set it up the way I wanted it. The only thing that's not working is my subform for titles. I click the command button to get to it but when I enter info I get an error message. I did change the relationships so I probably messed it up. If you or anyone could take a look I would be very appreciative. Thanks.

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

    Re: Subform within subform (2003)

    Take a look at what?

  13. #13
    Star Lounger
    Join Date
    Feb 2003
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform within subform (2003)

    Sorry, I don't know what happened to my attachment.

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

    Re: Subform within subform (2003)

    I don't understand why there is an OffID field in tblTitles and why it is linked to OffId in tblPrimOfficer. The link between officers and titles is through the intermediary table tblTitleEmployBridge.

    Furthermore, I think you should have a separate form to enter titles. The Titles button on the sfrmPrimOfficer subform shouldn't open sfrmTitle (which is not a subform, by the way), but a form based on tblTitleEmployBridge. You need extra code to set the default value for OfficerID.

    See attached version. I have also removed duplicate indexes from the tables (the take up unnecessary space).

  15. #15
    Star Lounger
    Join Date
    Feb 2003
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform within subform (2003)

    Hans,
    I can't tell you how much I appreciate you helping me with this. I'm trying to look through everything you did so I can understand this a little better. It all works great but the only problem I have is with entering a title. When I try to type something in it, it gives me the error 'text not an item in the list." I managed to get the Limit to List off but it still wouldn't let me enter new text. I see why it's a combo box but because I don't fully understand all of this yet, I don't know what to do to allow new entries in the title. Sorry I can't seem to get any of this myself.

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
  •