Main and subform woes

[Beemerang]

Hi All

I have a main and subform related to a training event with trainees. So the main form includes information relating to training date, location, training content etc while the subform contains a list of attendees.

The attendees come from a table that contains a list of potential attendees. I would like to set the subform up in such a way that I can:

a) Select an attendee ID from a combobox to choose a participant from the participant table and
b) have all the other fields on that record that relates to that participant I chose, populated automatically.

I'll probably need some help to ensure that I have set up the relationships between the tables correctly as well.

Is anyone prepared to give it a shot?

Thanks!
Beem

 

[EternalMyrtle]

This is just a basic Main Form/ Subform situation and your problem is probably with the table structure.

So your main form table will be:

tblEvents
EventID (PK)
TrainingDate
Subject
etc.

Then you should have your attendees table:

tblAttendees
AttendeeID (PK)
Add rest of the info you need...

Now you need a junction table:

tblEventsAttendees
EventAttendeeID (PK)
EventID (FK to tblEvents)
AttendeeID (FK to tblAttendees)

Now you make your main form events and your subform a basic query between tblEventsAttendees and tblAttendees

All will fall into place.

Make sure you make the relationships properly. If you need help with that, ask in tables

 

[Beemerang]

Thanks so much for the quick reply, I'm going to give it a shot!

Beem

 

[Beemerang]

This is just a basic Main Form/ Subform situation and your problem is probably with the table structure.

So your main form table will be:

tblEvents
EventID (PK)
TrainingDate
Subject
etc.

Then you should have your attendees table:

tblAttendees
AttendeeID (PK)
Add rest of the info you need...

Now you need a junction table:

tblEventsAttendees
EventAttendeeID (PK)
EventID (FK to tblEvents)
AttendeeID (FK to tblAttendees)

Now you make your main form events and your subform a basic query between tblEventsAttendees and tblAttendees

All will fall into place.

Make sure you make the relationships properly. If you need help with that, ask in tables

Hi Myrtle,

You are brilliant, thanks again!

Okay, so no problem with setting up the junction table and relationships, that is sorted.

Can you perhaps just give me a pointer on the best way to then create the main/subform? Do I for example, use the form wizard to add fields from the tblEventsAttendees and tblAttendees forms? Apologies for being obtuse, I'm pretty new to Access so everything seems hard to grasp at first!

Beem

 

[Beemerang]

Sorry, what if I also want to add other fields from the attendees table like gender etc that does not appear on the junction table?

Beem

 

[EternalMyrtle]

Ok, so you made sure to enforce referential integrity and you made the data type for the FK fields number? You should also create a multi-column index in tblEventsAttendees since this is a many-to-many relationship. You don't want to accidentally enter the same attendee and the same event twice...

Now you are ready to make your form. Your main form will have tblEvents as its record source. If you click on tblEvents and choose Create Form, a basic form will be made based on the data in that table.

Then, in Design view, select the subform tool. You can let the wizard help you. For now, simply make the record source of the subform your junction table (so tblEventsAttendees). Select EventID (from tblEvents) and EventID (from tblEventsAttendees) as your Master/Child fields.

Now you should be good to go. I would change the record source of the subform to a query at some point. Just go into the data tab and click on the three dots after Recordsource and add tblAttendees to it (the PK/FK fields should automatically link up if you have the correct relationships). Add the fields you need from tblAttendees and voila!

I will post some pics for you in a few mins.

 

[EternalMyrtle]

Sorry, what if I also want to add other fields from the attendees table like gender etc that does not appear on the junction table?

Beem

You should not add that to the junction table. The subform will ultimately be based on a query that links tblAttendees to tblEventsAttendees and you can add info from tblAttendees at that point.

 

[Beemerang]

Ok, so you made sure to enforce referential integrity and you made the data type for the FK fields number? You should also create a multi-column index in tblEventsAttendees since this is a many-to-many relationship. You don't want to accidentally enter the same attendee and the same event twice...

