Multiselect List Box issues...

[ozzy68]

Hello forum,
I have a form that I use to populate a table that schedules and records training events for employees. I have many combo boxes that I use to populate most of the fields in the table, and one list box that I want to use to select one or more employee/s to add to the table. When I popolute the form, everyhting shows up on the table except for the employee name/s highlighted in the listbox. I am sure I need to create an event procedure to write the item/s selected in the listbox to the table field "LastName", but I am not good with VBA at all...is there a simple code that I can use to accomplish this?

 

[Fuse3k]

Are you trying to create a new record for each item selected in the listbox or are you trying to add those names to a single field in a row?

 

[ozzy68]

Thanks for getting back to me...

There are may fields of information, LastName is one of the fields for each row. So the best way to answer your question would be to say that I am creating a new record each time I populate the form. So, if I were to select two names from the list box, and populated all of the combo boxes with training event information, I would see two of the same training event records each with a different name in the LastName field.

 

[AccessMSSQL]

Here's some code to loop through the list box and obtain the items selected:

Dim strEmployees as string
Dim Item as Variant
strEmployees = ""
For Each item In Me.lstEmployees.ItemsSelected
       strEmployees = strEmployees & "," & Me.lstEmployees.ItemData(item)
Next
strEmployees = Right(strEmployees,Len(strEmployees)-1) 'this removes the leading comma

Not sure how you are storing your list of employees but this examples returns a comma delimited list.

Hope this helps!

AccessMSSQ.com

 

[AccessMSSQL]

Oops..sorry I just read the 2nd post - I see you need to add multiple records. Here is a modification to the code.

Dim Item as Variant
Dim strSQL as string
For Each item In Me.lstEmployees.ItemsSelected
       strSQL = "INSERT INTO TrainingEvent (Field1, Field2, Employee) VALUES (" & combo1 & "," &  combo2 & ",'" & Me.lstPeriod.ItemData(item) & "')" 
     currentdb.execute(strSQL)
Next

Of course you would have to modify the insert statement to include all of the fields you need. NOTE: text fields need to be surrounded by the single quotes.