MultiSelect List Box

I'm a novice user and I need help writing code to to work with a multiSelect list box. I would like to have the selections made from the list box on my form stored in a table. Can someone tell me how?

Thanks

This is the basic idea for extracting selected data from a MultiSelect listbox.

'This requires Option Base 0 for the array to be declared in the Module Header.

'Start of the procedure

Dim intCount As Integer
Dim varSelected() As Variant
Dim intSelected As Integer
Dim varItem As Variant

intCount = 0: intSelected = 0

intCount = Me!ListBox.ItemsSelected.Count

If intCount = 0 Then Exit Sub ' Nothing selected.

ReDim varSelected(intCount) ' array

For Each varItem In Me!ListBox.ItemsSelected

varSelected(intSelected) = Me!ListBox.ItemData(varItem)
intSelected = intSelected + 1 ' increment the array

Next varItem

' Each varSelected(intSelected) contains a selected data row

'End procedure

Thank you for the help. Can you explain how to then take the items selected and input that data into a table?

That's a good question. I use DAO to update my data tables. In essence, one can embed the Listbox loop into a recordset loop, or recreate the same loop within the same procedure based on the array count (intSelected). Both work fine and it's a question of context. Not having seen your design, I assume that you're working with macros, an area that I largely bypassed, so you should look for macro equivalents of appending or updating data tables.

Well I think I have definitely gone beyond my experience level! My design is pretty straight forward. Basically I have a table that provides the source of items for the listbox on my form and then I just need to figure out a way to track and store the selections made from the listbox so I can generate a report later. Unfortunately I don't have much experience writing code. Is this an unusual request for a listbox? While researching how to do this I can't seem to find any examples of code writing the data back to a table.

You can always add a Field to your table named Selected with a data type of Yes/No. Then using your list box On Click Event type the following code:

'Create a tempory copy of the recordset from the table
Dim curDB As Database, rs As Recordset, SQLStmt As String
Set curDB = CurrentDb()
SQLStmt = "SELECT [tblID], [Selected] FROM [tblName] WHERE [tblID] = '" & Me![listbox] & "'"
Set rs = curDB.OpenRecordset(SQLStmt, DB_OPEN_DYNASET)
'Change the Selected value to Yes/No when the items are selected or de-selected
Do While Not rs.EOF
rs.Edit
If rs("Selected") = True Then 'Now loop through each record and
rs("Selected") = False 'change the Selected field to either
Else 'Yes or No Only records marked Yes
rs("Selected") = True 'will be used to display the Records
End If
rs.Update
rs.MoveNext
Loop
rs.Close 'Now close the temporary copy of records

You will need to use the table ID, table Name and List box name that you used in your application and substitute it in the italized ones in this example. You will have to use the multiselect set to None for this to work. This will place a Yes on each item selected so you can run a query later on to see which records have been selected from the list box for your report. You can also make another list box based on the same query to see which records have been selected.
You can only select one record at a time, but would work if you don't have to select alot of records at any one given time.
Just another suggestion

Good Luck
lwells

Well I couldn't resist, had to figure out a way to select items from a multiselect list box and put them into a table. First I made an append query to add records to a table. I use the Products table from the Northwinds Database to use as the table to select products from. I made another table for the selected products to be stored in. I named the append query qryProductsSelected and the table that stored the information tblProductsSelected. The list box named lstProducts contained the all the Products from the Products table. I made a command button on a form named cmdOK along with the list box to take the items selected in the list box move them into the append query as a string and then run the query to add those records to the table. In essence I think this is what you wanted. Here is the code I used:

Option Compare Database
Option Explicit
Private Sub cmdOK_Click()
On Error GoTo Err_Handler
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryProductsSelected")
' Loop through the selected items in the list box and build a text string
If Me!lstProducts.ItemsSelected.Count > 0 Then
For Each varItem In Me!lstProducts.ItemsSelected
strCriteria = strCriteria & "tblProducts.Product = " & Chr(34) _
& Me!lstProducts.ItemData(varItem) & Chr(34) & "OR "
Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - 3)
Else
MsgBox "Must Select An Item From The List First"
Exit Sub
End If
' Build the new SQL statement incorporating the string
strSQL = "INSERT INTO tblProductsSelected ( Product ) " & _
"SELECT tblProducts.Product, * FROM tblProducts " & _
"WHERE " & strCriteria & ";"
' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryProductsSelected"
DoCmd.SetWarnings True
' Empty the memory
Set db = Nothing
Set qdf = Nothing

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description
Resume Exit_Handler