Now you are ready to make your form. Your main form will have tblEvents as its record source. If you click on tblEvents and choose Create Form, a basic form will be made based on the data in that table.

Then, in Design view, select the subform tool. You can let the wizard help you. For now, simply make the record source of the subform your junction table (so tblEventsAttendees). Select EventID (from tblEvents) and EventID (from tblEventsAttendees) as your Master/Child fields.

Now you should be good to go. I would change the record source of the subform to a query at some point. Just go into the data tab and click on the three dots after Recordsource and add tblAttendees to it (the PK/FK fields should automatically link up if you have the correct relationships). Add the fields you need from tblAttendees and voila!

I will post some pics for you in a few mins.

This is so helpful, thank you!

Okay, so I have enforced RI and used the correct datatype. To create the multicolumn index, should I simply changed the two foreign keys in the junction table to Indexed (No duplicates) or do you mean something else?

 

[EternalMyrtle]

No!! Then you wouldn't be able to enter more than one EventID or AttendeeID and that is the whole point of a junction table...

Go to this link and scroll down or search for the part that says "Create a multiple-field index":

http://office.microsoft.com/en-us/access-help/create-and-use-an-index-to-improve-performance-HA010341594.aspx#BM3

 

[Beemerang]

Okay,

I have created the main and subform with the latter based on the junction table. There are no records and no dropdowns available in the datasheet that the wizard created. Is this expected?

 

[EternalMyrtle]

You will make the drop downs yourself. There would only be record if you entered data into the table.

To make the drop downs (or "lookups") in your subform, right click on the fields that should be a lookup (so EventID and AttendeeID). Now select "Change To">ComboBox as shown in the attached picture.

Now you need to set the row source on your combo box (form properties> data tab). So, taking EventID as an example, set the rowsource to tblEvents and click on the three little buttons. Now select EventID and EventName (or whatever you want to show up in the drop down list should go in the second column or third, fourth ect. if you want to show several). The bound column should be set to 1, which is the EventID field (the PK).

Now go back to the properties and click on the Format tab. This is where you set the columns for the combo box. See picture. Select the number of columns you wish to show including the ID field (column count field). Now select the widths (column widths field). Since the ID field is not useful to a user make the first column 0".

Hope this helps.

 

[Beemerang]

Okay, I have the combobox created and it shows the columns I want. However, I receive an error message that say"You cannot add or change a record since a related record is required in [the attendance table]".

Clearly I stuffed up somewhere!

 

[EternalMyrtle]

Maybe you should post the database or at least a screenshot of the relationships window....

 

[Beemerang]

Okay, here is the table relationships screenshot. Does this look correct?

 

[EternalMyrtle]

You will need to make the lookup for AttendantID too. To make a complete record in your junction table you will need an EventID and an AttendantID.

 

[EternalMyrtle]

Yes looks good!!

You may want a contacts table for both Attendants and any other people (PersonInCharge etc.) PersoninCharge should probably be an FK field too this way you are not entering redundant data (i.e. the same person numerous times).

Also, I would suggest doing the same for locations.

If you have only a limited number of people in charge and locations you could use a simple value list.

 

[Beemerang]

Is it possible for the eventID to be automatically populated from the main form or not?

Is your "attendantID" different to my "participantID" which is the person attending the training event?

 

[EternalMyrtle]

Although your table names are a little weird

Also get rid of any spaces in field names (I see two in Events: Target Audience and Activity Description)

 

[Beemerang]

Yes looks good!!

You may want a contacts table for both Attendants and any other people (PersonInCharge etc.) PersoninCharge should probably be an FK field too this way you are not entering redundant data (i.e. the same person numerous times).

Also, I would suggest doing the same for locations.

If you have only a limited number of people in charge and locations you could use a simple value list.

I have one participants table from which I select any individual either presenting or attending training. Only one person can be in charge per training, would I still require a FK for this field?

 

[EternalMyrtle]

Yes EventID will be populated if your link matser/child fields are correctly set.

Yes ParticpantID--I hadn't looked at your screenshot yet :O)