"Too few parameters. Expected 8."

  • Thread starter Thread starter michael c
  • Start date Start date
M

michael c

Hi. I have a make table query that executes when when a
clock on my form hits a certain time like so:

Private Sub Form_Timer()

Me.Clock = Format(Now, "h:nn:ss AM/PM")

If Me.Clock = #6:17:40 PM# Then

DoCmd.DeleteObject acTable, "tblManagementShippedToPlan"
CurrentDb.QueryDefs("qryManagementShippedToPlan").Execute
dbFailOnError

End If
End Sub

An error shows up at the line...

CurrentDb.QueryDefs("qryManagementShippedToPlan").Execute
dbFailOnError

....called Runtime Error 3061 "Too few parameters. Expected
8." I have no idea why. My make table query has 5 columns,
not 8, so I'm not sure what to do. Any suggestions would
be great. Thanks!!
 
The message that you're getting means that the make-table query has 8
parameters in it for which it needs values. You must provide those parameter
values to the query before you run it. Try this:

Private Sub Form_Timer()
Dim qdf As QueryDef
Dim prm As Parameter
Me.Clock = Format(Now, "h:nn:ss AM/PM")

If Me.Clock = #6:17:40 PM# Then

DoCmd.DeleteObject acTable, "tblManagementShippedToPlan"
Set qdf = CurrentDb.QueryDefs("qryManagementShippedToPlan")
For Each prm in qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
qdf.Execute dbFailOnError
Set qdf = Nothing

End If
End Sub
 
I'm getting a "User type not defined" error on Dim qdf as
QueryDef. Thanks for the suggestion btw. Any thoughts on
the error? Thanks!
 
btw, the sources for the make table query are a couple of
joined queries. both of those joined queries have
equations that look something like this:

MTDDollarsShipped: Sum(Abs(([InvoiceDate]>=Forms!Dashboard!
FirstDayOfMonth))*[DollarsShipped])

the make table query that i'm executing in the clock sub
is in the vb code behind the form called dashboard in the
query above. not sure if that is causing the problem...
 
Weird though. It allows me to run the make table query
manually but not from code...i actually didn't really want
a table but i did it to solve a problem i was having
referencing the data in what was a select query, before i
made it into a make table query.

The reason I used the make table was because when I used
the query as a record set in my form's clock event which,
in turn, uses a docmd send event to send the query's
values to someone, i was getting that same parameter
error, so i thought making a table instead would solve the
problem, which it does if i make the table manually.
problem is i need docmd event to take place at 5:30am and
i'm not here to manually run the make table query. i guess
the problem is somewhere in the query...
 
Make sure you have a reference set to the DAO Object Library and use
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
 
It worked! Thanks Duane. That's a huge help. I appreciate
it. I didn't have the DAO referenced. I'm not really sure
how this works, but it does. I have another db with make
table queries that work okay without the DAO and I'm not
sure why, so I guess I have to read up on that. Thanks
again.
 
This probably isn't worth sending, but ....

I wonder if the strange "8 parameters" message
had to do with the 8 possible option constants
that the Execute method uses

** quote from help **
Constant Description
dbDenyWrite Denies write permission to other users
(Microsoft Jet workspaces only).
dbInconsistent (Default) Executes inconsistent updates
(Microsoft Jet workspaces only).
dbConsistent Executes consistent updates
(Microsoft Jet workspaces only).
dbSQLPassThrough Executes an SQL pass-through query.
Setting this option passes the SQL statement
to an ODBC database for processing
(Microsoft Jet workspaces only).
dbFailOnError Rolls back updates if an error occurs
(Microsoft Jet workspaces only).
dbSeeChanges Generates a run-time error if another user is
changing data you are editing
(Microsoft Jet workspaces only).
dbRunAsync Executes the query asynchronously
(ODBCDirect Connection and QueryDef objects only).
dbExecDirect Executes the statement without first calling
SQLPrepare ODBC API function
(ODBCDirect Connection and QueryDef objects only).
** unquote ***

and when DAO library was not referenced,
it choked out the red-herring error message?
 
Back
Top