Translate Checkboxes to a Table

[alex]

I’m trying to construct a form, but am running into a bit of a
roadblock…

I’d like to create a form with a tabbed controlled subform.

On each of the tabs (2 of them) I’d like about 15 checkboxes. So the
user opens the form (with the tabbed subform) and can choose from 30
checkboxes. This data is of course stored in a table and can be
edited when the form is reopened. The unique identifier in this
situation would be something called the CaseName and is held on
another form.

What I just described can easily be done with a table that contains
the [CaseName] and 30 checkbox fields. That, I don’t believe, is the
best way to go and is certainly not normalized data.

The table should look something like this:
[CaseName] [CheckBox Identifier]
MiniMart CB1
MiniMart CB4
MiniMart CB6
ShellMart CB1
ShellMart CB10

That way I can create another table with the [CheckBox Identifier] and
a [Description] then set a relationship.

I cannot figure out, however, how to translate 30 checkboxes into the
proper table. I could probably write the data to a table, but then
how could it be edited by the user; i.e., when the user opens the form
with particular case (e.g., MiniMart) check box CB1, CB4, and CB6 are
all true and can be edited while new checkbox values are added!

Has anyone run into this situation? I hope I haven’t confused anyone.
alex

 

[John W. Vinson]

I’m trying to construct a form, but am running into a bit of a
roadblock…

I’d like to create a form with a tabbed controlled subform.

On each of the tabs (2 of them) I’d like about 15 checkboxes. So the
user opens the form (with the tabbed subform) and can choose from 30
checkboxes. This data is of course stored in a table and can be
edited when the form is reopened. The unique identifier in this
situation would be something called the CaseName and is held on
another form.

What I just described can easily be done with a table that contains
the [CaseName] and 30 checkbox fields. That, I don’t believe, is the
best way to go and is certainly not normalized data.

The table should look something like this:
[CaseName] [CheckBox Identifier]
MiniMart CB1
MiniMart CB4
MiniMart CB6
ShellMart CB1
ShellMart CB10

That way I can create another table with the [CheckBox Identifier] and
a [Description] then set a relationship.

I cannot figure out, however, how to translate 30 checkboxes into the
proper table. I could probably write the data to a table, but then
how could it be edited by the user; i.e., when the user opens the form
with particular case (e.g., MiniMart) check box CB1, CB4, and CB6 are
all true and can be edited while new checkbox values are added!

Has anyone run into this situation? I hope I haven’t confused anyone.
alex

If the checkbox user interface is essential, then you have two (both somewhat
laborious) options:

1. Have a denormalized data entry table, with code on a suitable form event to
append the checked values to your normalized table, and other code to populate
the scratch table when the user selects a record.

2. Use thirty unbound checkboxes and code in the form's Current event to
populate the checkboxes from the table, and vice versa in the form's
BeforeUpdate event (or some other appropriate event).

You're quite right about the table design; the checkbox user interface is a
pain in the neck, but sometimes essential due to user demand.

 

[Jeff Boyce]

Alex

I'm with John, trying to provide a "checkbox interface" is laborious and
fraught with potential issues, not the least of which is the tremendous
maintenance burden that accompanies adding or removing a single checkbox!

As a potential alternate approach, consider paired listboxes. The query
wizard in Access offers an example of how this looks. The basic concept is
that the first listbox shows all available items, while the second shows
only those that have been selected. You can use <Add This One>, <Add All>,
<Remove This One>, and <Remove All> command buttons to "push" the items back
and forth between the listboxes.

No, it doesn't look like a series of checkboxes ... but no, it doesn't
require intensive maintenance!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I’m trying to construct a form, but am running into a bit of a
roadblock…

I’d like to create a form with a tabbed controlled subform.

On each of the tabs (2 of them) I’d like about 15 checkboxes. So the
user opens the form (with the tabbed subform) and can choose from 30
checkboxes. This data is of course stored in a table and can be
edited when the form is reopened. The unique identifier in this
situation would be something called the CaseName and is held on
another form.

