How do I record multiple options in Microsoft Access

October 7, 2008 5:49 PM

I can't believe I don't know how to select many checkboxes in Microsoft Access...

OK, say you have a store where you sell sporting equipment. You are trying to figure out some new things to carry so you are surveying your customers and you are then entering the data into an Access database.

You have three categories of items you are thinking about selling; volleyball equipment, equestrian equipment, and hiking equipment. Each category has several items in it such as freeze-dried meals, backpacks, tents, etc. The paper form has customers fill out information about themselves and then check the box of anything they would be interested in seeing at the store.

In your database you have a table for the customer info. You also have a table where you assign each type of equipment a number and a table that associates customers and their equipment choices.

Here is the problem. How in the heck do I make an entry form that will allow me to enter customer info AND check multiple boxes of equipment types and then have it all go to the places it's supposed to? I know how to make a drop down list but they aren't choosing between these things, they could choose them all if they wanted.

I have fallen into this conundrum when I wanted to make a database where each item could be searched by several keywords. How in the hell do you do several keywords? I feel like I should know this but I don't. Yes, I am a total chote and the answer is probably right before my eyes. I have done some searching online but I don't seem to know the correct terminology to bring up relevant information.

posted by Foam Pants to computers & internet (9 comments total)

Two questions here, right? One about the customer survey, another about searching text data?

Are the equipment choices offered to the respondent being stored as additional fields in your 'customer' table, or as a third table that links to (a) the customer record; and (b) each piece of equipment selected? The first option would be terrible from a design standpoint, but would make designing your form easier (and render your 'equipment' table probably superfluous). The second option is better form, but harder to code behind. What you'll probably need to do is attach a trigger to the form's save event that does some VBA jigglepokery and manually parses the state of the checkboxes and update the database accordingly. If this is in fact what you'll need to do, I'm sure someone here can provide in broad strokes the code that will get you on your way.

Regarding the keyword question, what you're probably going to want to do is put a text box on your form ('enter search terms here'), with a "Search" button. When the button is clicked, you'll write a bit of VBA to parse the contents of the textbox and do the search like

SELECT ProductID, ProductName FROM tblProducts WHERE ProductDescription LIKE '%foo%' And ProductDescription LIKE '%bar%'

Percent signs represent a wildcard search here, and 'foo' and 'bar' are VBA variables holding the results of having split the textbox value into separate variables.
posted by Doofus Magoo at 6:01 PM on October 7, 2008

You make a separate field in the table for each possible check box.

Then you can easily search for "everyone with volleyball checked" or "everyone with saddles checked" or "everyone with both volleyballs and saddles checked."
posted by bcwinters at 6:55 PM on October 7, 2008

I essentially have three tables. One is a table of customer data, another is a list of all possible equipment choices on the survey, and the third is a table where I hook up customers with choices. The third table, which is really the meat of the operation, would look something like this:

customer 23 -- equip. 14
customer 23 -- equip. 27
customer 23 -- equip. 90
customer 24 -- equip. 12
customer 25 -- equip. 27
customer 25 -- equip. 34
customer 26 -- equip. 81

I am also equating this to something I wanted to do in the past which would be a three table setup with the third table looking like this:

collection 13 -- keyword 22
collection 13 -- keyword 56
collection 14 -- keyword 11
collection 15 -- keyword 56

I am assuming that something like this runs the keyword function here. If my logic here is flawed, I would like to be shown the light. I can't make a field for each possible check box as there are potentially 50 some possible checkboxes. YIKES! I also want to apply the same principle to a keyword driven setup where keywords are entered on the fly into a form. There could be hundreds, if not thousands, of keywords in a database like that.
posted by Foam Pants at 7:19 PM on October 7, 2008

Ahh, OK, I thought you were just stuck on the most basic setup of a table and a set of fields. My bad.

In this case I (and by "I" I mean "the guy I pay to do the tough stuff") would set up a flexgrid control (not part of Access, but developed by Microsoft and easily embeddable into an Access db) to show the list of available choces from the equipment table with VB code to allow selection of one/any/all of the items in the grid, with a "submit" button to fire off the code to insert the correct values into the "customer equipment interest" table. Doing it by hand with built-in Access form tools would be a headache.
posted by bcwinters at 7:32 PM on October 7, 2008

I'm going to disagree with bcwinters on the last suggestion. I think it would be fairly simple to create a subform of your equipment choices and embed it in your customer data form using the join table as the recordsource. I'll see if I can build you an example in the next couple of hours (if I have the time) and post back.
posted by cdmwebs at 8:42 PM on October 7, 2008

You don't need 3 tables to accomplish this. You can do it with 2 tables. This is a 1-to-many relationship. You only need a 3-table setup if you were doing a many-to-many relationship which this is not.

tblCustomer with CustomerID, Name, etc.
tblEquipment with EquipmentID, CustomerID, EquipmetName, etc.

EquipmentID would be an AutoNumber field and the key. EquipmentName is your dropdown field with all the equipment choices.

Your primary form has the customer details with tblCustomer as the RecordSource. Embed a subform using the Subform wizard with tblEquipment as the RecordSource. Choose the Tabular format. At the end of the wizard, it will prompt you to set the linking Parent and Child fields. You'll want to set it to the CustomerID field in tblCustomer for Parent and CustomerID in tblEquipment in Child. The final subform will look like a datasheet. Because the CustomerID field is linked between tblCustomer and tblEquipment, you can add an endless number of equipment choices and the CustomerID from tblCustomer will be populated into tblEquipment. To prevent editing of CustomerID in the equipment subform, you should hide the field and remove it as a tab stop.
posted by junesix at 10:49 PM on October 8, 2008

Huh. I hadn't thought of that. So, saddles have been selected 10 times, each time by a different customer (obviously). Each entry in the equipment table would look like this:



Equip ID CustomerID EquipmentName

12 43 saddle

38113saddle




It won't matter that the equipment ID is different each time as I still have consistent equipment names thanks to a drop down box. I can search for everybody who chose saddle. Is this a typical way to solve this problem? Is there any problem I might run into down the road by doing it this way? I can't see any myself. If I could reach into this screen, I would kiss you.
posted by Foam Pants at 9:42 AM on October 10, 2008

I swear, the table tags totally worked in preview.
posted by Foam Pants at 9:44 AM on October 10, 2008

Only 1 caveat I can think of off the top of my head:

This setup assumes you would only have 1 survey per customer. If you want to do multiple surveys per customer, you'll need to add an intermediate tblSurvey. The field structure would be similar except now the CustomerID field is moved to tblSurvey, and in tblEquipment, CustomerID is replaced by SurveyID. Then customers are linked to surveys (1-to-many) which are then linked to the equipment data from the survey (1-to-many). To see which customers selected "saddles", you'd have to trace it back from the equipment table, through surveys, and back to customers which isn't too difficult.

The form side is a little harder to explain but in essence, customers would have two subforms, one for surveys and one for equipment, with surveys and equipment linked through a proxy field. Message me if you want to go down this path.
posted by junesix at 12:36 PM on October 10, 2008