copy list selections to a third list???

[Leanne]

I'm very new to Access, and I think--I know--I'm in over my head. But
I'll try to explain this as best I can, and hopefully someone can
help. It's pretty involved (at least, for me). But I've invested too
much time to give up on it now. I'm working on a form. Here's what I
need to do.

I have two cascading list boxes lstItems and lstVariety. For example,
lstItems--shoes, pants, shorts, tops, etc. If the user selects
"shoes," lstVariety will list tennis shoes, loafers, heels, etc.

I have this part working. Here's where I'm running into trouble:

Based on a double-click, command button, or something, I need to have
the item and variety appear in a third list box in the format
Item,Variety or Item--Variety. I don't need them moved, just copied. I
need the user to be able to go back and select another item/variety
and have that added to the third box also. Somewhat like an inventory
list. From all the possible items and types of items, they're creating
a list of those that they have.

I also need them to be able to save a copy of this list that they've
created.

I'd appreciate any help, and if there's a better way to set this up
than that I've described, please share. Thanks

 

[John W. Vinson]

I'm very new to Access, and I think--I know--I'm in over my head. But
I'll try to explain this as best I can, and hopefully someone can
help. It's pretty involved (at least, for me). But I've invested too
much time to give up on it now. I'm working on a form. Here's what I
need to do.

I have two cascading list boxes lstItems and lstVariety. For example,
lstItems--shoes, pants, shorts, tops, etc. If the user selects
"shoes," lstVariety will list tennis shoes, loafers, heels, etc.

I have this part working. Here's where I'm running into trouble:

Based on a double-click, command button, or something, I need to have
the item and variety appear in a third list box in the format
Item,Variety or Item--Variety. I don't need them moved, just copied. I
need the user to be able to go back and select another item/variety
and have that added to the third box also. Somewhat like an inventory
list. From all the possible items and types of items, they're creating
a list of those that they have.

I also need them to be able to save a copy of this list that they've
created.

I'd appreciate any help, and if there's a better way to set this up
than that I've described, please share. Thanks

It sounds like you've made a very common mistake: jumping to your Form
too soon.

The foundation of your database is your tables. Forms are just tools
to edit data in tables - you build your foundation *first*, and then
start framing in the walls and the roof!

What are the Tables that you've set up? How are they related to one
another? And... more fundamentally yet... what real-life Entities
(things, persons, or events) is your database designed to handle?

It sounds like you are trying to construct an Order for an assortment
of garments or other items...? Do you have an Orders table? Does an
Order have an associated Customer?

Just as a pointer, you will NOT want to "create a list" containing the
text strings for variety, etc. You'll instead want to load "pointers"
- Foreign Keys, to use the proper jargon - into a Table, linking that
table to your Items table and Varieties table.

Take a look at:
Jeff Conrad's resources page:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

and at the Northwind sample database that came installed with Access
to see how you might proceed.

John W. Vinson JVinson *at* Wysard Of Info *dot* com
John W. Vinson [MVP]

 

[Leanne]

Thanks so much. I'll take a look at the links you provided. To answer
your question, though, I have the following tables set up:

tblItems: ItemID, ItemName
tblVariety: VarietyID, ItemID, Brand, Type, Size
Each item can have multiple varieties, but each variety is unique. So
there's a one-to-many relationship between these two tables.

I also have qryVariety to concatenate tblVariety.[Brand], [Type], and
[Size] into one string (for appearance more than anything--I didn't
want columns in lstVariety and I wanted the fields separated by
commas.)

The way the form is currently set up is that when it loads, lstItems
pulls ItemID and ItemName from tblItems with columns set to 0"; 2".
lstVariety is blank. When the user makes a selection in lstItems,
qryVariety runs matching tblVariety.ItemID with Forms!fmCreateList!
lstItems and puts all varieties available for that item in lstVariety.

It's not really an order form--but I'll take a look at it from that
angle because that might make more sense.

I hope this makes it somewhat clearer. Thanks again.

 

[John W. Vinson]

Thanks so much. I'll take a look at the links you provided. To answer
your question, though, I have the following tables set up:

tblItems: ItemID, ItemName
tblVariety: VarietyID, ItemID, Brand, Type, Size
Each item can have multiple varieties, but each variety is unique. So
there's a one-to-many relationship between these two tables.

Well, in your original post you say:

I need the user to be able to go back and select another item/variety
and have that added to the third box also. Somewhat like an inventory
list. From all the possible items and types of items, they're creating
a list of those that they have.

If so you need someplace to STORE that list; a Listbox is a way to
*display* the values in a Table, but you can't "store the list" in a
listbox. You need another table to store the user's selections.


John W. Vinson [MVP]

 

[Leanne]

Makes perfect sense--and solved the problem beautifully.

I was so focused on the interactions between the list boxes that I
didn't even think about needing another table (recurring case of
"forest for the trees" disease, I'm afraid).

Thanks for your help. I really appreciate it.