What I just described can easily be done with a table that contains
the [CaseName] and 30 checkbox fields. That, I don’t believe, is the
best way to go and is certainly not normalized data.

The table should look something like this:
[CaseName] [CheckBox Identifier]
MiniMart CB1
MiniMart CB4
MiniMart CB6
ShellMart CB1
ShellMart CB10

That way I can create another table with the [CheckBox Identifier] and
a [Description] then set a relationship.

I cannot figure out, however, how to translate 30 checkboxes into the
proper table. I could probably write the data to a table, but then
how could it be edited by the user; i.e., when the user opens the form
with particular case (e.g., MiniMart) check box CB1, CB4, and CB6 are
all true and can be edited while new checkbox values are added!

Has anyone run into this situation? I hope I haven’t confused anyone.
alex

 

[alex]

Alex

I'm with John, trying to provide a "checkbox interface" is laborious and
fraught with potential issues, not the least of which is the tremendous
maintenance burden that accompanies adding or removing a single checkbox!

As a potential alternate approach, consider paired listboxes.  The query
wizard in Access offers an example of how this looks.  The basic concept is
that the first listbox shows all available items, while the second shows
only those that have been selected.  You can use <Add This One>, <Add All>,
<Remove This One>, and <Remove All> command buttons to "push" the items back
and forth between the listboxes.

No, it doesn't look like a series of checkboxes ... but no, it doesn't
require intensive maintenance!

Regards

Jeff Boyce
Microsoft Office/Access MVP


I’m trying to construct a form, but am running into a bit of a
roadblock…

I’d like to create a form with a tabbed controlled subform.

On each of the tabs (2 of them) I’d like about 15 checkboxes.  So the
user opens the form (with the tabbed subform) and can choose from 30
checkboxes.  This data is of course stored in a table and can be
edited when the form is reopened.  The unique identifier in this
situation would be something called the CaseName and is held on
another form.

What I just described can easily be done with a table that contains
the [CaseName] and 30 checkbox fields.  That, I don’t believe, is the
best way to go and is certainly not normalized data.

The table should look something like this:
[CaseName] [CheckBox Identifier]
MiniMart CB1
MiniMart CB4
MiniMart CB6
ShellMart CB1
ShellMart CB10

That way I can create another table with the [CheckBox Identifier] and
a [Description] then set a relationship.

I cannot figure out, however, how to translate 30 checkboxes into the
proper table.  I could probably write the data to a table, but then
how could it be edited by the user; i.e., when the user opens the form
with particular case (e.g., MiniMart) check box CB1, CB4, and CB6 are
all true and can be edited while new checkbox values are added!

Has anyone run into this situation?  I hope I haven’t confused anyone..
alex

John/Jeff,

Thanks for the invaluable information! That’s what I thought
regarding the checkboxes, but hearing from people with your kind of
knowledge let’s me know I’m on the right track.

Jeff, I like the paired listbox idea!

Do you have an example of the code needed for the command button(s) in
between?

The listbox on the left would be pretty easy…the listbox on the right,
however, would need to be bound in some manner. Correct?

alex

 

[Jeff Boyce]

Actually, you can get a bit tricky with both listboxes.

