Insert a multiple selection into a table

[Steve887]

Solved: Insert a multiple selection into a table

This is probably in the wrong forum, but I'm not sure what area the answer would cover, either queries, macros, VBA etc.

I have a form, on which is a listbox with multiple selection enabled getting it's data from a query. What I want to do is for the user to be able to select multiple products from the listbox and have some VBA code or query concatenate each id and insert them into a table, separated by commas so I can separate them again for reports etc.

Eg.

Listbox:
ID
1
2
3

User selects 1 and 2 and clicks submit. Selections are concatenated to 1,2 and inserted into the table.

Please bear in mind I'm not the best at this kind of advanced databasing, so a simple or at least easy to follow answer would be very appreciated.

Thank you all in advance.

Steve

 

[Rabbie]

This is probably in the wrong forum, but I'm not sure what area the answer would cover, either queries, macros, VBA etc.

I have a form, on which is a listbox with multiple selection enabled getting it's data from a query. What I want to do is for the user to be able to select multiple products from the listbox and have some VBA code or query concatenate each id and insert them into a table, separated by commas so I can separate them again for reports etc.

Eg.

Listbox:
ID
1
2
3

User selects 1 and 2 and clicks submit. Selections are concatenated to 1,2 and inserted into the table.

Please bear in mind I'm not the best at this kind of advanced databasing, so a simple or at least easy to follow answer would be very appreciated.

Thank you all in advance.

Steve

This looks like you need to read up on data normalisation urgently. If you don't you are digging a deep hole for yourself and making things much more difficult than they need to be. Search these forums for normalisation and also google for it.

 

[Steve887]

I'm not sure it's a normalisation problem, but I had an idea about this today but am unsure how to code it.

I thought the best thing to do would be when the submit button is clicked to run a loop that gets each selected value and puts them together in a string, this string is then used in an append query to insert into the table.

Now, not being good with VBA means I have no idea how to write a loop putting each selection into a string, but maybe someone on here can point me to a tutorial or sample code for a similar thing that I can adapt to my needs.

 

[gemma-the-husky]

what rabbie is saying is that you appear to be starting with normalised data, but by concantenating rows, you will be denormalising the data, for no apparent reason

what are yuo actually trying to achieve?

 

[Steve887]

Ok, here's the table schema:

products
prodid (PK)
name
description
price

shopping_basket
basketid (PK)
custid
prodid

The listbox on the form runs from a query that gets just the id and name of each product from the product table. What I want is for the user to select one or more products from the listbox and, along with the customer id, be inserted into the shopping basket table.

What I'm trying to do with concatenating the string is so each selected product id is put into one row in the shopping basket table. Ie, if product id 1 and 3 are selected, the string 1,3 is inserted into the table.

One way I've though to do this is to use a loop that put each selected product into a string, which is what is inserted into the table.

Or am I going completely the wrong way?

 

[boblarson]

Or am I going completely the wrong way?

Yes, you are going the complete wrong way. Rabbie's comment about normalization is accurate. If you are storing data this way, then you are not using normalized data. What you should be doing if you wish to have multiple products in the basket is to actually have another table and make it like this:

products
prodid (PK)
name
description
price

OrderHeader
OrderID (PK)
CustomerID (FK)
OrderDate

shopping_basket
basketid (PK)
OrderID (FK)
prodid (FK)

 

[Steve887]

Aah, I understand. So for each selected product, you put a new row in the shopping_basket table, but each with the same orderid.

I've been looking around for some ideas on how to do this, and it looks like an arrays the way to go, but I'm not sure how to implement this. Any ideas?

 

[Steve887]

Ok, I found Lister's code: http://www.access-programmers.co.uk/forums/showthread.php?t=136282 and have adapted it to print a msgbox with productname (name of query).prodid=3 OR etc if more than one are selected.

How can I now get this to insert each selection in its own row in the shopping basket table?

Thanks again for putting up with me, this will hopefully be the last thing.

 

[boblarson]

Use a main form with the OrderHeader as the recordsource and then use a subform on it to capture the shopping basket items and link the two by OrderID.