kgross73
08-21-2018, 02:39 PM
Hello,
I am working on an access database that has a form w/a list box called lst_Jobs. The idea is for someone to select a job or jobs from the list box and submit. Upon hitting submit, a new record is created that stores two fields, employee_id and job_id. There is a validation that searches through the selected items and if "Yes" is found in the 4th column of the list box, then a message box pops up, the user answers yes or no and depending on which they choose, their submissions are submitted or cleared out. this part works fine.
However, if the validation search returns NO, then the messagebox is skipped and the info is submitted. This is where the error comes in. If the user selects more than one item, I receive the runtime3061 error. Otherwise I don't get the error and it only happens when the validation returns no.
Below is the code (I am not that strong in VBA so some things maybe unnecessary) with the section in question bold & italiczed. Thanks in advance for any help as I have been struggling with this all day.
Private Sub Command4_Click()
Dim i As Long
Dim lngRow As Long
Dim strMsg As String
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
With Me.lst_Jobs
For lngRow = 0 To .ListCount - 1
If .selected(lngRow) Then
strMsg = strMsg & ", " & .Column(3, lngRow)
End If
Next lngRow
End With
If InStr(strMsg, "Yes") Then
Msg = "One or more of the jobs you selected may require the following disclosure: If you are not presently qualified as a driver " & vbNewLine
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 & " *You must be physically examined and accepted under DOT physical requirements" & vbNewLine
Msg = Msg & " *You must be able to drive a manual transmission (stick-shift)" & vbNewLine
Msg = Msg & " *You must pass a current UPS road test" & vbNewLine
Msg = Msg & " *You must have a good driving record as determined by the company( e.g. No more than 3 moving violations in the last 3 years AND none in the most recent 12 month period)" & vbNewLine
Msg = Msg & " *You must meet the same requirements as outside applicants." & vbNewLine
Msg = Msg & " *Must successfully complete Package Driver Orientation." & vbNewLine
Msg = Msg & "Do you wish to submit your selections?"
Style = vbYesNo + vbCritical + vbDefaultButton1
Title = "Attention!"
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. You are all set! We will let you know if you are selected. Good Luck! The jobs will be filled in seniority order.")
DoCmd.Close acForm, "frm_SelectJobs", acSaveNo
DoCmd.Close acForm, "frm_EmpVerification", acSaveNo
DoCmd.Close acForm, "frm_EmpSearch", acSaveNo
DoCmd.OpenForm "frm_EmpSearch", acNormal
Else
For i = 0 To lst_Jobs.ListCount - 1
lst_Jobs.selected(i) = False
Next
End If
Else
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. You are all set! We will let you know if you are selected. Good Luck! The jobs will be filled in seniority order.")
DoCmd.Close acForm, "frm_SelectJobs", acSaveNo
DoCmd.Close acForm, "frm_EmpVerification", acSaveNo
DoCmd.Close acForm, "frm_EmpSearch", acSaveNo
DoCmd.OpenForm "frm_EmpSearch", acNormal
End If
I am working on an access database that has a form w/a list box called lst_Jobs. The idea is for someone to select a job or jobs from the list box and submit. Upon hitting submit, a new record is created that stores two fields, employee_id and job_id. There is a validation that searches through the selected items and if "Yes" is found in the 4th column of the list box, then a message box pops up, the user answers yes or no and depending on which they choose, their submissions are submitted or cleared out. this part works fine.
However, if the validation search returns NO, then the messagebox is skipped and the info is submitted. This is where the error comes in. If the user selects more than one item, I receive the runtime3061 error. Otherwise I don't get the error and it only happens when the validation returns no.
Below is the code (I am not that strong in VBA so some things maybe unnecessary) with the section in question bold & italiczed. Thanks in advance for any help as I have been struggling with this all day.
Private Sub Command4_Click()
Dim i As Long
Dim lngRow As Long
Dim strMsg As String
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
With Me.lst_Jobs
For lngRow = 0 To .ListCount - 1
If .selected(lngRow) Then
strMsg = strMsg & ", " & .Column(3, lngRow)
End If
Next lngRow
End With
If InStr(strMsg, "Yes") Then
Msg = "One or more of the jobs you selected may require the following disclosure: If you are not presently qualified as a driver " & vbNewLine
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 & " *You must be physically examined and accepted under DOT physical requirements" & vbNewLine
Msg = Msg & " *You must be able to drive a manual transmission (stick-shift)" & vbNewLine
Msg = Msg & " *You must pass a current UPS road test" & vbNewLine
Msg = Msg & " *You must have a good driving record as determined by the company( e.g. No more than 3 moving violations in the last 3 years AND none in the most recent 12 month period)" & vbNewLine
Msg = Msg & " *You must meet the same requirements as outside applicants." & vbNewLine
Msg = Msg & " *Must successfully complete Package Driver Orientation." & vbNewLine
Msg = Msg & "Do you wish to submit your selections?"
Style = vbYesNo + vbCritical + vbDefaultButton1
Title = "Attention!"
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. You are all set! We will let you know if you are selected. Good Luck! The jobs will be filled in seniority order.")
DoCmd.Close acForm, "frm_SelectJobs", acSaveNo
DoCmd.Close acForm, "frm_EmpVerification", acSaveNo
DoCmd.Close acForm, "frm_EmpSearch", acSaveNo
DoCmd.OpenForm "frm_EmpSearch", acNormal
Else
For i = 0 To lst_Jobs.ListCount - 1
lst_Jobs.selected(i) = False
Next
End If
Else
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. You are all set! We will let you know if you are selected. Good Luck! The jobs will be filled in seniority order.")
DoCmd.Close acForm, "frm_SelectJobs", acSaveNo
DoCmd.Close acForm, "frm_EmpVerification", acSaveNo
DoCmd.Close acForm, "frm_EmpSearch", acSaveNo
DoCmd.OpenForm "frm_EmpSearch", acNormal
End If