a query that opens but errors on Set qry=currentDB.Openrecordset()

  • Thread starter Thread starter roger
  • Start date Start date
R

roger

MSA2k7: An existing select query and an existing sub that all work fine

I add one more column to my select query to filter the records further. It
uses a "like" function that pulls a value from an unbound combo box on a form:

Like "*" & [Forms]![frmGroups].[cboFilterCrit] & "*"

The query works. It opens from the db container, and from a docmd.openquery
command:

But:
Now my existing sub crashes when I set an object to the query :

Set objSourceQry = CurrentDb.OpenRecordset(strSourceQry) ' - this line
crashes
with "Too few parameters expected 1"

but if i substitute that with:
DoCmd.OpenQuery strSourceQry ' -- it works

If I try to open the query while the Code is in Break mode, I get:
"This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables"

It I stop the code and open the query, it works.
If I delete the "like" statement, then Set object works.

what gives?

please don't refrain from answering just because you don't "know" the
answer, anyone with any ideas, please chime in.

(i've already repaired the db, next I'm going to reboot the machine, just to
be sure, and then I'm checking for Office Updates)

*A*HA tia
 
Your query sees a name like:
[Forms]![frmGroups].[cboFilterCrit]
as a parameter (i.e. a value that gets supplied when the query runs.)

When you run the query from the query window, Access looks at the parameter
and passes it to something called the Expression Service (ES) to see if it
can make sense of the name. If frmGroups is actually open, the ES
understands the reference, gets the value from the form, and passes it back
to the query. If the form is not open, the ES can't make it work, so Access
pops up a parameter dialog asking you to supply the value.

When you use OpenRecordset() in code, the DAO library does not call the ES
to get the value. Neither does it pop up a dialog asking you for the
parameter value. The value of the parameter must be supplied by the code
before you attempt to OpenRecordset. Failure to do so results in the error
message you experienced.

There are several ways around the issue. One way is to explicitly give the
querydef the value of the parameter, and you can even tell it to read the
form to get the value. Using your example, and assuming the form is open:
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set qdf = db.QueryDefs("Query1")
qdf.Parameters("[Forms]![frmGroups].[cboFilterCrit]") =
Forms!frmGroups.cboFilterCrit
Set rs = qdf.OpenRecordset

In most cases, I find it easier to just build the SQL statement in code
rather than use a saved query for this kind of thing. It makes it easier to
maintain the database (fewer saved queries, code doesn't fail if somone
modifies the query, code can reused in a different database, ...), and it is
much more flexible and efficient to execute (i.e. you can offer the user
lots of critiera boxes to use, but only build the WHERE clause using the
boxes where they actually entered something.)

For action queries, the same thing applies to Exeute instead of RunSQL:
http://allenbrowne.com/ser-60.html

For an example of building a WHERE clause like that, see:
http://allenbrowne.com/ser-62.html

For an example of how to copy the SQL statement from the query into code, so
you can then patch in the WHERE clause, see:
http://allenbrowne.com/ser-71.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

roger said:
MSA2k7: An existing select query and an existing sub that all work fine

I add one more column to my select query to filter the records further. It
uses a "like" function that pulls a value from an unbound combo box on a
form:

Like "*" & [Forms]![frmGroups].[cboFilterCrit] & "*"

The query works. It opens from the db container, and from a
docmd.openquery
command:

But:
Now my existing sub crashes when I set an object to the query :

Set objSourceQry = CurrentDb.OpenRecordset(strSourceQry) ' - this line
crashes
with "Too few parameters expected 1"

but if i substitute that with:
DoCmd.OpenQuery strSourceQry ' -- it works

If I try to open the query while the Code is in Break mode, I get:
"This expression is typed incorrectly, or it is too complex to be
evaluated.
For example, a numeric expression may contain too many complicated
elements.
Try simplifying the expression by assigning parts of the expression to
variables"

It I stop the code and open the query, it works.
If I delete the "like" statement, then Set object works.

what gives?

please don't refrain from answering just because you don't "know" the
answer, anyone with any ideas, please chime in.

(i've already repaired the db, next I'm going to reboot the machine, just
to
be sure, and then I'm checking for Office Updates)

*A*HA tia
Click to expand...
 
1. I switched from DAO to ADO years ago, so that code doesn't really apply
anymore.

2. end users don't get into modify my queries EVER. (and how do YOU modify a
query when they're saved as a bunch of SQL statements buried in all your
modules?)

3. That stuff about ES was interesting though. (and seems to be true of both
DAO and ADO) good to know just because a query works, that doesn't mean the
query WORKS (leave it to M$ to NOT evaluate an expression the same way twice,
and then to provide really lousy error msgs about it!)

so a much shorter and simpler solution was to replace the Forms! expression
in my query with a call to a 1-line function:

Function funcGroupFilterCrit()
funcGroupFilterCrit = [Forms]![frmPzdbGroups].[cboFilterCrit]
End Function

Thanks for your help, my filter works now :>

Allen Browne said:
Your query sees a name like:
[Forms]![frmGroups].[cboFilterCrit]
as a parameter (i.e. a value that gets supplied when the query runs.)

When you run the query from the query window, Access looks at the parameter
and passes it to something called the Expression Service (ES) to see if it
can make sense of the name. If frmGroups is actually open, the ES
understands the reference, gets the value from the form, and passes it back
to the query. If the form is not open, the ES can't make it work, so Access
pops up a parameter dialog asking you to supply the value.

When you use OpenRecordset() in code, the DAO library does not call the ES
to get the value. Neither does it pop up a dialog asking you for the
parameter value. The value of the parameter must be supplied by the code
before you attempt to OpenRecordset. Failure to do so results in the error
message you experienced.

There are several ways around the issue. One way is to explicitly give the
querydef the value of the parameter, and you can even tell it to read the
form to get the value. Using your example, and assuming the form is open:
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set qdf = db.QueryDefs("Query1")
qdf.Parameters("[Forms]![frmGroups].[cboFilterCrit]") =
Forms!frmGroups.cboFilterCrit
Set rs = qdf.OpenRecordset

In most cases, I find it easier to just build the SQL statement in code
rather than use a saved query for this kind of thing. It makes it easier to
maintain the database (fewer saved queries, code doesn't fail if somone
modifies the query, code can reused in a different database, ...), and it is
much more flexible and efficient to execute (i.e. you can offer the user
lots of critiera boxes to use, but only build the WHERE clause using the
boxes where they actually entered something.)

For action queries, the same thing applies to Exeute instead of RunSQL:
http://allenbrowne.com/ser-60.html

For an example of building a WHERE clause like that, see:
http://allenbrowne.com/ser-62.html

For an example of how to copy the SQL statement from the query into code, so
you can then patch in the WHERE clause, see:
http://allenbrowne.com/ser-71.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

roger said:
MSA2k7: An existing select query and an existing sub that all work fine

I add one more column to my select query to filter the records further. It
uses a "like" function that pulls a value from an unbound combo box on a
form:

Like "*" & [Forms]![frmGroups].[cboFilterCrit] & "*"

The query works. It opens from the db container, and from a
docmd.openquery
command:

But:
Now my existing sub crashes when I set an object to the query :

Set objSourceQry = CurrentDb.OpenRecordset(strSourceQry) ' - this line
crashes
with "Too few parameters expected 1"

but if i substitute that with:
DoCmd.OpenQuery strSourceQry ' -- it works

If I try to open the query while the Code is in Break mode, I get:
"This expression is typed incorrectly, or it is too complex to be
evaluated.
For example, a numeric expression may contain too many complicated
elements.
Try simplifying the expression by assigning parts of the expression to
variables"

It I stop the code and open the query, it works.
If I delete the "like" statement, then Set object works.

what gives?

please don't refrain from answering just because you don't "know" the
answer, anyone with any ideas, please chime in.

(i've already repaired the db, next I'm going to reboot the machine, just
to
be sure, and then I'm checking for Office Updates)

*A*HA tia
Click to expand...
Click to expand...
 
Back
Top