Multiselect list to table 1

[psandekian]

Hi. I am using Access 97 and I have several tables set up to help me populate a different table using a form. My form has 4 list boxes that allow the user to make multiple selections in each.

After making the selections, I look at the table and there is nothing there from those list boxes. My other items from the form are there. Am I doing something wrong? I'm fairly new to Access and I hope I don't have to program this. I have no experience programming in Access.

Thanks!
Patty
psandekian@earthlink.net

 

[jebry]

Hi Patty!

Bad news! To use multiselect list boxes, you will need to write some VB to store the information in a table. Your options are to make the tables single selection and the users can make their selections one at a time(for this you will probably want to use combo boxes instead of list boxes). And, if you want, I can help you the programming you need to store the information. I would need to know how your table is set up and how you expect to store the data.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[psandekian]

Thanks Jeff. I am new to Access but not VB so I should be able to keep up. Here's my set up: I have 6 tables and 1 form. The tables are: colors, artists, occassions, stamp sets, supplies, techniques, and samples.

The idea is to make my life a little easier by allowing me to select in my form the different colors, supplies, techniques I used in my samples (I have more than a hundred to input and most use several colors, lots of supplies, etc...).

So for instance, pines3.jpg is the filename of my sample. It uses

stamp sets: Pines, Creative Corners
colors: Creamy Caramel, Forest Foliage CS & Chocolate Chip Pad
supplies: corrugator, twine
techniques: corrugator, layers
occassions: Father's Day, Manly, Nature

What I'm hoping to have as an end product is a table of all of my samples so I can query it and pull up the ones I want to look at instead of searching by hand. I am going to try to incorporate this database into my website eventually. I'm learning ASP so I can do this.

While I'm thinking of it, if I have several techniques listed together in one entry, can I query for one and still get that sample pulled up? For example, if I have corrugator, layers, and masking for techniques and I query for layers, will it come up?

Sorry for being such a novice! Thanks for your help!
Patty
psandekian@earthlink.net

 

[jebry]

Hi Patty!

To do this properly you will need to store your colors, techniques etc. one per record with some id field to link it to the sample table. So your records will look like this:

stamp sets:
pines3, Pines
pines3, Creative Corners

colors:
pines3, Creamy Caramel
pines3, Forest Foliage CS
pinese, Chocolate Chip Pad

supplies:
pines3, corrugator
pines3, twine

techniques:
pines3, corrugator
pines3, layers

occassions:
pines3, Father's Day
pines3, Manly
pines3, Nature

To accomplish this with multiselect list boxes, create a command button to add records and use this code:

Dim varRow As Variant
Dim rst As DAO.Recordset

Set rst=CurrentDb.OpenRecordset("YourColorTable", dbOpenDynaset)
For Each varRow In Me!YourColorList.ItemsSelected
rst.AddNew
rst!SampleField = Me!txtSample
rst!ColorField = Me!YourColorList.Column(0, varRow)
rst.Update
Next varRow

Set rst = Nothing
And start over for the next list box, setting the recordset to the next table.

This will give you a set of records for each category which links back to a specific sample. This set up makes it easy to search for the information about your samples and to do any backward search for samples that use certain techniques or colors etc. It will also make it easy to do crossmatches such as does any sample use brick red and a layered technique. If you need some help with SQL to do these things let me know.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Guest_imported]

Hey, I have a similar problem, (I think).. I have a list box where you can select multiple options, but it doesn't show up in my table. I don't know how to fix this.

 

[jebry]

Hi 43y55gh!

You are correct, you will need to do something similar. When you change a list box to multiselect its value is null and will remain null so that is why you get nothing in the table. You will need to write code like whats above to add a new record for each selection.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Guest_imported]

Oh sweet god almight, I don't think I can do that.. Is there any way I can get away without using a command button?

 

[psandekian]

Jeff,

I added tables that you suggested so now I have:
Samples, Artists, Colors, ColorField, Occassions, OccassionsField, Stamp Sets, StampSetsField, Supplies, SuppliesField, Techniques, TechniquesField. The ones with Field on the end are the ones that will be written to. The ones without (excluding Samples) you might call my "Source" tables.

I took the color, stamp sets, supplies, techniques, and occassion columns out of my Samples table. Since doing this, I've had to recreate my form. But now I'm getting "Type mismatch in JOIN expression." I'm not sure how to get around this. I was using my source tables as my list source. I read that it is a common error for having one listed as a memo and the other as a text. But this happens straight from the wizard without me changing anything. So I'm stuck. Any pointers?

Thanks!
Patty
psandekian@earthlink.net

 

[jebry]

Hi Patty!

I'm not sure what is happening but it seems that you need to check the fields you are joining on in your queries to make sure they match for instance an autonumber matches to a long integer. If you want to email me a zipped copy of your Db, I'll look at to see if anything jumps out at me.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[psandekian]

Ok. So I got the type mismatch fixed but now my form does not show anything when I open it. It's all there in design view but not in form view. Is this related to having the form associated with several tables? Do I need to use VBA to show the form?

Thanks!
Patty
psandekian@earhtlink.net