This question is separate from the other one since it is simply working with Access and not specifically with VB.NET

I have been trying to figure out how I can have multiple items listed in a few of the columns, that are pulling from items listed in another table. The lookup column works when I select allow multiple items, but this doesn't sit so well with VB, and now I am trying to figure out another way to make it work where I can have multiple items in a column that are listed in another table.

I have no problem if I am just wanting 1 item listed in the column, but this needs to allow for more than 1.

Thanks for any ideas and help!

In Access? Columns in listbox, combobox? Why do you need to have 'multiple items'? Is this just for display to the user? RowSource for the boxes can be a join query and then concatenate the fields you want to display together in one column.

This post has been edited by June7: 27 Oct, 2009 - 03:38 PM

I'm not sure what you mean by row source.

I would attach the database file but it is nearly 8 mb and would take me forever to upload. Here's a better explanation of what I have, and where I am wanting to get.

This database is going to hold a list of "Cake Designs". Each "Cake Design" will have several items attached to it:

Techniques Used
Pans Used
Configurations Used
Templates Used
Themes
etc...

Each one of these is a list of items that I can select to be associated with the particular "Cake Design" I am looking at. For instance, the "Pan Table" will hold a list of all the pans that I have. With each "Cake Design" I will want to select from that "Pan Table" the pans that are used in the selected design.

This was going good with the lookup columns that allowed me to select the multiple items to display, but this wont work when I attach the database to VB, so I am looking for another way to do this.

Now I have another table called "Design Details". Each one of the columns in it are one of the ID's from each of the other tables. I tried to pull the idea from Northwind, and how the "Products" "Orders" and "Order Details" work together. In order to enforce the integrity, I have to link the ID's together, instead of the lists of stuff. This is useless as I would rather see the names of the items I want to attach instead of the ID's

As you can see this is just confusing, and there has to be a more simple way of understanding how I should tie all this together.

Yes, still confusing. You are trying to build VB.net frontend? What is the data backend format?

If you are not already familiar with RowSource, you must not be familiar with list and combo boxes, so how are data selections made? Every listbox and combobox has a RowSource property which sets the source of items to list that are available to users to select from. These boxes can be multi-column. A listbox will show all the selected fields. A combobox will display the bound value in the box but the drop list shows all the fields selected in the RowSource query. Listboxes and comboboxes are controls placed on data entry forms.

You store the ID in the Design Details table but can set the field to show a different value, such as the item name from the lookup table. In Access database this property can be set in the table and then any control subsequently created bound to that field will inherit that property or don't set property in table and set the control (list or combo box) formatting properties to show the user-friendly descriptive field.

Is the file so large because of data? Make a copy of database without records and then input just enough to allow the objects to work, see if that brings the size down enough. Post here or on a file share site such as Box.com and post link here. I can look at Access projects, not VB.

This post has been edited by June7: 28 Oct, 2009 - 11:58 AM

I will explain the VB interaction if that will explain things better .

The main form will show the Design's in a details listing. It will also display lists of each of the additional tables that will not be editable. If I need to change or add anything, I'll hit an edit item button that will bring everything up in another form where I can edit all the values. In this second form the additional tables will be listed as Datagrids, and for instance, if I wanted to add more Themes to this particular Design, I will be able to select them from a listing in the Themes table.

But right now the front end isn't my main concern as I can't even get the Access file to work properly. All the additional Tables are going to work the same way, so I'll just use one of them for this explanation. I have a Table called Theme with Theme ID and Theme Name. My main table Design has the Design ID, Design Name, and other stuff. Then I have another table Design Theme Link with Design ID and Theme ID

Each Design will be able to have numerous themes attached to it. Right now I can attach numerous Theme ID's, but not the Theme Names. When I have edited the Theme ID column to be the Theme Names, with a text data type, I cannot seem to get it working properly...honestly I have tried so many things I forgot what it said the issue was.

And yes, I know much less about Access than the small bit I know about VB. I didn't even find out until last night that I could use the Access files without needing to use VB as the front end, but for some reason I have a much more difficult time of learning about Access than I do programming in VB. I would like to get this working with VB, but can always just do the look up lists to display the multiple columns on Access forms once I figure those out

CakeMaker,
I downloaded the file and tried to extract it with Windows Compression and WinZip but couldn't, just errored. Hope someone else has more success with it.