Too few parameteres, expected 6 (1 Viewer)

[spikepl]

            strSQL = "UPDATE tblEmployeeJobs SET EmpID=@EmpID, JobID=@JobID, JobStart=@JobStart, JobEnd=@JobEnd  WHERE EmbJobID=@EmpJobID"

            Set qdf = CurrentDb.CreateQueryDef("", strSQL)
            With qdf
                .Parameters("@EmpID") = Me.txtEmpID
                .Parameters("@JobID") = Me.cboJobID
                .Parameters("@JobStart") = Me.txtJobStart
                .Parameters("@JobEnd") = Me.txtJobEnd
                .Parameters("@EmpJobID") = Me.txtEmpJobID
                .Execute
            End With

I have no clue why the thing wants 6 parameters - any ideas?

The table is

tblEmployeeJobs
-------------------
EmpJobID - autonumber
EmpID - FK
JobID - FK
JobStart - Date
JobEnd - Date


and the data are:

?Me.txtEMpID, Me.cboJobID, Me.txtJobStart, Me.txtJobEnd, Me.txtEmpJobID
 10, 31, 22/09/2015, 05/10/2015, 60

 

[pbaldy]

Not sure, but is there a reason for using a QueryDef? Why not just build the SQL and execute it?

strSQL = "UPDATE tblEmployeeJobs SET EmpID=" & Me.txtEmpID & ", JobID="...
CurrentDb.Execute strSQL

 

[spikepl]

Because I am trying to keep consistency throughout my db, because I like parametres in that they do not require messing around with date formats or ' or " in names, and now 'coz I'm utterly mystified by why it wants 6

I have another db where I use querydefs and parameters throughout for saved queries (that was written in A2007 at a time when Pat Hartmann opined that saved queries bloat less), and that works just fine.

 

[pbaldy]

Does this method work elsewhere? My guess about 6 rather than 5 is that a table or field name is spelled wrong.

 

[spikepl]

strSQL = "UPDATE tblEmployeeJobs SET EmpID=@EmpID WHERE EmbJobID=@EmpJobID"

Set qdf = CurrentDb.CreateQueryDef("", strSQL)
With qdf
.Parameters("@EmpID") = Me.txtEmpID
' .Parameters("@JobID") = Me.cboJobID
' .Parameters("@JobStart") = Me.txtJobStart
' .Parameters("@JobEnd") = Me.txtJobEnd
.Parameters("@EmpJobID") = Me.txtEmpJobID
.Execute
End With

When I do the above then it wants 3 parameters!

 

[Minty]

Try getting rid of the @ symbols...
http://www.access-programmers.co.uk/forums/showthread.php?t=167177

 

[spikepl]

Tried - replaced @ by P - no difference. The monster wants one more parameter! (I have @ all over in another db and it works fine there)

 

[spikepl]

Aaaaaaaaaaaaaaaaaargh - could you hear the scream? A bloody error 40 again! Of course!

EmbJobID=@EmpJobID

should be

EmpJobID=@EmpJobID"

 

[Minty]

Are you sure you don't need to declare the parameters first in the Insert statement?

Ignore this I just heard a scream...

 

[pbaldy]

Do I win?

My guess... is that a table or field name is spelled wrong.

 

[spikepl]

You do - you've already got a Thanks thumb-up - anything else?