MS ACCESS: Populate a subform with data from another table

Question

I have three tables in my Access Database.

**Table: Participants** 
ParticipantID   
FirstName   
LastName    
Address
Phone
SiteNameLookup  
YearLookup

**Table: DailyWorkshops**   
WkshpID 
WkshpDate   
AorB    
SiteStaff   
Artists 
Activities

**Table:  ParticipantAttendance**   
ParticipantID   Participants.ParticipantID
FirstName   Participants.FirstName
LastName    Participants.LastName
WkshpID DailyWorkshops.WkshpID
Level   *(here I have a lookup to fill in HALF/PARTIAL/FULL, not just yes/no)*

I have succesfully created a form, DailyWorkshopsForm, that has a subform of ParticipantAttendance. This subform is completely blank.

How can I now populate the ParticipantAttendance subform from the Participants form so all I have to do is check off the attendance level for each participant? I can figure out how to look up just Participant.ParticipantID or just Participant.FirstName or just Participant.LastName --- but looking up one doesn't automatically fill in the others... so I could end up looking up an ID # but not get the name that goes with it.

Edit: Clarification of question & problem.

Thanks... as I look at it more I realize ParticipantAttendance is not what the subform should be... that's just the table where the attendance data should ultimately live. What I need for the subform is something that looks like this:

WkshpID ParticipantID   FirstName  Lastname  Level

6   10-45   Bart    Simpson     Full

6   10-47   Ned Flanders    Half

6   10-49   George  Washington  None

6   10-50   Michelle Obama          Full

7   10-45   Bart    Simpson         Full

7   10-47   Ned Flanders    Full

7   10-49   George  Washington  Half

7   10-50   Michelle Obama          None

8   10-45   Bart    Simpson          Full

8   10-47   Ned Flanders    None

8   10-49   George  Washington  Half

8   10-50   Michelle  Obama     Half`

Alexander Galkin Alexander Galkin · Accepted Answer · 2011-11-14T22:42:38

If you look to the properties of the subform, you'll see that there is a way to indicate a "foreign key" relation between the parent form and the subform (look at the "Data", "Link Master Fields" to "Link Child Fields"). In your case you need to link the field WkshpID of both tables.

Then just use a normal SQL query as SELECT * FROM ParticipantAttendance in your subform, the WHERE condition will be automatically imposed by MS Access using the foreign key relation from the parent form and duly updated as long as you navigate to other data row in your parent form.

MS ACCESS: Populate a subform with data from another table

1 Answers