MultiSelect Listbox 1

[Icecream7]

I have been working on a form that has several listboxes that are set up for multiselection. When the user clicks the save button the choices write to a table. What I am having a problem with is getting all selections in the listbox to write to the table.So far I am only able to get the first selection. I know I need to throw a multiselect in but I'm not sure where. I've got a different post that explains more at

http://www.tek-tips.com/viewthread.cfm?qid=1250894&page=1

Here's the code I have so far.
'Start loop to process all items selected in the list boxes
For Each varItem In Me.lstBathroom.ItemsSelected
.Fields("lstBathroom") = Me.lstBathroom.Column(0, varItem)
.Update
Next varItem

 

[Remou]

I think it would be best to create the list firts, then update the field, otherwise you have to add to the field. So:
[tt]For Each varItem In Me.lstBathroom.ItemsSelected
strSelected = strSelected & Me.lstBathroom.Column(0, varItem)
.Edit
.Fields("lstBathroom") = strSelected
.Update
Next[/tt]

 

[Icecream7]

Thanks Remou, I will try that. I need some advice on something......As I've said I have about 20 listboxes on this form, with the table I'm writing to should I have a cell for each listbox or just generic cells that are only for the ones chosen? Right now I have corresponding table cells for each listbox but I know that they won't be choosing something out of each listbox to create the bid. When I asked them they thought they would choose maybe 10 items all together.

 

[Remou]

Have you looked at the various links on normalisation that you find dotted around Tek-Tips? I ask, because it seems to me that you need a second table to contain these details. For example:

MainTable
MainID

DetailsTable
DetailsID
MainID
Category
Description
Value

Details might contain:[tt]
DetailsID MainID Category Description Value
1 1 Bathrooms Fancy 2
5 1 Gardens Big True[/tt]

 

[Icecream7]

Thanks again, I will work on seperating the table so it isn't an overload on the main table.

 

[Icecream7]

Ok so I have been working with seperating the table but running into a wall with writing to the other one. How can I get it to write to a new row on the table? Do I create a new recordset and can I have them both set at the save button? With the strSelected code it is writing the options to the table great but I haven't been able to get a comma in so its just Kit1001Kit1002. Any help?

Sorry if I'm a pain, I'm new to this and really appreciate the help.

 

[PHV]

To get a comma separated list:
With Me!lstBathroom
For Each varItem In .ItemsSelected
strSelected = strSelected & "," & .Column(0, varItem)
Next
End With
MsgBox Mid(strSelected, 2)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886