Multi-Select List Box

  • Thread starter Thread starter Buddy
  • Start date Start date
B

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.
 
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
 
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.
 
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
 
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.
 
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
 
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
 
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.
Click to expand...

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

Thanks.


Kelvin said:
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 said:
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
Click to expand...
need
to entries
Click to expand...
Click to expand...
 
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.
 
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

Buddy said:
When you say the following
Note you need single quotes ' around txtList since this will be text
Click to expand...
with
a
comma in it. Without the ', the code will fail.
Click to expand...

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

Thanks.


Kelvin said:
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 " &
Click to expand...
Me.txtID
&
", '" & txtList & "';"
DoCmd.RunSQL strSQL

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

Kelvin
Click to expand...
depending
on that
gets need
Click to expand...
Click to expand...
 
Back
Top