How to Dynamically Create Checkboxes On Access Form

Question

I have an access database with employee names, and products and product id's. When an employee name is selected from the combobox on the form, I then need checkboxes generated for each of the items and item id's concatenated. So if James has 4 items, I would need 4 checkboxes created with itemID - Item

Then on a button press event on the form, I need to be able to capture which checkboxes are checked.

How is this done in Access 2016 %2010?Logo Design

Answer 1

Create the maximum count of checkboxes you will need/accept.

Set their Visible property to False.

When selecting an employee, set the desired count of checkboxes to visible.

Answer 2

What is the underlying model.  Where there is a many-to-many relationship type between employees and products, as seems to be the case here, this would usually be modelled by three tables, Employees, Products and EmployeeProducts, the last modelling the many-to-many relationship type by resolving it into two one-to-many relationship types:

EmployeeProducts

….EmployeeID  (FK)

….ProductID  (FK)

The primary key of the table is a composite of the two foreign keys.  Other columns can be included in the table to represent non-key attributes of the relationship type , e.g. quantity.

The usual interface for this would an Employees form, in single form view, with an EmployeeProducts subfrom, in continuous forms view, embedded in it, liked on EmployeeID.  An alternative would be, as Duane mentions, a multi-select list box rather than a subform.  This requires code to be written to read/write data from/to the table, however, whereas a subform is code-free.  A list box, as would check boxes, would also not allow non-key attributes to be recorded.

You'll find examples of both approaches in StudentCourses.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.

As you'll see the use of a subform allows the Status attribute of the StudentCourses relationship type to be recorded in the subform, whereas the list box does not.  The other possible solution would be the use of a Multi-Valued Field, for which the interface is a combo box, but again this would not cater for non-key attributes of the relationship type.

Answer 3

It's very difficult to create controls on forms if not in design view. Most of us would use multi-select list boxes to achieve similar functionality. 

Can you share your table structures? What do you mean by "capture which checkboxes are checked"?