Help Req'd: Selecting & Storing Multiple Checkbox Values

[Mala]

Hi!

This seems simple enough, but it's not working...

I am trying to add a check box control on my Access 2K
form and store the values in a field in my table.

First, when I add the check box controls (4 of them) in an
option group bound to a field, I can only pick one option.
I'd like to select any or all of them. The single value
stores in the DB fine, but again, it's only one value and
doesn't completely suit my needs.

(on the form)
X Value 1
_ Value 2
X Value 3
_ Value 4

(options 1, 3 selected)

Second, when I add the check box controls without the
option group, it becomes an all or nothing situation: all
are selected or none of them. Besides not being what I
want, it stores -1 in the DB.

I thought check boxes could toggle on and off by default
to select any of the options.

Third, when I use a list box and set the MultiSelect
option to Extended, I can *select* the items I want, but
*nothing* stores in the DB, not even -1.

What I'm hoping I can store is a list of integers that I
can pass through a lookup table to get the text.

(e.g. in the table)
1 Tom Smith 1,2,4
2 Mary Smith 2,4
3 Fred Smith 1

What am I doing wrong? Which control should I be using?
What property am I not setting/setting incorrectly to do
the multiple select with the checkboxes? What should the
DB type be for the field I'm trying to bind? I've
alternated between text (to store the text values of the
list box) and number (to store the option number of the
check boxes).

I hope someone can help me soon. Your help is greatly
appreciated. I've looked on the web and in books and am
stumped!

Thank you,

Mala

 

[Dirk Goldgar]

Hi!

This seems simple enough, but it's not working...

I am trying to add a check box control on my Access 2K
form and store the values in a field in my table.

First, when I add the check box controls (4 of them) in an
option group bound to a field, I can only pick one option.
I'd like to select any or all of them. The single value
stores in the DB fine, but again, it's only one value and
doesn't completely suit my needs.

Yes, that's the way option groups work: an option group has only one
value. So you don't want to put your check boxes in an option group,
but rather add them to the form independently. If you want them to
*look* grouped, you can just draw a rectangle around them.

(on the form)
X Value 1
_ Value 2
X Value 3
_ Value 4

(options 1, 3 selected)

Second, when I add the check box controls without the
option group, it becomes an all or nothing situation: all
are selected or none of them. Besides not being what I
want, it stores -1 in the DB.

I'm not sure I understand what you mean by "all or nothing", but my
guess is that you have bound multiple check boxes to the same yes/no
field. That's the only way I can explain what you seem to be
describing. Again, that's not what you want to do. If you're going to
use bound check boxes, each one must be bound to its own separate field.

As far as -1 being stored in the DB, that's how boolean (= yes/no)
fields are actually stored: as -1 for True, 0 for False. How this
field is *displayed* depends on what sort of control and formatting you
apply. If you set the display control to a check box, that's what
you'll see. If you set it to a text box, you'll see -1 or 0 if you
specify numeric formatting (or no formatting), and "Yes" or "No" if you
specify "Yes/No" format.

I thought check boxes could toggle on and off by default
to select any of the options.

If bound to separate fields, they will -- but each option would have to
be represented as a distinct field in the table design.

Third, when I use a list box and set the MultiSelect
option to Extended, I can *select* the items I want, but
*nothing* stores in the DB, not even -1.

That's because a single field can only contain one value, not multiple
values. So a multiselect list box has no value, and can't be bound to a
field. Only a single-select list box can be bound.

What I'm hoping I can store is a list of integers that I
can pass through a lookup table to get the text.

(e.g. in the table)
1 Tom Smith 1,2,4
2 Mary Smith 2,4
3 Fred Smith 1

What am I doing wrong? Which control should I be using?
What property am I not setting/setting incorrectly to do
the multiple select with the checkboxes? What should the
DB type be for the field I'm trying to bind? I've
alternated between text (to store the text values of the
list box) and number (to store the option number of the
check boxes).

If you need to store a list of values, you should not attempt to store
it in a single field. Properly, such a list should be stored in a
related, "child" table. For example, if you have table People
containing these records

PersonID, PersonName
------------------------
1, "Tom"
2, "Mary"
3, "Fred"

and each person may have more than one option, then you should also have
a table of Options with records like these:

OptionID, OptionName, OptionText
-------------------------------------
1, "Option 1", "Text for option 1"
2, "Option 2", "Text for option 2"
3, "Option 3", "Text for option 3"
4, "Option 4", "Text for option 4"

and a linking table PeopleOptions with records like these:

PersonID, OptionID
---------------------
1, 1
1, 2
1, 4
2, 2
2, 4
3, 1

By containing key values from both table People and table Options, the
linking table PeopleOptions identifies which person has which options.
This data structure is easy to query and manipulate using the set-based
operations that relational databases are built around.

The easiest, built-in way to enter and edit the options for each
individual using a table structure like this is to use a subform based
on the linking table, or based on a query joining the linking table to
the Options table. You *can* use other techniques, such as a
multiselect list box or a set of *unbound* check boxes, but then you
have to write VBA code to collect the data from the table and present it
in the user-interface controls you've chosen, and then to transform it
back into record in the table again when the user makes updates. It's
not actually hard to do, but you may not want to get involved in that
process, in which case a subform is easiest.

 

[Mala]

Thank you, Dick. I'm beginning to understand where I
goofed. I've set up the parent-child tables as you
suggested and am working on the subform. I don't know how
long it'll be until I get this working, so I figure I'd
thank you in advance.

Mala

 

[Dirk Goldgar]

Thank you, Dick. I'm beginning to understand where I
goofed. I've set up the parent-child tables as you
suggested and am working on the subform. I don't know how
long it'll be until I get this working, so I figure I'd
thank you in advance.

Thanks for letting me know. If you run into problems, post back to this
discussion thread.