Form with multiple check boxes per record

[Green Biro]

I have a many-to-many relationship in that any persons can have any roles.
I know that the correct way of doing this is to create a child table that
simply links the person_id and role_id together.

My problem is that (at the moment at least) the number of roles is limited
to ten and I would like a form that simply displays names and ten check
boxes - one for each role - that a user can tick as required against each
person.

In order to achieve the above, I have actually hard coded my roles as
columns in a person_roles table. Inevitably, that is making things a bit
difficult further down the line so I am thinking of redesigning and doing it
the proper way (ie child table per first paragraph) but what tequniques can
I use to create my form when the tables are designed in this way?

Thanks in anticipation of some guidance.

GB

 

[PieterLinden via AccessMonster.com]

Use the BeforeInsert event to ensure that each PersonID can only have 10
PersonRole records. use DCOUNT to get the number of PersonRole records for
the given PersonID. If it's already 10, cancel the insert. Doing this the
way you are proposing is a BAD idea.

Create a PersonRole subform based on the table, then you can choose roles
from a dropdown. The problem with using checkboxes is simple:

What happens when a new role is created? You have to redesign your database.
You'll have to trade in your green biro for a big pink eraser.

 

[Green Biro]

Yes I know what I'm doing is bad. But I really need the user to be able to
simply check and uncheck roles at will and not have to create each
combination separately. I'm pretty sure that I won't have another role in
this application but I may want to port my design for a future project. I
desparately don't want to 'bodge' my design but I also desparately want the
user to be able tick and untick the roles easily and quickly for one person
after another. If I go for correct design then I think that I'm heading
towards a form with a load of unbound controls and a load of VBA behind it
to make it do what I want it to do.

Thanks in advance for any further insight.

GB

 

[tina]

well, the most direct way to do it is to set up the tables the "proper" way,
as you've already described. then create a mainform bound to tblPeople, and
a subform bound to tblPeopleRoles (make sure tblPeopleRoles includes a
Yes/No field). each time a new person record is entered in the mainform, you
can automatically add a record for each role listed in tblRoles (currently
that would be 10 records) using an Append query, then requery the subform so
all the "roles" records show up. the user goes through and checks the Yes/No
field for the applicable roles for that person, then exits the subform. on
the subform control's Exit event, a Delete query deletes all records from
tblPeopleRoles where the Yes/No field = No (False, 0), and requeries the
subform so the deleted records don't show up anymore.

you can get fancier, for instance getting rid of the Yes/No field by putting
it in a temporary table, but that's basically it (and a Yes/No field is
tiny, so i probably wouldn't sweat it unless you're really going to have a
huge volume of records). you're going to have some bloat, because you're
repeatedly adding records and then deleting some of them, in tblPeopleRoles.
how big an issue that is depends on the number of records deleted over a
period of time. make sure you compact and backup the database regularly.

hth

 

[Green Biro]

Thank you for this. I shall give some consideration to this approach.

GB

 

[tina]

you're welcome

Thank you for this. I shall give some consideration to this approach.

GB