query problems in form code

  • Thread starter Thread starter bob
  • Start date Start date
B

bob

Hi

I am having trouble with a query based recordset which I
am trying to open after a click event on a form in Acess
2000. The query uses info from the form controls. Code
snippet is below:

dim mdb as database
dim qd as querydef
dim rst as recordset
set mdb=currentdb()
set qd=mdb.QueryDefs("qry1")
set rst=qd.openrecordset

qry1 is a stored query in the db, it has no parameters
but uses form controls in criteria fields. The strange
thing is if I have the form open and controls
selected/established, then click the button to run the
code above, after the set rst statement above, the
rst=Nothing, but if I open the query in design view, then
click view (with the form open and controls
selected/established), it shows records ! Don't recall
seeing this before. In the code I can see the form
control values which make up criteria for the query. I
cannot figure out why the query works from design view
but not from code?

Only thing I can say is the query is based on 2 tables
and the tables do not have an inherent relationship
established, but in the query design view grid, 1 is
established between two fields.

I tried to simplify the query criteria w/ no luck. Not
sure I need to though since I can see the intended
records outside of the code.

Help !
Bob
 
The problem, probably, is that you need to evaluate all the query's
parameters before you can open the recordset. I say probably because this
would normally give you the "too few parameters, expected n" error message,
and you don't mention getting this. Do you have any error handling in the
code - an On Error Resume Next perhaps?

I know you say that the query has no parameters, but "form controls in
criteria fields" are parameters. If you open a saved query that uses this
type of parameter then it will automatically resolve all the parameters if
the form is open. This does not happen if you open a recordset based on the
query - you have to supply a value for each parameter.

Try adding the following code to resolve the parameters:

Dim prm as DAO.Parameter
.....

For each prm In qd.Parameters
prm.Value = eval(prm.Name)
Next
 
In addition to Andrew's comments, note that (at least, per what
knowledgeable people have told me) a query based on values in a form is
one of the (few) things that will NOT work in an app distributed with
Access runtime. For these various reasons (and also by background i
guess) i never use this approach -- i build the SQL for the query in the
code and then execute it. This also helps to ensure the integrity of
your program -- no one can accidentally modify or delete the query.
-=-=
 
jfp said:
In addition to Andrew's comments, note that (at least, per what
knowledgeable people have told me) a query based on values in a form is
one of the (few) things that will NOT work in an app distributed with
Access runtime.
Click to expand...

Incorrect. This works perfectly well in the Runtime. Find *more* knowledgeable
people. :-)
 
jfp said:
In addition to Andrew's comments, note that (at least, per what
knowledgeable people have told me) a query based on values in a form is
one of the (few) things that will NOT work in an app distributed with
Access runtime. < snip >
Click to expand...

Not correct information. Such queries will work fine in a runtime
environment. What may have been meant is to try to run a query in code that
uses form values as parameters; you need to resolve those parameters before
you run the query in code. A stored query will run without needing to
resolve the parameters if you use a DoCmd.OpenQuery action.
 
Back
Top