Error 3061: Too few parameters...

[andrew3254]

Hey, Im trying to execute a simple SQL statement in VB and its not liking
it. It is saying its expectign 7 parameters when there 7 there and the table
names match the table and the data type.

Here is the code
CurrentDb.Execute ("INSERT INTO [Tbl_View Variations]([Project Number],
[Variation Number], [Variation Description], [Variation Cost], [Date
Created], [Start Date], [Month]) VALUES (project, varNumber, description,
poValue, dateCreated, startDate, months);")

The variables project, varNumber etc being variables declared in the
function as the appropriate data type.

I may be missing somethign small but Ive been sitting here getting square
eyes for hours.

 

[Allen Browne]

Your string has no FROM clause, so the named variables don't come from
anywhere. If they are VBA variables, they must be concatenated into the
string, with the correct delimiters for each field type.

Try building the SQL statement slong these lines:

Dim strSql As String
Const strcJetDate = "\#mm\/dd\/yyyy\#"

strSql = "INSERT INTO [Tbl_View Variations]([Project Number], " & _
"[Variation Number], [Variation Description], [Variation Cost], " & _
"[Date Created], [Start Date], [Month]) SELECT " & project & _
" AS ProjectNum, " & varNumber & " AS VariationNum, """ & description & _
""" AS VarDescrip, " & poValue & " AS VarCost, " & _
Format(dateCreated, strcJetDate) & " AS DateCreated, ...

'Debug.Print strSql
CurrentDb.Execute strSql, dbFailOnError

BTW, several of the names used in your example are ambiguous, so Access
could misunderstand them:
- If you put the Month control on a form, Access is likely to get it
confused with the Month() function.
- Project is a (hidden) constant in Access, so always has the value 1.
- Description is a propety that applies to lots of things (tables, fields,
etc.)

For a list of the names to avoid when designing your tables, see:
http://allenbrowne.com/AppIssueBadWord.html

 

[Ofer Cohen]

Executing the SQL that way will be the same as executing a query, and the
query doesn't recognise the variable declared in the function, the variables
need to be taken out of the SQL, and instead the value of the valrable will
be assigned.

Try

CurrentDb.Execute ("INSERT INTO [Tbl_View Variations]([Project Number],
[Variation Number], [Variation Description], [Variation Cost], [Date
Created], [Start Date], [Month]) VALUES (" & project & ", " & varNumber & ",
'" & description & "', " & poValue & ",#" & dateCreated & "#,#" & startDate
& "#," & months & ")")

When addind the a date value field need # before and after the value, as
you can see in the startDate and dateCreated
When addind the text value field need ' before and after the value, as you
can see in the description
Number values need nothing
If I missed a field type in the example, please fix it, or you'll get an
type mismtch error