Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Feb 2003
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    relationships and subforms (97)

    I'm just beginning to set up a new database and have some questions. I'll explain it the best I can and I hope someone can help. I have my first table called Members which includes ID, Name, Address, City, State, Zip.
    I have a 2nd table called Programs which is just a list - it has ID (autonumber), and Program (volleyball, hockey, walking, etc.)

    I need to make a fill in form which includes all member info, then be able to pick a program, or more than one program depending on how many a member joins, and then I was thinking of a subform for each program which lists info pertaining to that program.

    So my questions are - how can I pick more than one program for each member and how can I do that but still be able to see the appropriate subforms? Should I make each program it's own table instead of the list like I have?

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

    Re: relationships and subforms (97)

    What you describe is a many-to-many relationship between members and programs: one member can participate in several programs, and several members can participate in one program.

    A many-to-many relationship is iomplemented by creating an intermediate table (let's call it MemberPrograms) that contains two fields: MemberID and ProgramID. You will add a record to this table for each member-program combination. So if member # 37 participates in programs #5, #12 and #23, you will add three records to the intermediate table. If you need to store information that is specific for the combination of member and program (for example start and end dates, fee paid, etc., you can add extra fields to the intermediate table.

    You must set a relationship between Members and MemberPrograms on ID vs MemberId, and between Programs and MemberPrograms on ID vs ProgramID. Set referential integrity and cascading updates for both relationships; if you wish, you can also set cascading deletes (so that if you remove a member, for instance, all programs (s)he participated in will also be removed.)

    The form setup could be as follows:
    <UL><LI>Main form based on Members
    <LI>Subform based on a query joining MemberPrograms and Programs (so that you can display program info in the subform)
    <LI>Main form and subform linked on ID vs MemberID[/list]I hope this will give you a start. If you need more help, don't hesitate to post back.

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

    Re: relationships and subforms (97)

    Hi Hans
    Thanks for the help. I got started on what you suggested but now I'm hung up again. I've got the form and subform set up but when I try to enter a Program in the subform it gives me an error on the ProgramID. It can't find the ProgramID. I just can't seem to make enough sense to figure it out. Can you take a look at this?

    Thanks for any help you can give me!
    Attached Files Attached Files

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

    Re: relationships and subforms (97)

    The cause of the problem is that the Program combo box is bound to the Program field. It should be renamed and bound to the ProgramID field, for the (hidden) first field in the row source of the combo box, corresponding to the bound column, is ProgramID.

    I have made some other changes:
    <UL><LI>The tblMemberPrograms table doesn't need an AutoNumber field; I have removed it and set the primary key to the combination of MemberID and ProgramID. This combination is the unque identifier for this table. The Program field is redundant, since it can be looked up in the Programs table; I removed it too.
    <LI>There were some duplicate and superfluous indexes in the tables. I removed these.
    <LI>I based the form and subform on the Members and tblMembersPrograms tables, repsectively. There is no need for the queries at this stage.'
    <LI>I removed the MemberID and ProgramID fields from the subform, there is no need to see them.
    <LI>The search combo box referred to a field named ID; since you renamed it to MemberID, the row source and AfterUpdate code had to be updated.[/list]I have attached the modified database.
    Attached Files Attached Files

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

    Re: relationships and subforms (97)

    Post deleted by dknoll@mnpower

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

    Re: relationships and subforms (97)

    Hi Hans
    Thanks for all the help on this database. I've progressed a little further and now have another question. I've got the main form and one subform set up (with your help) and now I need additional information added. I need some fields that would be linked to 3 certain programs, Running, Inline and Nordic. The new fields would be Race (yes/no), Marathon Type, Registration paid (yes/no) and Year. Should I just put the fields into the subform table I already have set up, even though these fields aren't needed for all the program choices and there would be alot of blanks, or should I create another table and somehow link them depending on the choice of Running, Inline or Nordic? I've set up a table and form but haven't tried to link them because I really don't know the best way. Can you help?
    There is alot of extra stuff in this database that is unrelated to this table. My tables are: Members, MembersPrograms, Programs (these three are the ones you helped me with) and RUNNINGinfo (which I just created for this new info). My forms are: Members-Programs ENTRY, subformPrograms (these two are the ones you got working for me) and subformRUNNINGinfo (my new one for this new information).
    So I need to somehow link these additional fields if someone picks the program choice of Running, Inline or Nordic. Also, this information would have to be stored by year somehow because we would need a clean slate each year to specify if they were racing and registration was paid. Would that be a new table every year??
    Please let me know if I need to explain this better. Thanks for any help you can give me, I really appreciate this!

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

    Re: relationships and subforms (97)

    Hans,
    Thank you for all the help with this database. It looks and works great! My only problem may be that everyone will think I figured it out myself and expect this from me all the time!! You are wonderful.
    I deleted my previous post because I didn't know how to get rid of that attachment and then I reposted it so now we're out of order in case anyone is wondering.
    I may have more issues with this database but for now, thank you!

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

    Re: relationships and subforms (97)

    Note: This post was originally in reply to <post#=263159>post 263159</post#>, but the original poster deleted that post and re-posted it without the attachment as <post#=263433>post 263433</post#>, so this post should now be read as a reply to the latter. (Deleting the entire post was more drastic than necessary; there is a check box for deleting an attachment when you edit a post.)

    I would go with the new table, and keep records for multiple years in it. You can always filter for the current record if necessary. I made the combination of MemberID, ProgramID and Year the primary key of the table, because this combination must be unique and must be completed for each record.

    There were no relationships in the database as posted; I created them.

    The RunningInfo subform is linked to the main form by MemberID and to the Programs subform by ProgramID. In order to do this, I put a text box on the main form with control source =[subformPrograms]![ProgramID]. I left it visible so that you can see how it works, but you should make it invisible in the production version. The links take care of setting the default value for MemberID and ProgramID in the RunningInfo subform.

    I put code in the programs subform to make the RunningInfo subform visible/invisible depending on the selected program (you only want to see it for Running, Inline and Nordic.)

    P.S. You left a hidden table in the database you posted. This table contains data that look real; that is not a good idea; you may want to remove or edit the attachment. I removed all superfluous objects from the database attached to this reply, including the hidden table.
    Attached Files Attached Files

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

    Re: relationships and subforms (97)

    Just a small point about the way you respond to threads. If you notice I am responding to thread 263438 and not your original thread you put up.
    The reason to reply to a persons advice etc is that it will prompt that person by email that you have responded to them.

    The way to respond to a person's post is to click on the "Reply to this post" icon which is just under the head (Send2Friend).

Posting Permissions

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