End Sub


Now you have a table showing the items that were selected by the user. You might add some additional columns to give you enhancements such as a date or the name of the user that selected the items etc so that you can identify when and whom made the selections from the list box later on.

This was a nice challenge...thanks,
lwells

lwells,

Thanks for figuring out that code. I am trying to use it in a database that I have created but am not sure what to substitute for DAO.database and DAO.query since I too am a novice user. I have figured out the other substitutions (for the fields, tables and queries), but am stuck on this one.

Thanks

Hi eschaffer,

The DAO is a reference to a library. Open your code window, go to tools/references and put a check in the checkbox next to the Microsoft DAO 3.XX Object Library. The XX is dependant on the version of Access you are using. The libraries are listed in alphabetical order so scroll down until you find it. Then select Debug/Compile to compile the database. If you receive any additional errors, post back to what they are.

lwells

Thanks!

Iwells thanks for the code, I have been trying to do a similar thing for 3 days and could not puzzle it out.
Is there anyway now that all items selected can be put in one field in the table (concated) I have had a try but cannot figure it out.
It's my first project with Access and am trying to include a list of ingredients for various products, however each product has up to 15 ingredients and I would like them all in the one field if possible.

Thanks again

mehere

Sorry to come in at the end, but am just curious at what you are trying to do.

What I think you are trying to do is have a list of "Products" that you want the user to be able to multi-select and then have the selection viewed either by report, form or query.

If I am not out of order and am correct let me know, I will post you some sample databases.

I have one that I use and it has "Categories" of Customers. If you select Government + Religious + Primary, you would get a form or report or query showing all the customer details who fit the categories selected.


Regards,


John A

You can take the above code and modify it to fit your needs. For example to get the items selected into a concantenated string and add it to your table as a new record in one field:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM NameofTable"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

For Each varItem In Me!NameofListBox.ItemsSelected
strCriteria = strCriteria & ", " & Me!NameofListBox.ItemData(varItem)
Next varItem

'Remove the comma after the last item selected from the string
strCriteria = Left(strCriteria, Len(strCriteria) - 2)

rs.MoveLast
With rs
.AddNew
rs!NameOfField.Value=strCriteria
.Update
End With

Set db = Nothing
Set rs = Nothing

This would put a concantenated string into one field, but I do wonder why you want to do that. Concantenated string data should never be stored in a table. Data should always be broken down to the smallest amount of data per field....then let your queries or code concantenate the data together for viewing. I would really advise not doing this or at least look at the reasons why it is important to have this string in one field and not one item per field.

Anyway, there you go.
Cheers,
lwells

To be honest, I don't know why I even started to do this either!! thanks very much for taking the time to read the post and providing the extra code.

We operate a small bakery company and need to print out specification (weights, sizes, ingredients etc) for 80 of our products, to begin with I set up a basic database in access and then used word to print out the relevant information. I tried to use access report to print out the info, but it would not accept the amount of fields I wanted to use.

After setting it up in a hurry as mentioned above all worked fine, except for the ingredients of which there could be 20 for each product, Icolud not find another way of printing them through Word, other than using a seperate field for each ingredient, with a list box in a form for each ingredient then going through each list and selecting required ingredients for each product.
I eventually got this down to 6 list boxes by combining some ingredient names, but it is still not practical, fine if I am doing it but no good if someone else steps in.

I also have the same problem with Additives and colorings, they all need to be added seperately. I now have a table with 83 fields.

What I ewas hoping to acheive by concating the text was to make the field number less and also offer the operator one list box to choose from and all the ingredients or additives appear in one list box instead of 6.

This is my first main use of access and I am completely lost with it, I have borrowed books from the library but am still finding it difficult to get to grips with it.

Thanks again for your time, if there is a better way of acheiving what I want I would be very grateful to hear it.

Ahh you don't want to store the fields like that. That will double the amount of data in your database. Prolly more then that actually since simple numbers will have to be stored as strings.

For a report, use a query that puts the data together into 1 string, then just display the results of that query. Were you trying to put a textbox for each field? Don't do that; too much work for the computer and yourself.