Results 1 to 4 of 4
  1. #1
    wwhit is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    18

    Multiselect Listboxs

    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.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Sure; you nest one loop inside another. Here's an example:

    Code:
      For Each varItem In ctl.ItemsSelected
        For Each varItem2 In ctl2.ItemsSelected
          strSQL = "INSERT INTO tblPartApply(PartNumber, model, year) " _
                   & "VALUES('" & Me.txtPartNum & "', '" & ctl.ItemData(varItem) _
                   & "', " & ctl2.ItemData(varItem2) & ")"
          db.Execute strSQL
        Next varItem2
      Next varItem
    Your recordset code would also work fine (is probably better in fact).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    wwhit is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    18
    It worked. Thanks. Here is the code I used.

    Msg = ""
    Set ctl = [Forms]![Class Requirement Form]![List63]
    Set ctl2 = [Forms]![Class Requirement Form]![List65]
    For Each varItem In ctl.ItemsSelected
    For Each varItem2 In ctl2.ItemsSelected
    rs.AddNew
    rs![CLASS NAME2] = ctl.ItemData(varItem)
    rs![EMPLOYEE] = ctl2.ItemData(varItem2)
    rs.Update
    Msg = Msg & ctl2.ItemData(varItem2)
    Msg = Msg & vbCrLf & ctl.ItemData(varItem) & vbCrLf & vbCrLf
    Next varItem2
    Next varItem
    MsgBox "You have added: " & vbCrLf & vbCrLf & Msg

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Multiselect Listbox
    By wwhit in forum Forms
    Replies: 19
    Last Post: 03-09-2015, 02:58 PM
  2. Listbox multiselect status
    By Sam23 in forum Programming
    Replies: 5
    Last Post: 03-06-2012, 01:13 PM
  3. Append Records using MultiSelect Listbox
    By Ted C in forum Programming
    Replies: 14
    Last Post: 03-15-2011, 01:25 PM
  4. HELP! using list boxes with multiselect
    By tarhim47 in forum Forms
    Replies: 21
    Last Post: 11-08-2010, 02:51 PM
  5. Replies: 3
    Last Post: 08-11-2009, 10:40 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums