Results 1 to 8 of 8
  1. #1
    database_1 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    34

    Multi value loop for data entry


    I have a form where a user picks an employee in a combobox. The user then has a listbox full of trainings which can be assigned to said employee using a double click method. What I would like to be able to do is pick multiple employees then when I double click on a training, the data for that option is added to all the employees chosen. Is there any possible way to accomplish this? Thanks for your time.

  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
    Is this what you're looking for?

    http://www.baldyweb.com/MultiselectAppend.htm

    If you had two listboxes, you could loop one within the other to get all the combinations.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    database_1 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    34
    I tried to do my code in the style of the sample database but for the other comboboxes, I get a code where an object is reuired? Can you help with that?

  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
    Not without seeing the code, no.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    database_1 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    34
    Here it is

    Private Sub CMD_SAVE_Click()

    Dim strSQL As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim ctl As Control
    Dim varItem As Variant


    Dim lookup As Integer
    Dim emp As Integer
    emp = Me.TXT_EMP.Column(2)
    Dim trng As Integer
    trng = Me.TXT_TR.Column(1)
    lookup = DCount("[TRAINING]", "[EMP_TRNG_TBL]", "[TRNG_ID] =" & trng & " AND [EMP_ID] = " & emp)
    If lookup = 0 Then
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("EMP_TRNG_TBL")
    Set ctl = Me.TXT_TR
    For Each varItem In ctl.ItemsSelected
    rs.AddNew
    rs!TRAINING = ctl.ItemData(varItem)
    rs!TRNG_ID = MeTXT_TR.Column(1)
    rs!EMP_ID = Me.TXT_EMP.Column(2)
    rs.Update
    Next varItem


    Me.TXT_TR.Value = Null
    Else
    MsgBox "Training already submitted"
    End If


    End Sub

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What line throws the error? The syntax to refer to a different column in the loop would be:

    ctl.Column(x, varItem)

    where x is the desired zero-based column, so like:

    rs!TRNG_ID = ctl.Column(1, varItem)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Do you have these two lines at the top of EVERY code module??
    Code:
    Option Compare Database
    Option Explicit

    I reformatted your code - in doing so I found an error
    Code:
    Private Sub CMD_SAVE_Click()
    
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim ctl As Control
        Dim strSQL As String
        Dim varItem As Variant
    
        Dim lookup As Integer
        Dim emp As Integer
        Dim trng As Integer
    
        Set db = CurrentDb()
    
        emp = Me.TXT_EMP.Column(2)
        trng = Me.TXT_TR.Column(1)
        lookup = DCount("[TRAINING]", "[EMP_TRNG_TBL]", "[TRNG_ID] =" & trng & " AND [EMP_ID] = " & emp)
    
        If lookup = 0 Then
            Set rs = db.OpenRecordset("EMP_TRNG_TBL", , AppendOnly)
            Set ctl = Me.TXT_TR
            For Each varItem In ctl.ItemsSelected
                rs.AddNew
                rs!training = ctl.ItemData(varItem)
                rs!TRNG_ID = MeTXT_TR.Column(1)      '<<<<----- missing a period after the ME  !!!!
                rs!EMP_ID = Me.TXT_EMP.Column(2)
                rs.Update
            Next varItem
            Me.TXT_TR = Null
        Else
            MsgBox "Training already submitted"
        End If
    
        'clean up
        rs.Close
        Set rs = Nothing
        Set ctl = Nothing
        Set db = Nothing
    
    End Sub
    Should be
    Code:
                rs!TRNG_ID = Me.TXT_TR.Column(1)

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Correction!!

    This
    Code:
            Set rs = db.OpenRecordset("EMP_TRNG_TBL", , AppendOnly)
    Should be
    Code:
            Set rs = db.OpenRecordset("EMP_TRNG_TBL", , dbAppendOnly)


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

Similar Threads

  1. Replies: 4
    Last Post: 12-21-2015, 10:57 PM
  2. Multi Entry Form
    By Telejazzer in forum Forms
    Replies: 3
    Last Post: 05-30-2015, 07:58 AM
  3. Multi user data entry
    By FJM in forum Access
    Replies: 5
    Last Post: 01-07-2015, 02:27 AM
  4. Replies: 5
    Last Post: 05-13-2014, 10:48 AM
  5. Multi-table Data entry form
    By halpme in forum Forms
    Replies: 3
    Last Post: 11-10-2011, 02:48 PM

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