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.

Hi mehere,

From what you are stating, it sounds like you probably ought to reconsider your table structures slightly. With 80 some products and even more ingredients, perhaps you can redo the tables slightly different. Take the products and break that down into separate tables. Example All the breads on one table, all the pastries in another table and so forth. Do the same for ingredients as well as the additives, coloring etc. Basically get everything that is relevent on a table. Then you can use the one to many relationships to create your selections from as well as simplifing your queries for your reports. I know this may seem a little daunting to you as you are just beginning to use access, but keep things simple when building your tables. Think in terms what ingredients are required by all your products, and what ingredients are only used on certain items. What coloring is used in all products and what coloring is used in only other items. Basically establish what goes with what, then build the tables accordingly. Once you have your tables set up properly, then creating a report listing products and their ingredients can be done fairly easy as well as the weights and measures.

If you get the chance find an old copy of Beginning Access 2000 VBA by Robert Smith and David Sussman published by Wrox. Their sample database is an ice cream factory, which posed many of the situations your are describing that might be of help to you.

lwells

Iwells,
thanks again for your time, I have found the book on amazon and ordered a copy.

Unfortunately I think I am going to have to do as you recommend and rebuild everything. Because I had got it working before, (although a trifle hamfisted it did print out the information I needed when I was in a hurry) I was hoping to build on the original, but I can see now that I need to start from scratch.

Thanks for recommending the book, although I have borrowed boooks from the library, they have no relevance to the project and it makes it more complicated trying to patch bits from the book into the code etc I have tried to use. It will be good to find something that has some relevance to the project I am trying to complete.

Book should be here in a few days, hopefully I won't need to bother you again, I appreciate you taking the time to point me in the right direction.

mehere

Hi I am new to this forum and new to ACCESS. I am now working on the mulit selection box code that i copy from this site but when i try to debug/ complie it, it shows an error mgs: Error in loading DLL (error 48) when it runs to the red line below.. why it happens and how to sort it out?! can anyone help me ..PLEASE... i ve been working on it for a long time. actually wat i did is:

Private Sub cmdSubmit_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("selectedhospital")
' Loop through the selected items in the list box and build a text string
If Me!lstPROCodes.ItemsSelected.Count > 0 Then
For Each varItem In Me!lstPROCodes.ItemsSelected
strCriteria = strCriteria & "tblProvider_lookup.PROCode = " & Chr(34) _
& Me!lstPROCodes.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 tblProvider_lookup ( PROCode ) " & _
"SELECT tblProvider_lookup.PROCode, * FROM tblProvider_lookup" & _
"WHERE " & strCriteria & ";"
' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.SetWarnings False
DoCmd.OpenQuery "selectedhospital"
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

thanks very much
Betsy