Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Posts
    364

    Unanswered: Multiselect Listbox - how to select multiple records and insert into another table?

    Hello,

    I have a listbox on a form set to multiselect, the listbox displays Users from tblUsers.

    I want to select one, two or more rows of users from the listbox and on the click of a command button, have the UserID's inserted into a seperate table.

    I have the following code to add a single User from a listbox, but how can I alter it to allow for multiple users? -
    Code:
    Forms![visits_frm]![userID] = Forms!form1.lstUsers.Column(0)
    How can I do this?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You must use the ItemsSelected collection of the listbox. From Access help:
    Code:
    Sub AllSelectedData()
        Dim frm As Form, ctl As Control
        Dim varItm As Variant, intI As Integer
    
        Set frm = Forms!Contacts
        Set ctl = frm!Names
        For Each varItm In ctl.ItemsSelected
            For intI = 0 To ctl.ColumnCount - 1
                Debug.Print ctl.Column(intI, varItm)
            Next intI
            Debug.Print
        Next varItm
    End Sub
    Have a nice day!

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Here's an example, using a recordset:

    Multi-Select Listbox
    Paul

  4. #4
    Join Date
    Aug 2004
    Posts
    364
    Ok, I think I need to expand my question if I may..

    Basically my form is for booking group visits. One group can make many visits.

    A group visit can also have named Users making the visit (many groups can have many Users), so I have a link table called tblLinkedUsers sitting between tblusers and tblvisits to allow for the many-to-many relationship.

    So on my form I select from two listboxes - a Lstgroup (from tblGroups), and lstUsers (from tblUsers). Then, when the command button is pressed another form called frmVisits opens which displays the selected visit details which are now stored in tblVisits. Each row in tblVisits has its primary key of visitID set to an autonumber.

    I then have a subform within frmVisits called subfrmLinkedUsers which displays the selected User(s) for that visit again linked by the visitID fields

    And it is this User(s) part which I need to be able to work out I need to select at least one or more Users at a time from lstUsers listbox (set to multiselect) on the first form, press the command button to open the second form and have the visit details displayed in a listbox (this part I can do already), and then in the subform (subfrmLinkedUsers)display the User(s) who are part of the group visit.

    It is the last part of adding multiple records into tblLinkedUsers (along with the VisitID) which I need to work out.

    So I am unsure how to adjust the code suggested in this thread to do add the autonumber from the visit aswell as the UserId's.

    Wow its complicated!

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Sorry, I lost track of this thread. In my example, this line:

    rs!OtherValue = Me.txtOtherValue

    would be changed to refer to the VisitID field in the target table and the textbox containing that value on the form.
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •