Too Few Parameters when opening query

[Guest]

Hi, everyone!

Have a query, "BOMWeights", that resides in the front-end database. It
brings together into one record summary data produced by 3 other queries that
extract data from a table in the back-end database. When I double-click on
the query, or use a OpenQuery command in VBA, it displays fine.

When I try to open it using the OpenRecordset command, however, it generates
a run-time error 3061: "Too few parameters. Expected 1." error. Here is the
code:
....
Dim rst1 As Recordset
Set rst1 = CurrentDb.OpenRecordset("BOMWeights")
With rst1
Me![PanelWeight] = Nz(![PanelWeight], 0)
Me![FrameWeight] = Nz(![FrameWeight], 0)
Me![HardwareWeight] = Nz(![HardwareWeight], 0)
End With
rst1.Close
Set rst1 = Nothing
....

What am I doing wrong?

Thanks,
Bruce

 

[Dirk Goldgar]

Hi, everyone!

Have a query, "BOMWeights", that resides in the front-end database.
It brings together into one record summary data produced by 3 other
queries that extract data from a table in the back-end database.
When I double-click on the query, or use a OpenQuery command in VBA,
it displays fine.

When I try to open it using the OpenRecordset command, however, it
generates a run-time error 3061: "Too few parameters. Expected 1."
error. Here is the code:
...
Dim rst1 As Recordset
Set rst1 = CurrentDb.OpenRecordset("BOMWeights")
With rst1
Me![PanelWeight] = Nz(![PanelWeight], 0)
Me![FrameWeight] = Nz(![FrameWeight], 0)
Me![HardwareWeight] = Nz(![HardwareWeight], 0)
End With
rst1.Close
Set rst1 = Nothing
...

What am I doing wrong?

Does your query by any chance refer to controls on forms as criteria?
These constitute parameters to the query. When Access itself runs a
query, it automatically fills in the values for those parameters, but
when you run the query yourself via DAO, Access doesn't do that for you.

If this is the problem, you can open the recordset from the querydef
(stored definition of the query) and fill in the parameters yourself,
either by name or by getting Access to fill them in for you, like this:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rst1 As Recordset

Set db = CurrentDb
Set qdf = db.QueryDefs("BOMWeights")

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rst1 = qdf.OpenRecordset
With rst1
Me![PanelWeight] = Nz(![PanelWeight], 0)
Me![FrameWeight] = Nz(![FrameWeight], 0)
Me![HardwareWeight] = Nz(![HardwareWeight], 0)
End With
rst1.Close
Set rst1 = Nothing

Set qdf = Nothing
Set db = Nothing

 

[Guest]

Dirk,

That was it! Query was using form-based value, and code you provided worked
great!

Thanks,
Bruce

Hi, everyone!

Have a query, "BOMWeights", that resides in the front-end database.
It brings together into one record summary data produced by 3 other
queries that extract data from a table in the back-end database.
When I double-click on the query, or use a OpenQuery command in VBA,
it displays fine.

When I try to open it using the OpenRecordset command, however, it
generates a run-time error 3061: "Too few parameters. Expected 1."
error. Here is the code:
...
Dim rst1 As Recordset
Set rst1 = CurrentDb.OpenRecordset("BOMWeights")
With rst1
Me![PanelWeight] = Nz(![PanelWeight], 0)
Me![FrameWeight] = Nz(![FrameWeight], 0)
Me![HardwareWeight] = Nz(![HardwareWeight], 0)
End With
rst1.Close
Set rst1 = Nothing
...

What am I doing wrong?

Does your query by any chance refer to controls on forms as criteria?
These constitute parameters to the query. When Access itself runs a
query, it automatically fills in the values for those parameters, but
when you run the query yourself via DAO, Access doesn't do that for you.

If this is the problem, you can open the recordset from the querydef
(stored definition of the query) and fill in the parameters yourself,
either by name or by getting Access to fill them in for you, like this:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rst1 As Recordset

Set db = CurrentDb
Set qdf = db.QueryDefs("BOMWeights")

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rst1 = qdf.OpenRecordset
With rst1
Me![PanelWeight] = Nz(![PanelWeight], 0)
Me![FrameWeight] = Nz(![FrameWeight], 0)
Me![HardwareWeight] = Nz(![HardwareWeight], 0)
End With
rst1.Close
Set rst1 = Nothing

Set qdf = Nothing
Set db = Nothing


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)