OpenRecordset failed to open query : run time error 3061

[Guest]

here is my code to open the recordset based on table and query

Dim dbCalc As Databas
Dim rstCalc As Recordse
Dim rstResult As Recordse
Dim strFilter As Strin

Set dbCalc = CurrentD

Set rstResult = dbCalc.OpenRecordset("DVIEW_POTENTIAL") 'it works fin

If strFilter = "" the
Set rstCalc = dbCalc.OpenRecordset("Q_Potential_Well_Calc") 'didn't works
els
Set rstCalc = dbCalc.OpenRecordset("Select * from Q_Potential_Well_Calc " &
" WHERE " & strFilter) 'didn't work
Endi

DVIEW_POTENTIAL is back end table link to current databas
Q_Potential_Well_Calc is query from several linked tabl

Can anybody explain me why it always failed when tried to open recordset based on query with error message
Run-time error '3061'
Too few parameters. Expected 2

Any help would be very appreciate

Gabriel

 

[Alex Dybenko]

if it says:
Too few parameters. Expected 2.
then 2 parameters are missing in query. if you have reference to some form
control in a query - then you have to replace it with value, or supply
parameters. see parameters property of querydef in online help
--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

here is my code to open the recordset based on table and query :

Dim dbCalc As Database
Dim rstCalc As Recordset
Dim rstResult As Recordset
Dim strFilter As String

Set dbCalc = CurrentDb

Set rstResult = dbCalc.OpenRecordset("DVIEW_POTENTIAL") 'it works find

If strFilter = "" then
Set rstCalc = dbCalc.OpenRecordset("Q_Potential_Well_Calc") 'didn't works
else
Set rstCalc = dbCalc.OpenRecordset("Select * from Q_Potential_Well_Calc " & _
" WHERE " & strFilter) 'didn't works
Endif

DVIEW_POTENTIAL is back end table link to current database
Q_Potential_Well_Calc is query from several linked table

Can anybody explain me why it always failed when tried to open recordset

based on query with error message :

 

[Marshall Barton]

here is my code to open the recordset based on table and query :

Dim dbCalc As Database
Dim rstCalc As Recordset
Dim rstResult As Recordset
Dim strFilter As String

Set dbCalc = CurrentDb

Set rstResult = dbCalc.OpenRecordset("DVIEW_POTENTIAL") 'it works find

If strFilter = "" then
Set rstCalc = dbCalc.OpenRecordset("Q_Potential_Well_Calc") 'didn't works
else
Set rstCalc = dbCalc.OpenRecordset("Select * from Q_Potential_Well_Calc " & _
" WHERE " & strFilter) 'didn't works
Endif

DVIEW_POTENTIAL is back end table link to current database
Q_Potential_Well_Calc is query from several linked table

Can anybody explain me why it always failed when tried to open recordset based on query with error message :
Run-time error '3061':
Too few parameters. Expected 2.

When you open a query from the query design window, the
database window, or a form/report record source, use the
OpenQuery (for display) method, it's actually Access that's
managing things for you. One of the things it takes care of
is the resolution of query parameters.

When you open a recordset (in VBA), your code is responsible
for resolving the parameters.

That explains the Why, but I suspect that you really want to
know How to deal with it. If you can find the Help topic
for OpenRecordset, you'll see that there are two forms of
the method. The one that you used based on a database
object and the other based on a QueryDef object:

Dim qdf As QueryDef
Set qdf = dbCalc.QueryDefs("Q_Potential_Well_Calc")
'resolve parameters here
Set rstCalc = qdf.OpenRecordset()

How you resolve the parameters depends on what type of
parameter you're using. If the parameters are references to
controls on a form (recommended), you can do it this way:

Dim prm As Parameter
For Each prm In qdf
prm.Value = Eval(prm.Name)
Next

If the parameters are popup prompts, then you have to get
the values from the user:

Dim prm As Parameter
For Each prm In qdf
prm.Value = InputBox(prm.Name)
Next

 

[tina]

i was trying to work out a solution to Gabriel's issue, using the method you
describe, but when i got to this line in his code

dbCalc.OpenRecordset("Select * from Q_Potential_Well_Calc " & _
" WHERE " & strFilter)

i stopped because i wasn't sure of the syntax to replace
Q_Potential_Well_Calc with rstCalc - or if it's even possible.
not the original post-er, but i am trying to learn more about QueryDef -
hope you don't mind the question.

 

[John Vinson]

Can anybody explain me why it always failed when tried to open recordset based on query with error message :
Run-time error '3061':
Too few parameters. Expected 2.

Please post the SQL view of the query. Access evidently is looking
without success for parameters defined in the query.

 

[Guest]

Thanks

This is what I'm looking for, actually the parameter I set for the query is refer to the controls in a form

Again, thanks for the idea

Gabrie

----- Marshall Barton wrote: ----

Gabriel wrote


Dim prm As Paramete
For Each prm In qd
prm.Value = Eval(prm.Name
Nex