Results 1 to 3 of 3
  1. #1
    kgross73 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    1

    Looping through selected items in a list box

    Hello,
    I have an access form called frm_Selection and on this form is a listbox called lst_jobs. List jobs has four columns and the forth column is a yes/no field. I am trying to use vba to loop through lst_jobs and if one or more of the items selected has a "Yes" in the fourth column, I would like a message box to pop up. Otherwise, just insert the selected items in tbl_SelectedJobs. I am not strong in vba, and so far come up with this: The part highlighted in red is what I need help with.

    Private Sub Command4_Click()

    Dim frm As Form
    Dim ctl As Control
    Dim varItem As Variant
    Dim strSQL As String
    Dim answer As VbMsgBoxResult
    Dim Verification As VbMsgBoxResult
    Dim Msg, Style, Title
    Msg = Msg & "Do you wish to submit your selections?"
    Style = vbYesNo + vbCritical + vbDefaultButton2
    Title = "Attention!"
    Set frm = Forms!frm_SelectJobs
    Set ctl = Forms!frm_SelectJobs!lst_Jobs

    ***code needed here****
    If column 4 is "Yes" then display the message box below and go through the rest of the process- not sure how to reference the 4th column- its bound to a yes/no field





    Msg = Msg & " *You must be a minimum of 21 years of age" & vbNewLine
    Msg = Msg & " *You must have a current, valid, applicable license" & vbNewLine
    Msg = Msg & " Proceed?" & vbNewLine
    Verification = MsgBox(Msg, Style, Title)
    If Verification = vbYes Then
    For Each varItem In ctl.ItemsSelected
    strSQL = "INSERT INTO tbl_SelectedJobs (Employee_ID, Job_ID) VALUES(" & _
    Me.Text2 & ", " & ctl.ItemData(varItem) & ");"
    CurrentDb.Execute strSQL, dbFailOnError
    Next varItem
    MsgBox ("Thank you for your selection.")
    Else *clear form fields and start over
    End if
    Else
    'No message box, just insert selections into table

    For Each varItem In ctl.ItemsSelected
    strSQL = "INSERT INTO tbl_SelectedJobs (Employee_ID, Job_ID) VALUES(" & _
    Me.Text2 & ", " & ctl.ItemData(varItem) & ");"
    CurrentDb.Execute strSQL, dbFailOnError
    Next varItem
    MsgBox ("Thank you for your selection.")
    End If

    End Sub

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Here's how you refer to the column:

    ctl.Column(x, varItem)

    where x is the zero-based column number (3 for the 4th column).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    apr pillai's Avatar
    apr pillai is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2010
    Location
    Alappuzha, India
    Posts
    209
    Please find Code snippet that loop through selected listbox items and addressing of columns here: https://www.msaccesstips.com/2008/05...r-listbox.html

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

Similar Threads

  1. How to detect items selected in a list box
    By LonghronJ in forum Modules
    Replies: 3
    Last Post: 08-05-2016, 04:48 PM
  2. Replies: 4
    Last Post: 07-27-2016, 12:07 PM
  3. Replies: 3
    Last Post: 10-23-2012, 03:32 PM
  4. List Box Items Are De-Selected On A Screen Refresh
    By plengeb in forum Programming
    Replies: 2
    Last Post: 10-03-2012, 11:58 AM
  5. Replies: 5
    Last Post: 04-04-2011, 09:57 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