Multi-Select List Box

[Buddy]

Can anyone tell me why it is when I try and select multiple entries in my
list box they do not get inputed into my table? If I change multi select to
none then i can select one entry and it does get loaded into my table, but
when i change it to simple it does not.

Thanks.

 

[Kelvin]

Buddy,

Multi-select list boxes do not store data. It is just a tool to be able to
select items from a list. To actually store the data you will need to code
the box to store the selection to another table or to a variable for later
use.

Kelvin

 

[Buddy]

okay, then how can i have a multi-select box that is populated from table
'a' and when multiple selections are made they are stored in table 'b'?
i really don't have a clue about the coding, i just use the basic operations
that are given in properties.

thanks.

 

[Kelvin]

Buddy,

What will you do with the data? The code will be different depending on
what you want to do. If you are just storing the data, thats easy. If you
want to generate a report or a query, then it gets a bit tricky. If you
want to then take this list and repopulate the multi-select box, that gets
trickier.

Kelvin

 

[Buddy]

Just want to store the selected data in a single table field with a ','
between each selected item.
Is this something that is easy or tricky?

Thanks much.

 

[Gerald Stanley]

It is tricky because your database design is not
normalised. Each selected item from the list box should be
stored on another table as an entry in its own right
together with the primary key of your table.

Hope That Helps
Gerald Stanley MCSD

 

[Kelvin]

Buddy,

Create your table with 2 fields. 1) the ID and 2) the SelectedItems. I'll
call this table tblItems.

Create a button next to your list box to run the following code.

Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim txtList, strSQL As String

Set frm = Forms!frmNameOfForm
Set ctl = frm!txtMultiSelectList
For Each varItm In ctl.ItemsSelected
txtList = txtList & "," & ctl.ItemData(varItm)
Next varItm
txtList = Mid(txtList, 2) 'to remove the first comma
strSQL = "INSERT INTO tblItems ([ID], [SelectedItems]) SELECT " & Me.txtID &
", '" & txtList & "';"
DoCmd.RunSQL strSQL

Note you need single quotes ' around txtList since this will be text with a
comma in it. Without the ', the code will fail.

Kelvin

 

[Buddy]

When you say the following

Note you need single quotes ' around txtList since this will be text with a
comma in it. Without the ', the code will fail.

Do you mean in the entire code or are you just referring to one single
instance?

Thanks.

Buddy,

Create your table with 2 fields. 1) the ID and 2) the SelectedItems. I'll
call this table tblItems.

Create a button next to your list box to run the following code.

Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim txtList, strSQL As String

Set frm = Forms!frmNameOfForm
Set ctl = frm!txtMultiSelectList
For Each varItm In ctl.ItemsSelected
txtList = txtList & "," & ctl.ItemData(varItm)
Next varItm
txtList = Mid(txtList, 2) 'to remove the first comma
strSQL = "INSERT INTO tblItems ([ID], [SelectedItems]) SELECT " & Me.txtID &
", '" & txtList & "';"
DoCmd.RunSQL strSQL

Note you need single quotes ' around txtList since this will be text with a
comma in it. Without the ', the code will fail.

Kelvin

Just want to store the selected data in a single table field with a ','
between each selected item.
Is this something that is easy or tricky?

Thanks much.

If
you be
able

need

to entries

 

[Buddy]

I'm not sure i follow:

example...

tbl_items - single column of items (milk, cheese, bread, etc)
tbl_orders - multiple columns (customer, address, phone, ITEMS, email, etc)

form_itmes - updates tbl_items
form_orders - allows you to choose from a list of items (tbl_items) through
a listbox

I am trying to save what every items are selected in form_orders in
tbl_orders. This seems simple enough in theory, can you help?

Thanks.

 

[Kelvin]

For the line that starts "strSQL =", which has actually been wrapped into 2
lines, at the end there is the word "txtList". This variable is actually a
string so you have to include the single quotes around it. Heres what I
mean. If txtList = "1, 2, 3" then

strSQL = "Select " & txtList

will get translated as

Select 1, 2, 3

The select statement is now looking for 3 fields called 1, 2, and 3.

strSQL = "Select <'>" & txtList & "<'>"

Ignore < and >, they are used to highlight the apostrophe. This will be
translated as

Select '1, 2, 3'

This will translate '1, 2, 3' as one field.

Kelvin

When you say the following

Note you need single quotes ' around txtList since this will be text

with

a
comma in it. Without the ', the code will fail.

Do you mean in the entire code or are you just referring to one single
instance?

Thanks.

Buddy,

Create your table with 2 fields. 1) the ID and 2) the SelectedItems. I'll
call this table tblItems.

Create a button next to your list box to run the following code.

Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim txtList, strSQL As String

Set frm = Forms!frmNameOfForm
Set ctl = frm!txtMultiSelectList
For Each varItm In ctl.ItemsSelected
txtList = txtList & "," & ctl.ItemData(varItm)
Next varItm
txtList = Mid(txtList, 2) 'to remove the first comma
strSQL = "INSERT INTO tblItems ([ID], [SelectedItems]) SELECT " &

Me.txtID

&
", '" & txtList & "';"
DoCmd.RunSQL strSQL

Note you need single quotes ' around txtList since this will be text

with

a
comma in it. Without the ', the code will fail.

Kelvin

depending

on that
gets need