Results 1 to 9 of 9
  1. #1
    jrichardson10 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    16

    Creating multiple records in a Subform under one Grouping in Main Form

    I am attempting to build a screening database that will ultimately house screening data from approximately 30 different affiliates.



    The basic premise is that for a screening event, there are 1 to "infinity" participants. I want to use a form to enter participant data, but I only want to enter the event data one time. So I have created a form that allows me to enter data about the event (name, date, location, host). On this form I have a datasheet subform for participant data. Is there a way to create buttons that say "Open Event" and "Close Event" where any data entered in the subform between those two button presses is automatically tied to the main form event data?

    I have attached a screen shot with what I've got, and as solid as my logic is in my head, I'm not super familiar with Access and I'm learning as I go, so feel free to include any and all pointers.

    Click image for larger version. 

Name:	Capture.jpg 
Views:	18 
Size:	101.0 KB 
ID:	33407

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Data normalization. Master table for the event info and dependent table for the participants.

    This is many-to-many relationship: each event has many participants and each participant can associate with multiple events. This means 3 tables: Events, People, EventsPeople. Data entry in form/subform arrangement: main form bound to Events, subform bound to EventsPeople with a combobox to select people.

    Unless you don't want to retain details about people (name, address, phone, DOB, etc), in which case the relationship is 1 to many.

    Regardless of which structure is used, the Master/Child Links properties of subform container control will synchronize the records.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    jrichardson10 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    16
    June7:

    I think I understand what you're saying. Fortunately, there's no longitudinal element to participant data. So it's a one to many relationship? In terms of event to participants, yes?

    If I am grasping your post, the tbl_People would go away because I don't care about tracking any given individual across events. EventsPeople is thus my subform, and I need to read up on Master/Child Links. In short though, my desired functions are possible, yes?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Master/Child Links will automatically pass the main form record ID to the dependent record thus establishing the PK/FK association.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    jrichardson10 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    16
    June7: SCORE.

    Thank you! I'll dig around in there and see what I can see. If you don't mind staying subscribed to the thread for a bit, I'm not in front of this computer every day so it may be a bit before I can really dig in. You rock!

  6. #6
    jrichardson10 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    16
    Am I limited in the number of details (variables) I can record on a subform/child form in this setup? I am trying to do some reading on this (finally) and while I think I understand the logic, I am struggling a bit with execution.

    I've gone ahead and attached my database - there's nothing confidential in it anyway. The theory is that the main form would record details about the event (name, date, location, affiliate) and the subform would record details about each of the participants in that event (age/age range, sex, race, clinical history, blood pressure measurement 1, and if applicable, blood pressure measurement 2). These details are currently recorded in tbl_Screens (my memory on naming conventions is bad, so bear with me) and the event details are recorded in tbl_Events. I suppose it's possible that we could just use the PK from tbl_Events for each of the records in tbl_Screens but that's going to drive me nuts.

    Please excuse the naming, my (even moreso than I) technologically illiterate boss has a bad habit of inadvertently wreaking havoc.

    SCOPEDatabaseBuild KEEP OUT for now.zip

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    PK from tbl_Events would be FK in tbl_Screens. Why would that drive you nuts? tbl_Screens could have its own PK field as a unique record identifier.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    jrichardson10 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    16
    I just meant that if I have to type the PK from tbl_Events every time it could get tiresome. Some events have over 200 people in attendance. Does the subform with the parent/child relationship take care of that?

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    See post #4.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Sort subform records on a main form
    By vazdajic in forum Access
    Replies: 5
    Last Post: 01-24-2018, 04:44 AM
  2. Subform not gathering all records from Main Form
    By compstudent in forum Access
    Replies: 1
    Last Post: 03-05-2015, 01:07 PM
  3. Replies: 1
    Last Post: 04-08-2013, 11:58 AM
  4. Replies: 18
    Last Post: 01-27-2012, 12:53 PM
  5. Replies: 0
    Last Post: 07-26-2010, 04:36 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums