Run-time 3061: too few parameters expected 1

[kgross73]

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

[nuclear_nick]

First, and I've given this advice a lot... ... I would do this...

strSQL="<your SQL here>"
Debug.Print strSQL
Stop

You should be able to copy/paste from the immediate window to a new query (SQL view) and if it is correct when you 'run' the query, then it's fine. If there is something wrong with the SQL (which is what I suspect), then you can fix the statement.

Right off the bat, I don't see anything that may prevent Me.Text2 from being blank, which would affect things.

[nuclear_nick]

Oh, and when you are finished testing, don't forget to remove the 'debug' and 'stop' statements. Actually, the debug won't hurt, but you probably don't want your application stopping on people.

[kgross73]

Thank you Nuclear_Nick for your help. I made some changes to the data itself for testing purpose and have realized that I get the error when the job_id field has a letter in it. In some cases the job ID may be begin w/a letter in others it may begin w/ number. Do you know why this would present an issue?

[nuclear_nick]

The field types you are trying to 'input' (Employee_ID, Job_ID), I can assume, are numbers, correct?

Can't put letters in a numerical field. I would suggest keeping the ID 'numbers' just that... numbers.

[OBP]

Nick, if the Job_ID requires letters, then the field should be set to text type String to accomodate it.
I am not sure that it will solve the problem though.

[nuclear_nick]

Again... slightly simpler...

What field type is Job_ID in the database?

If it is a string, then I'm assuming it is failing because in your SQL string is expecting a number, not a string. If Job_ID is a string and not a number, you'll need to surround the Job_ID in the SQL string with quotes.

strSQL = "SELECT Job_ID FROM tblJobs WHERE Job_ID = '" & Job_ID & "'"

Note the placement of the single quotes surrounding Job_ID. Just a sample...