Relational Yes/Nos as checkboxes instead of combo box?

[Allison]

Access 2003 SP3
Win XP SP2

Using Allen Browne's tutorial ( http://allenbrowne.com/casu-23.html ), I
have set up a form with a subform that populates a combo table using a combo
box. (Complete with the three underlying tables - two mains and a
combination table.)

I'd like to do the same thing, but instead of a combo box, set up a bunch of
check boxes to perform the same function.

For instance, instead of having to select the combo box to select an option,
I'd like to set up checkboxes to select the option.

How would I do this?

 

[John W. Vinson]

Access 2003 SP3
Win XP SP2

Using Allen Browne's tutorial ( http://allenbrowne.com/casu-23.html ), I
have set up a form with a subform that populates a combo table using a combo
box. (Complete with the three underlying tables - two mains and a
combination table.)

I'd like to do the same thing, but instead of a combo box, set up a bunch of
check boxes to perform the same function.

For instance, instead of having to select the combo box to select an option,
I'd like to set up checkboxes to select the option.

How would I do this?

One possiblity would be to bind the child field (what's its datatype!??) to an
Option Group control. This will display multiple checkboxes and let the user
select only one of them.

 

[Allen Browne]

If I understand you correctly, Allison, you want to use a relational design
(3 tables with the 3rd one as the junction between the other 2), but
interface it so that there's a column with a check box for each choice (like
the bad example at the top), becuase that will be easier for the user?

It is possible to design an interface like that. One approach is to create a
temporary table with a foreign key field (such as StudentID), and a yes/no
field for every possible choice that could occur (assuming you will never
offer more than 254 different sports.) The Yes/No fields will be named
Field1, Field2, etc. Using VBA code, you would then delete any exisiting
records in this table, populate it with a record for every student (execute
an Append query statement), and loop through the sports figuring out which
sports are represented by which columns, and executing an Update query from
the data in the StudentSport table so it shows correctly. When your form
opens, you hide the irrelevant columns, set the labels above the relevant
ones, and the user can now check the boxes. In the Form Footer section of
this continuous form, you would need a Commit button that writes the changes
to the 3rd table. This would need to execute a series of queries to INSERT
or DELETE records in the junction table.

The biggest problem with that approach is that Access is multi-user. If
other users might be changing the data at the same time, the cached info in
the temporary table might be out of date by the time the user commits it,
i.e. they are inadvertantly overwriting changes from another user.

Another approach would be to use a cross-tab query rather than a temporary
table. This can easily show the check boxes at the right matrix point, but
crosstabs are read-only. You therefore need to use an unbound control that
gets focus in the Enter event of each of the other ones, and you set its
Left property so it jumps in front of the one you are interested in. In the
AfterUpdate event of this unbound control, you can execute an action query
to INSERT or DELETE the appropriate record in the junction table. Since the
action query exeuctes immediately, this solves the multi-user issue, but you
then have some other display issues to sort out, such as requerying the form
(so it hears about the update), and then finding the correct row again.

 

[Allison]

Thank you, but there needs to be functionality to select multiple or all
checkboxes, and an option group would only allow one.

 

[Allison]

Thank you Allen. This looks to be far beyond my current capabilities, and
much more work than training people how to use the database with the combo
box setup.

My coworkers are brilliant at their specific job functions, but somewhat
technophobic when it comes to software - thus the desire for checkboxes
instead of selecting multiple choices from a combo box.

Thanks anyway.