What's the Correct Way to Handle Multiple Checkboxes?

[bryan0]

Hello, I have a database with a userform that's used for data entry. Each record is for a product, and there are 15 checkboxes for the countries that can be selected for each product.



In the past, I would have created 15 yes/no fields in the database (1 for each country). But I know that's not the right way to do it. What I (think I) want is to have a PRODUCT_COUNTRY table with 2 fields: PRODUCT_ID and COUNTRY_ID. Here I would just populate them according to the checkboxes that were checked (there would be a record if it was checked, and no record if it was not checked).

My question is, how would I accomplish this in Access? Would I need an IF statement for each checkbox with an INSERT query to add it to the table if it was not there, and a DELETE query to remove it if the box was unchecked? I've seen Oracle databases used in web applications where this is done, but I'm struggling with finding the best way of handling it in Access. How are you all doing it?

[ItsMe]

I would use a combo box control, personally. Not sure if this suits your business rules/data structure.

[orange]

bryan,

It would be helpful if you could add more business context to your question as plog hinted.
What exactly are you trying to do in simple English -- no Access terms or jargon?
With more info of WHAT you are doing, there may be more focused responses as to HOW that could be achieved.
Simple terms as if you were tlking to someone who doesn't know you, doesn't know database and is not familiar with your business.

Good luck

[bryan0]

bryan,

It would be helpful if you could add more business context to your question as plog hinted.
What exactly are you trying to do in simple English -- no Access terms or jargon?
With more info of WHAT you are doing, there may be more focused responses as to HOW that could be achieved.
Simple terms as if you were tlking to someone who doesn't know you, doesn't know database and is not familiar with your business.

Good luck

Hi Orange - Thanks for the advice, here's my situation:

I have a form, and would like to be able to have the user check the checkboxes that apply for what they're working on. There will be about 15 checkboxes that they can select from, each representing a different country. Rather than have a database field for each of the checkboxes, or a multivalue field as ItsMe suggested, I'm looking for the correct way of handling how to store the user's responses into my table.

[ItsMe]

I do not recommend using Lookup fields or Multivalue fields in tables. You can create Combo Box controls on forms that will select a single record from a table. You can relate records selected from the combobox by placing the Primary Key value from the table the combo is based on in the Foreign Key field of another table.

To accomplish this you need a Normalized data structure.
http://www.youtube.com/watch?v=-fQ-bRllhXc

[orange]

When I said no Access terms or jargon, I really meant What are you trying to do.
If I remove your reference to Access form and checkbox this is what I get
I'm looking for the correct way of handling how to store the user's responses into my table.

Can you expand on this?
-So a user is responding to something and you want to record what the response by this user was to this question-

How about some more info - even a sample question and answer?

As ItsMe has said - Normalization makes querying etc much easier. I wouldn't use multivalued fields and Lookups in Tables is a recognized no-no.

[June7]

If user is allowed multiple responses then they should be saved as multiple records in a related table. This is essentially what a multi-value field does - Access 'hides' the related table.

You should fully understand multi-value fields and their limitations before committing to their use. I NEVER use them.
http://office.microsoft.com/en-us/ac...001233722.aspx
http://office.microsoft.com/en-us/ac...010149297.aspx

If you do decide to use multi-value field then the lookup in table is necessary. I also NEVER build lookups in table.
http://access.mvps.org/access/lookupfields.htm

[bryan0]

Thanks guys, and yes, I will never consider using multi-value fields.

I'm trying to store which countries we can buy certain products from. I've made some sample tables below in an attempt to better articulate what I mean.

Currently, this is what I have (I know it's not the best way, which is why I'm asking for help ):

PRODUCT_ID	AUSTRIA	BRAZIL	CHINA	FRANCE
20123	True			True
20124
20125		True	True

I also have a PRODUCT table:

PRODUCT_ID	DESCRIPTION
20123	Mouse
20124	Keyboard
20125	Monitor

What I want to do, is create a COUNTRY table like this:

COUNTRY_ID	DESCRIPTION
1	Austria
2	Brazil
3	China
4	France

...And a PRODUCT_COUNTRY table, like this, which shows which countries each product can be bought from:

PRODUCT_ID_FK	COUNTRY_ID_FK
20123	1
20123	4
20125	2
20125	4

My big question is - How do I store this information in the format of the PRODUCT_COUNTRY table above? I currently use bound checkboxes to the AUSTRIA, BRAZIL, CHINA, and FRANCE fields within the first table in this post. Do I need to use unbound checkboxes with IF statements for each of them, and do insert/delete queries in VBA? I feel like I'm missing something obvious.

[June7]

Eliminate the first table and keep the other 3.

[bryan0]

Eliminate the first table and keep the other 3.

Yes, that's the plan, but how can I configure/program the checkboxes in such a way that they support the new table structure (of adding/deleting records in the PRODUCT_COUNTRY table)?

Right now they're bound to individual fields, so I know that needs to change...

[June7]

But there would not be any checkboxes in any table therefore no checkboxes on form.

If you want to add a record to PRODUCT_COUNTRY, then do data entry by selecting Product and Country from comboboxes.

If you want to delete a record from PRODUCT_COUNTRY, then find the record and delete.

Form options for data entry/edit.

1. single form with comboboxes for product and country

2. main form bound to PRODUCTS, subform bound to PRODUCT_COUNTRY with combobox to select country

3. main form bound to COUNTRIES, subform bound to PRODUCT_COUNTRY with combobox to select product

[bryan0]

Thanks for your help June. I think checkboxes would have been nicer for the user to use, and thought I was missing something simple.

But I understand, a mainform bound to PRODUCTS and a list box bound to COUNTRIES, then I can have add/remove buttons with a subform bound to PRODUCT_COUNTRY to add and remove the available countries from the product.

[June7]

A control used to enter filter/search criteria must be UNBOUND, otherwise you change the value in record.