MS Access - populate checkbox list from table

Question

I'm using a local Access database. Let's say I have 3 tables -

Projects, Contractors, Project_Contractors (linking table, multiple contractors can work on multiple projects).

I'm building a form to be used to create a new Project entry. I want this form to have a checkbox list of all the Contractors, so the form will add a new entry to the Projects table as well as populate the linking table.

I'm very experienced with SQL, but not with Access. Is there a way I can populate a list of form options directly from the Contractors to complete this? I'm using Access 2013.

You can't add controls to a form at runtime without switching your form on design mode, add controls, save the form and return to normal mode. This is a terrible solution. Also I don't know how many contractors you might have but if it's hundreds or more, the solution is not applicable from a space, resources and practical point of view. The solution of Christopher below is the best approach I can think off — Thomas G
– Thomas G, Commented Mar 10, 2016 at 20:08
Must you use checkboxes? Is it a stylistic choice or a requirement? — MoondogsMaDawg
– MoondogsMaDawg, Commented Mar 10, 2016 at 22:02
Checkboxes would be ideal, there won't ever be more than 10-15 Contractors. I can give the listbox option a shot tomorrow and see how it looks, thanks. — Kyle H
– Kyle H, Commented Mar 11, 2016 at 4:26

MoondogsMaDawg · Accepted Answer · 2016-03-10 22:01:37Z

A listbox control is pretty good at accomplishing this. With larger datasets it can be clunky to scroll through them all, and if you can't make it tall enough to show all rows you also lose visibility on previous selections, but it's pretty easy to set up and link to the Contractors table (you should be able to do it through the wizard, or type some SQL into the control source).

Turning on the multi-select property of the listbox should do what you are looking for. MultiSelect Property

Getting the selections out of the listbox to generate your append queries I think requires VBA. Here's a link explaining how to accomplish it. Clicky

EDIT: to more directly answer your question, you could use checkboxes by adding a boolean field (there's a checkbox option there) to your Contractors table and use the table as a subform in your entry form. I personally think that's bad design, and the steps to clean it up make it way more complicated than using a listbox.

Collectives™ on Stack Overflow

MS Access - populate checkbox list from table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related