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