The one on the left (assume left-to-right, left listbox holds "available")
could be updated after each/every item selected (i.e., 'moved to the right
listbox'), so that it only offers "remaining available items", and doesn't
show "already selected" items.

The one on the right ("selected" items) would be looking at a table (a
'junction'/'resolver'/'many-to-many' table) that holds one row for each
valid combination of "owner" and "item". Note that whatever is "owning"
these checkboxes would show up in multiple rows because the same "owner"
could have many "items".

Generically, the <Add One> command button simply checks to be sure an item
was marked in the left listbox, then adds a new record to the table with
"owner" and "item", followed by requerying both left and right listboxes to
"show" the change.

The <Add ALL> button could use a query that takes all available items and
the "owner" and appends them all to the "selected" table... but there's a
sneaky trick you can use to get around the possibility that some single
items were already selected for that owner. If you first remove all for the
owner, then the append query will not be duplicating any rows. Remember to
requery the listboxes to see the changes.

The <Remove This One> button first needs to check that one has been
highlighted, then simply deletes that record (where Owner = x and Item = Y).
Again, requery both listboxes afterwards to display the change.

Finally, the <Remove ALL> button simply deletes all records for the Owner,
then requeries the listboxes.

And if you are REALLY sneaky, your <Add ALL> button will call the <Remove
ALL> button's code first<g>!

Does this give you enough to get started?

Regards

Jeff Boyce
Microsoft Office/Access MVP


John/Jeff,

Thanks for the invaluable information! That’s what I thought
regarding the checkboxes, but hearing from people with your kind of
knowledge let’s me know I’m on the right track.

Jeff, I like the paired listbox idea!

Do you have an example of the code needed for the command button(s) in
between?

The listbox on the left would be pretty easy…the listbox on the right,
however, would need to be bound in some manner. Correct?

alex

 

[alex]

Actually, you can get a bit tricky with both listboxes.

The one on the left (assume left-to-right, left listbox holds "available")
could be updated after each/every item selected (i.e., 'moved to the right
listbox'), so that it only offers "remaining available items", and doesn't
show "already selected" items.

The one on the right ("selected" items) would be looking at a table (a
'junction'/'resolver'/'many-to-many' table) that holds one row for each
valid combination of "owner" and "item".  Note that whatever is "owning"
these checkboxes would show up in multiple rows because the same "owner"
could have many "items".

Generically, the <Add One> command button simply checks to be sure an item
was marked in the left listbox, then adds a new record to the table with
"owner" and "item", followed by requerying both left and right listboxes to
"show" the change.

The <Add ALL> button could use a query that takes all available items and
the "owner" and appends them all to the "selected" table... but there's a
sneaky trick you can use to get around the possibility that some single
items were already selected for that owner.  If you first remove all for the
owner, then the append query will not be duplicating any rows.  Remember to
requery the listboxes to see the changes.

The <Remove This One> button first needs to check that one has been
highlighted, then simply deletes that record (where Owner = x and Item = Y).
Again, requery both listboxes afterwards to display the change.

Finally, the <Remove ALL> button simply deletes all records for the Owner,
then requeries the listboxes.

And if you are REALLY sneaky, your <Add ALL> button will call the <Remove
ALL> button's code first<g>!

Does this give you enough to get started?

Regards

Jeff Boyce
Microsoft Office/Access MVP

John/Jeff,

Thanks for the invaluable information!  That’s what I thought
regarding the checkboxes, but hearing from people with your kind of
knowledge let’s me know I’m on the right track.

Jeff, I like the paired listbox idea!

Do you have an example of the code needed for the command button(s) in
between?

The listbox on the left would be pretty easy…the listbox on the right,
however, would need to be bound in some manner.  Correct?

alex

Jeff,

That definitely helps. One thing though: my lisbox on the left (the
one with all the values) is based on a table. When you select a value
on the left and put in on the right, how can that value be removed
from the listbox on the left?

 

[alex]

Jeff,

That definitely helps.  One thing though:  my lisbox on the left (the
one with all the values) is based on a table.  When you select a value
on the left and put in on the right, how can that value be removed
from the listbox on the left?- Hide quoted text -

- Show quoted text -

Jeff,

I found some code on this tek-tips site that works as an example:
http://www.tek-tips.com/faqs.cfm?fid=4246

All that I need to do now is to change the source (master table with
all values including Pkey) and destination locations...as you said: a
table that holds one row for each valid combination of "owner" and
"item".
If you have any helpful hints, don't be afraid to share.

Thanks again,
alex