Can you combine the following code to include two listboxs instead of just one? I am trying to get the code to work where the users choices multiple employees and multiple classes and it will add a recordset for the employees choosen for each class choosen. For example if I choose John Doe, Jane Doe, John Smith, and Jane Smith then I choose the classes that they should be in like Class I, Class II, and Class III. I would like the information to go to the table as such:
John Doe Class I
Jane Doe Class I
John Smith Class I
Jane Smith Class I
John Doe Class II
Jane Doe Class II
John Smith Class II
Jane Smith Class II
John Doe Class III
Jane Doe Class III
John Smith Class III
Jane Smith Class III
Here is the code I am using. Just need to know a better way.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant
Dim Item As Variant
On Error GoTo ErrorHandler
Set db = CurrentDb()
Set rs = db.OpenRecordset("Class Requirement Table", dbOpenDynaset, dbAppendOnly)
Msg = ""
Set ctl = [Forms]![Class Requirement Form]![List63]
For Each varItem In ctl.ItemsSelected
rs.AddNew
rs![CLASS NAME2] = ctl.ItemData(varItem)
rs![EMPLOYEE] = [Forms]![Class Requirement Form]![List65].ItemData(Item)
rs.Update
Msg = Msg & [Forms]![Class Requirement Form]![List65].ItemData(Item)
Msg = Msg & vbCrLf & ctl.ItemData(varItem) & vbCrLf & vbCrLf
Next varItem
MsgBox "You have added: " & vbCrLf & vbCrLf & Msg
Right now it is taking each class to one employee (first employee in the list). Like so:
Jane Doe Class I
Jane Doe Class II
Jane Doe Class III
Just need to know a better way. Thanks in advanced.