60 checkboxes - is there a better way to do this

[Guest]

I have an Access 2003 db. It is a CRM and contains customer, product, and
order information for a small business. The company is getting a new online
shopping system and we need to revamp the product categories so products are
easy to find on the web and in the printed catalog.

I have a list of categories (contains 8 items) and a list of subcategories
(contains 60 items and each is associated to one of the main categories).

Each product can be associated to more than one category/subcategory. I need
to build the product entry/modification form so that the CRM user can select
the appropriate categories for each product..

As far as I know I am unable to use the combo box or list box to show the
options because the subcategory list is dynamic based on the category and if
more than one category is selected it is not clear how I would display
multiple subcatory lists in a list box or combo box. Also the customer wants
to be see all the categories/subcategories at a glance and not have them
hidden under a pull down or scrollable list.

I ended up creating a tabbed page interface for the product and made a page
with the list of all the subcategories under category headings with
checkboxes next to each subcategory. I know I need to write code to evaluate
each checkbox to see if it is checked (uggh). I was planning to make the
checkbox name contain the storage id of each subcategory (ex. chk_34). Then I
thought i would try to use a "for each" loop and "case" option (for each ctl
in frm.controls with ctl select case .controltype) to determine if it is
checked and I need to store the product no and subcategory id in the product
category table.

I haven't actually coded it yet so I don't know if this will work or if
there is a better way to do this. I figure this will required lots of coding
and I should ask if I am headed down the right path before I spend too many
more hours on it. Thanks so much.

 

[Guest]

Sounds like you will want to use a MultiSelect Listbox or a few of them...

You could then have a link table that holds all your combinations containing
e.g.
ProductID, CategoryID, SubCategoryID that would form a compund unique key.

Diificult to explain further without knowing your category structure. You
may have products that fall into more than one Category and related
SubCategories. A Category may also be a SubCategory... A link table like the
one above would still work for that but you would require enough listboxes to
handle it.

In any case, I think listboxes would be neater than loads of checkboxes...

Steve

 

[John W. Vinson]

On Fri, 3 Aug 2007 14:08:04 -0700, Krista H

Just based on the subject line alone, I'll answer "Yes, there is a better

I have an Access 2003 db. It is a CRM and contains customer, product, and
order information for a small business. The company is getting a new online
shopping system and we need to revamp the product categories so products are
easy to find on the web and in the printed catalog.

I have a list of categories (contains 8 items) and a list of subcategories
(contains 60 items and each is associated to one of the main categories).

Each product can be associated to more than one category/subcategory. I need
to build the product entry/modification form so that the CRM user can select
the appropriate categories for each product..

As far as I know I am unable to use the combo box or list box to show the
options because the subcategory list is dynamic based on the category and if
more than one category is selected it is not clear how I would display
multiple subcatory lists in a list box or combo box. Also the customer wants
to be see all the categories/subcategories at a glance and not have them
hidden under a pull down or scrollable list.

Your assumption is wrong. You can easily have a Listbox for the 8 categories,
and a second listbox, dependent on the first one, for the subcategories
appropriate to that category. With a bit more code, this can be even done with
multiselect listboxes - though if the user selects all eight categories there
won't be physical room on the screen for all 60 subcategories unless you use a
tiny font!

I ended up creating a tabbed page interface for the product and made a page
with the list of all the subcategories under category headings with
checkboxes next to each subcategory. I know I need to write code to evaluate
each checkbox to see if it is checked (uggh). I was planning to make the
checkbox name contain the storage id of each subcategory (ex. chk_34). Then I
thought i would try to use a "for each" loop and "case" option (for each ctl
in frm.controls with ctl select case .controltype) to determine if it is
checked and I need to store the product no and subcategory id in the product
category table.

OUCH. That will be *impossible* to maintain. You could - perhaps - use the Tag
property of the checkbox control to encode a value, but it's still really
barking up the wrong tree.

I haven't actually coded it yet so I don't know if this will work or if
there is a better way to do this. I figure this will required lots of coding
and I should ask if I am headed down the right path before I spend too many
more hours on it. Thanks so much.

Step back and normalize. A Table with two fields, Category and Subcategory;
base a listbox lstCategory on a query

SELECT DISTINCT Category
FROM Catgories
ORDER BY Category;

and a second, lstSubcategory, on

SELECT Subcategory
FROM Categories
WHERE Category = Forms!yourformname!lstCategory
ORDER BY Subcategory;

and requery lstSubcategory in the AfterUpdate event of lstCategory.


John W. Vinson [MVP]

 

[Guest]

I guess the article below couid give you some help designing your Access
program.

Titleon't use Yes/No fields to store preferences
by Allen Browne, July 2007

http://allenbrowne.com/casu-23.html

 

[Guest]

Thanks, this post was most helpful.