Query not returning recordset when code running

[Riverboat Gambler]

I have a very strange problem which is confusing me.

For several months I have provided a reporting facility via my system
which uses a query (which is called in a report's Open event) to
provide the details of a number of pupils. This query uses two
parameters to filter the record source and the value of the parameters
are picked up from the vlaues held in two comboboxes on an open form.
This has worked very well.

Recently, I have tried to build an additional function into the
procedure that would allow the user to save to disk copies of the same
report as snapshot files - one for each pupil. In order to do this, I
decided that to it would be best to use the same underlying query and
to get a list of the PupilIDs from it, and then itterate through each
record passing the PupilID as a further parameter for the report
generation.

Unfortunately, each time I try to get hold of the list of PupilID's,
my procedure fails indicating that it cannot find the query - however,
if I run the query manually it does return a recordset. However, if I
halt my code half way through and then try to the query, it returns no
results.

In order to make sure that I have not misspelt anything, I have
created a copy of the query called Query1 and set the SQL string in
code to return all fields (rather than just a limited selection):

strSQLPupils = "SELECT * FROM Query1"
Set rstPupils = CurrentDb.OpenRecordset(strSQLPupils)

I have confirmed that Query1 does return the right recordset before
running the code, but when I run the code it falls over at the Set
statement and returns an Error 0 which in my book suggests that Access
cannot find the query.

I have put watches on my code and the comboboxes which provide the
values for the parameters for the query are definitely showing the
correct values.

I have been using parameterised queries for a long time and never come
across a problem like this before. Has anybody got any ideas on where
I might be going wrong?

This is an Access 2003 format DB (split FE / BE) which I am modifying
using Access 2007 running on Vista x64.

Regards,

Huw.

[hbinc]

Hi Riverboat Gambler,

Did you try: Set rstPupils = CurrentDb.OpenRecordset("Query1")?

HBInc.

[Ken Snell [MVP]]

Probably because your recordset goes out of scope right after you open it
because you're using CurrentDb as the reference, which is not a persistent
object/reference.

Instead of this line:
Set rstPupils = CurrentDb.OpenRecordset(strSQLPupils)

Use these lines:
Dim dbs As DAO.Database
Set dbs = CurrentDb
Set rstPupils = dbs.OpenRecordset(strSQLPupils)

Then, later in your code, set dbs = Nothing after you've closed the
recordset and set the recordset to Nothing.

--

Ken Snell
http://www.accessmvp.com/KDSnell/

"hbinc" <j.van...@hccnet.nl> wrote in message
news:91730157-de76-4f08...@b15g2000yqd.googlegroups.com...

[hbinc]

On Nov 21, 3:08 pm, "Ken Snell [MVP]"

<kthsneisll...@ncoomcastt.renaetl> wrote:
> Probably because your recordset goes out of scope right after you open it
> because you're using CurrentDb as the reference, which is not a persistent
> object/reference.
>
> Instead of this line:
>     Set rstPupils = CurrentDb.OpenRecordset(strSQLPupils)
>
> Use these lines:
>     Dim dbs As DAO.Database
>     Set dbs = CurrentDb
>     Set rstPupils = dbs.OpenRecordset(strSQLPupils)
>

Hi Ken,

There is nothing wrong with using the statement
Set rstPupils = CurrentDb.OpenRecordset(strSQLPupils)
and the use of CurrentDb.

At least when I use it, and I use it "a million times" in my
databases, without any problems.

HBInc.

[Salad]

That would be a good test. The OP says "returns an Error 0" which would
indicate that there is not error. Another check might be to check the
recordcount. If the OP did something like
msgbox rstPupils.recordcount
right after the Set statement it should return either 0 or 1. If 1
there's at least 1 record if not more. If entering
rstPupils.MoveLast
rstPupils.MoveFirst 'be sure to to to first rec to process
msgbox rstPupils.recordcount
it should return the accurate count of records.

[Riverboat Gambler]

> it should return the accurate count of records.- Hide quoted text -
>
> - Show quoted text -

Dear Ken, HBInc & Salad,

Many thanks for all of you getting back to me so quickly - especially
at the weekend.

I have tried both Ken's and HBInc's suggestions, but the code still
fails with an Error 0 as soon as I try to do the Set. This also means
that I can't test for a recordcount as the recordset never actually
gets set and so shows as "Nothing". I must admit I have a
philosophical problem with Microsoft's assertion at an Error 0 is not
an Error - my take has always been that if it isn't an error, then
don't give it the label - talk about giving a dog a bad name :)!. Over
the years, I've come to rationalise it in my mind that Error 0 roughly
equates to "something's not there that we're expecting to be there"
which is one of the reasons I'm finding this behaviour very confusing
as the "Query1" definitely is there and I can run it any time I like -
just not when any code is running.

I'm going to try taking the criteria out of the query and running it
again - if it works, it'll give me far too many records, but it'll
maybe give me some further info. I'll let you know how I get on.

Regards,

Huw.

[Riverboat Gambler]

> Huw.- Hide quoted text -

>
> - Show quoted text -

Hello all,

I have now got some further information, but I'm not sure it helps...

I removed the criteria references to the comboxes from the query and
hardcoded some values in there and ran my code. Sure enough the code
ran through clean as a whistle and the Set statement executed just
fine.

Therefore, I decided to eliminate the comboboxes from the equation so
I modded my code so that before the Set statement is called I pick up
the values from the comboxes and put them in two plain textboxes on my
form - (this also enabled me to confirm that the values being picked
up from the comboboxes were valid values).

I then removed the hardcoded values in the query and instead inserted
references to the two textbox controls and ran the query (from the
Navigation Pane) - the values were picked up from the texboxes on the
form without a hitch and the query returned a recordset with the
correct number of records.

Finally, I ran my code from the top and it failed at the same point
with an Error 0. Again halting the code just before the failure point,
and running the query from the Navigation Pane gave me no records.
Stopping the code and running it again gave me all records.

So the query appears to be a valid query and the criteria parameters
are working fine when I'm not running code.

As a final test, I put a breakpoint in the code for an entirely
different sub on the form (a command button which switches the
visibility / invisibility of a series of listboxes) but I put it right
at the top before the code does anything. Before pressing the command
button, I ran Query1 and got a proper recordset. So I closed Query1,
clicked the command button and let it pause at the breakpoint and then
tried to open Query1 again - no records!

I am now getting very confused - it appears that the very act of
running any VBA code is causing this query to fail.

Regards,

Huw.

[Salad]

Riverboat Gambler wrote:

Before setting the recordset, what do you get with
MsgBox CurrentDb.QueryDefs("Query1").SQL
?

[Salad]

Salad wrote:

Also, you could enter
debug.print CurrentDb.QueryDefs("Query1").SQL
and then copy the code to a new query window's SQL and attempt to run.
Maybe there's a runon like "From Table1WHere" or something not quite right.

[Ken Snell [MVP]]

See
http://www.utteraccess.com/forums/showflat.php?Cat=&Board=84&Number=1720818

Good discussion with links about use of CurrentDb.
--

Ken Snell
http://www.accessmvp.com/KDSnell/

"hbinc" <j.van...@hccnet.nl> wrote in message

news:1887e985-c277-4935...@p8g2000yqb.googlegroups.com...

[Riverboat Gambler]

> ?- Hide quoted text -

>
> - Show quoted text -

Hello Salad,

The query is quite complex so I've done the following to dump the
results into a textbox on the form:

strSQLPupils = "SELECT * FROM Query1"

Me.txtSQL = dbs.QueryDefs("Query1").SQL
Set rstPupils = dbs.OpenRecordset("Query1")

I did this so that could compare the exact results of the QueryDef
call to what actually shows when I also open the SQL view of the query
from the navigation Pane - both give exactly the same - 209 words and
5,248 characters.

Am I going mad I ask myself?

Huw.

[Riverboat Gambler]

> Huw.- Hide quoted text -

>
> - Show quoted text -

Hello All,

Of course, the assignment of the SQL string to the strSQLPupils in my
previous post is superfluous - sorry for leaving it in.

I suppose the thing that I'm finding particularly confusing is the
fact that even when running another sub-routine, the query doesn't
work - it only seems to give any results when no code is running. Are
there any strange parameters that have been introduced in 2007 that
determine whether a query should not run when code is running??

As you can see - I'm trying to clutch at any straw going now :)

Regards,

Huw.

[Roger]

so if you do this
on error resume next

strSQLPupils = "SELECT * FROM Query1"

Set rstPupils = dbs.OpenRecordset("Query1")
debug.print err.number, err.description
stop

what is the error number / description ?

[David W. Fenton]

Riverboat Gambler <aber...@btinternet.com> wrote in
news:736263ac-ae0d-4ad5...@m26g2000yqb.googlegroups.co
m:

> I removed the criteria references to the comboxes from the query
> and hardcoded some values in there and ran my code. Sure enough
> the code ran through clean as a whistle and the Set statement
> executed just fine.

This is to be expected. The Access expression service is not
available inside DAO recordsets, so references to form controls will
always cause the recordset to fail to open. This has always been the
case.

Your solution is the correct one, i.e., resolve the reference in
on-the-fly SQL that you use to open your recordset.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Riverboat Gambler]

On 21 Nov, 22:35, "David W. Fenton" <XXXuse...@dfenton.com.invalid>
wrote:
> Riverboat Gambler <aberc...@btinternet.com> wrote innews:736263ac-ae0d-4ad5...@m26g2000yqb.googlegroups.co

David,

Many thanks for your reply. When I first read it I thought "no that
cannot be - I've been using parameterised queries for ages in code!".
But when I sat back and thought it through, although I use
parameterised queries for forms and reports, I couldn't think of
anytime I've used them to populate a recordset that I've called from
code - I have always done that bit by providing the parameter(s) on-
the-fly with a customised SQL string. I suppose what led me astray was
that I already had the query defined (that is used as a source for a
report) that I knew had the right records and so went blindly on
trying to use that - doh!!!!

So I removed all the parameters from the criteria rows for the query
and then provided the SQL string with the parameters built in (from
the values of the comboboxes) and everything is fine.

I can only apologise to everyone who got involved in this for starting
a ball rolling that didn't need to be rolled - many thanks to you all
from this very humble numpty.

Regards,

Huw Davies.