Form with Many CheckBoxes

[Shep]

Sorry, but this is killing me. I can't find anything in the archive which directly relates.

And it's complicated. I'll try to simplify.

In a nutshell:

As I scroll each Employee on a Parent form, I'd like a subform with functionality to check off any Certifications earned by this person and write the EmployeeID and CertificationID to a junction table.

Seems to me it should be simple, but...


Anyway, here is the probably overcomplicated version here:

The one side tables are 1. Employees and 2. Certifications
The junction table records which certifications employees possess. This table contains some Boolean fields.

The Parent form knows the EmployeeID.

My goal is to create a subform on which to display the full list of Certifications, something similar to the attached image, with a button to add an EmployeeID to the junction table AND tick off the Boolean checkboxes.

I've based the subform on a query showing ALL Certifications and all (Junction) records where there is a match

Because the junction table only stores related Employees and Certifications as entered, the checkboxes on my subform cannot be bound directly to the fields in the junction table and, at the same time, allow me to check off the Booleans.

Perhaps a little db redesign? Any help greatly appreciated.

 

[MarkK]

Your check boxes and dates should be in your junction table.
Here's an example with a certification subform you can use on both your Employee and Certification parents. You can add an Employee or Certification depending on the context and dates and check boxes work.

 

[Shep]

Thank you for your reply and example. I was wanting to create a continuous subform which displays all Certifications dynamically, with the ability to add records to a junction table based on the Parent Form's current EmployeeID, not a datasheet. Nonetheless, your example helped point out what could not be done quickly and easily.

After spending a few days on this form, I realize my question should have been, how do I accomplish this with checkboxes that are not greyed out?

Since the checkboxes are based on fields in a junction table, they are Null (hence grayed out) unless a record exists for that Certification and since unbound checkboxes, when changing states, change for every row, I couldn't figure a way to do what I wanted.

So, I've done it differently.

Thanks again.