Data from multiple fields in same record in combo box dropdown

[markzaal]

Access 2010

Hi all,

I have a form bound to a table which has 20 (name)fields and 1 date per record. A user fills in this form first to indicate which people are present this day. I know it would be better to use 1 name per record but that would in this case not work since the form must show all names for that day before saving and closing.
I have a different form where I use 4 combo boxes and a date field. The values that can be selected in the combo boxes should come from the 20 names on the first form and with the same date as the other form. So only 4 people of the 20 indicated as present on that day can be selected to hace performed some task.
I know how to select with a combo box from different records, but how would I do it from different fields in the same record? And then also for a particular date?

Thanks,

Mark

 

[John Big Booty]

What you have is a De-normalised record structure. You will need a table structure along the lines of;

TBL_Class
ClassID (PK)
ClassDate

TBL_ClassAttendance
ClassID (FK)
StudentID (FK)​

You would need a Form/SubForm set to display and deal with this. Your main form would be bound to the table TBL_Class and the subform bound to
TBL_ClassAttendance the two tables would be linked via the common field CalssID . Check out this link for how to properly set up a Form/SubForm set.

If you need to limit the number of students per class then check out some of the discussions on the topic.