Report Query Works 2nd Time, But Not 1st

[Neil]

I have a very strange situation - stranger than most things I've ever
come across.

I have an old MDB file that is being run in 2010 and there's a report
which uses about 5 queries to write to temporary tables, which the
report is then based on. One of the queries, for some reason, doesn't
write its data the first time the report is run. But then if you run the
report a second time, it works fine!

The temporary tables are cleared at the beginning of each run, so it's
not like it's using data from the previous run. It's not. Also, I've
stopped the process right before this query is run, and then manually
run the query. It works fine if I stop the process and then manually run
it, even on the first run.

But, for some reason, when you run the report the first time this query
doesn't write its data to its corresponding temporary table. If you then
run the report again a second time, it works fine.

All data that the query uses is from the main database tables, and there
are no data changes between the first and second iteration.

Any ideas as to what might be going on?

Thanks!

Neil

 

[Phil]

I have a very strange situation - stranger than most things I've ever
come across.

I have an old MDB file that is being run in 2010 and there's a report
which uses about 5 queries to write to temporary tables, which the
report is then based on. One of the queries, for some reason, doesn't
write its data the first time the report is run. But then if you run the
report a second time, it works fine!

The temporary tables are cleared at the beginning of each run, so it's
not like it's using data from the previous run. It's not. Also, I've
stopped the process right before this query is run, and then manually
run the query. It works fine if I stop the process and then manually run
it, even on the first run.

But, for some reason, when you run the report the first time this query
doesn't write its data to its corresponding temporary table. If you then
run the report again a second time, it works fine.

All data that the query uses is from the main database tables, and there
are no data changes between the first and second iteration.

Any ideas as to what might be going on?

Thanks!

Neil

I'm guessing

When you open the report the first time, there is no data in the recordsource
(Temp table), as you say you clear the data at the begining of each run (it's
is unclear what is meant by each run) The data in the Temp Table gets created
AFTER the report is opened. On the second opening, the Temp table has been
populated, so the report can open OK.

Phil

 

[Neil]

I'm guessing

When you open the report the first time, there is no data in the recordsource
(Temp table), as you say you clear the data at the begining of each run (it's
is unclear what is meant by each run) The data in the Temp Table gets created
AFTER the report is opened. On the second opening, the Temp table has been
populated, so the report can open OK.

Phil

What I mean by "each run" is each time the report is run. The data in
the temp tables are cleared out each time the report is run, at the
beginning of the process. So, no, there is no data carried over from the
previous run.

Furthermore, again, as I noted, if, on the first run (when it doesn't
work) I stop the process right before the query is to be executed, and
then MANUALLY run the query, it works fine. But, for some reason, it
doesn't populate the table when it's run automatically as part of the
first run. But the correct data is there, and the query works fine when
run manually in the middle of the first run.

Neil

 

[Phil]

What I mean by "each run" is each time the report is run. The data in
the temp tables are cleared out each time the report is run, at the
beginning of the process. So, no, there is no data carried over from the
previous run.

Furthermore, again, as I noted, if, on the first run (when it doesn't
work) I stop the process right before the query is to be executed, and
then MANUALLY run the query, it works fine. But, for some reason, it
doesn't populate the table when it's run automatically as part of the
first run. But the correct data is there, and the query works fine when
run manually in the middle of the first run.

Neil

Can we see your code please.

PS all replies should be addressed through the newsgroup. Please do not send
direct emails, unless specifically invited to do so.

Phil

 

[Neil]

Can we see your code please.

PS all replies should be addressed through the newsgroup. Please do not send
direct emails, unless specifically invited to do so.

Phil

Sorry - I'm using Thunderbird as my newsreader, as well as my e-mail
client, and, for some strange reason, it insists on sending a copy of
the newsgroup reply to the person's e-mail address when you click Reply
All (which I do automatically by default whenever I reply to anything).
So it wasn't intentional. Outlook Express never did that, and I used
that for years. If anyone knows how to turn off this feature in
Thunderbird, that would be great.

As for the code, there's no mystery there. It's just

CurrentDb.Execute "qapp_Member_Aversion_List_1", dbFailOnError

As I said, everything up to that point runs normally, and all the data's
in place. Then that line executes the query, but the query fails the
first time, but then works fine the second time.

The SQL for the query is:

INSERT INTO T_Member_Aversion_List ( Member_ID, Food_List_ID )
SELECT M_Member_Food_Aversions.Member_ID,
M_Member_Food_Aversions.Food_List_ID
FROM ((T_Labels INNER JOIN M_Member_Food_Aversions ON T_Labels.Member_ID
= M_Member_Food_Aversions.Member_ID) INNER JOIN M_Member_Meals ON
T_Labels.Member_ID = M_Member_Meals.Member_ID) INNER JOIN (SELECT
M_Member_meals.Member_ID, Count(M_Member_meals.Member_ID) AS MemberCount
FROM M_Member_meals WHERE
(((M_Member_meals.meal_id)=get_global('gbl_meal_id'))) GROUP BY
M_Member_meals.Member_ID) AS MemberCounts ON T_Labels.Member_ID =
MemberCounts.Member_ID
WHERE (((T_Labels.cart_plan)=False) AND
((M_Member_Food_Aversions.Lent_Only)=False) AND
((MemberCounts.MemberCount)>0));

Oh, and in case there was something with the subquery needing to be
compiled or something, I tried it with a stored query instead of the
ad-hoc MemberCounts subquery, but it didn't make any difference.

Basically I've tried changing the query every way I can think of, but to
no effect.

The M_ tables in the query are main tables, and their data is set when
the report is run.

The T_ table is a temporary table, whose data is populated in an earlier
stage of the Report Open code (which populates the tables). That would
seem to be the culprit, that perhaps that table's not populated on the
first pass. But it is. I put a break in the code at the point where the
query executes, and stopped it on the first pass, and the T_Labels table
is fully populated.

I even (as noted before) manually ran the query after stopping the code
at the query on the first pass, and it worked fine. But when run through
code on the first pass, it doesn't work fine.

 

[Neil]

Can we see your code please.

PS all replies should be addressed through the newsgroup. Please do not send
direct emails, unless specifically invited to do so.

Phil

Here's something else I just tried, for troubleshooting purposes. I
replaced the currentdb.execute command with docmd.openquery - because
that one produces a prompt.

And, sure enough, the first time the report was run, the openquery
command produce a prompt confirming that I wanted to run the query. It
then produced another prompt saying "You are about to append 0 records."

I then closed the report and immediately re-ran the report. The second
time I ran the report, the prompt said, "You are about to append 550
records."

So the query is definitely being executed. But it's appending 0 records
the first time.

I even put a debug.print dcount(... in the line right before the query
was executed, to confirm that T_Labels was populated before the query
was run. The dcount results were the same the first time the report was
run and the second time the report was run. So it's not a query of
T_Labels not being populated.

Also, another strange thing: when I run the report, as noted, it works
the second time, but not the first time. However, when the client runs
the report, it doesn't work until the THIRD time! That is, they have to
run it three times, and the first two times it produces no results.

 

[Phil]

Sorry - I'm using Thunderbird as my newsreader, as well as my e-mail
client, and, for some strange reason, it insists on sending a copy of
the newsgroup reply to the person's e-mail address when you click Reply
All (which I do automatically by default whenever I reply to anything).
So it wasn't intentional. Outlook Express never did that, and I used
that for years. If anyone knows how to turn off this feature in
Thunderbird, that would be great.

As for the code, there's no mystery there. It's just

CurrentDb.Execute "qapp_Member_Aversion_List_1", dbFailOnError

As I said, everything up to that point runs normally, and all the data's
in place. Then that line executes the query, but the query fails the
first time, but then works fine the second time.

The SQL for the query is:

INSERT INTO T_Member_Aversion_List ( Member_ID, Food_List_ID )
SELECT M_Member_Food_Aversions.Member_ID,
M_Member_Food_Aversions.Food_List_ID
FROM ((T_Labels INNER JOIN M_Member_Food_Aversions ON T_Labels.Member_ID
= M_Member_Food_Aversions.Member_ID) INNER JOIN M_Member_Meals ON
T_Labels.Member_ID = M_Member_Meals.Member_ID) INNER JOIN (SELECT
M_Member_meals.Member_ID, Count(M_Member_meals.Member_ID) AS MemberCount
FROM M_Member_meals WHERE
(((M_Member_meals.meal_id)=get_global('gbl_meal_id'))) GROUP BY
M_Member_meals.Member_ID) AS MemberCounts ON T_Labels.Member_ID =
MemberCounts.Member_ID
WHERE (((T_Labels.cart_plan)=False) AND
((M_Member_Food_Aversions.Lent_Only)=False) AND
((MemberCounts.MemberCount)>0));

Oh, and in case there was something with the subquery needing to be
compiled or something, I tried it with a stored query instead of the
ad-hoc MemberCounts subquery, but it didn't make any difference.

Basically I've tried changing the query every way I can think of, but to
no effect.

The M_ tables in the query are main tables, and their data is set when
the report is run.

The T_ table is a temporary table, whose data is populated in an earlier
stage of the Report Open code (which populates the tables). That would
seem to be the culprit, that perhaps that table's not populated on the
first pass. But it is. I put a break in the code at the point where the
query executes, and stopped it on the first pass, and the T_Labels table
is fully populated.

I even (as noted before) manually ran the query after stopping the code
at the query on the first pass, and it worked fine. But when run through
code on the first pass, it doesn't work fine.

Don't know anything about Thunderbird. I use a freebie called Newsman, but I
used to like Outlook Express.

Am I right in assumimg that on the OnOpen Statement you
1) Create the T_Labels table
2) Run CurrentDb.Execute "qapp_Member_Aversion_List_1", dbFailOnError to
populate the T_Member_Aversion_List Table 3) Try to use the data in
T_Member_Aversion_List Table as the record source for the report.

If so, and these are blind suggestions
On n the onload event if the report, reset the recordsource to
T_Member_Aversion_List. Or, and this should work. Use a command button on a
form to create the T_Member_Aversion_List and then so basically run the 3
steps above and 4) DoCmd.OpenReport "MyReport"

Phil

 

[John W. Vinson]

I have a very strange situation - stranger than most things I've ever
come across.

I have an old MDB file that is being run in 2010 and there's a report
which uses about 5 queries to write to temporary tables, which the
report is then based on. One of the queries, for some reason, doesn't
write its data the first time the report is run. But then if you run the
report a second time, it works fine!

The temporary tables are cleared at the beginning of each run, so it's
not like it's using data from the previous run. It's not. Also, I've
stopped the process right before this query is run, and then manually
run the query. It works fine if I stop the process and then manually run
it, even on the first run.

But, for some reason, when you run the report the first time this query
doesn't write its data to its corresponding temporary table. If you then
run the report again a second time, it works fine.

All data that the query uses is from the main database tables, and there
are no data changes between the first and second iteration.

Any ideas as to what might be going on?

Thanks!

Neil

Access runs action queries asynchronously; my guess is that it has LAUNCHED
the five queries that fill the temp tables, but that they are still running in
the background and have not finished.

Try putting line

Application.Idle

after the code that launches the queries but prior to opening the report.
It'll pause execution until the work is all done.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Phil]

Access runs action queries asynchronously; my guess is that it has
LAUNCHED the five queries that fill the temp tables, but that they are
still running in the background and have not finished.

Try putting line

Application.Idle

after the code that launches the queries but prior to opening the report.
It'll pause execution until the work is all done.

Never come accross that command. but I believe the correct syntax is
DBEngine.Idle, not Application.Idle

Phil

 

[John W. Vinson]

Never come accross that command. but I believe the correct syntax is
DBEngine.Idle, not Application.Idle

Phil

Sorry - you're perfectly correct!
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Neil]

Am I right in assumimg that on the OnOpen Statement you
1) Create the T_Labels table
2) Run CurrentDb.Execute "qapp_Member_Aversion_List_1", dbFailOnError to
populate the T_Member_Aversion_List Table 3) Try to use the data in
T_Member_Aversion_List Table as the record source for the report.

If so, and these are blind suggestions
On n the onload event if the report, reset the recordsource to
T_Member_Aversion_List. Or, and this should work. Use a command button on a
form to create the T_Member_Aversion_List and then so basically run the 3
steps above and 4) DoCmd.OpenReport "MyReport"

No, the T_Labels table isn't created each time. Its contents are deleted
at the beginning of the run, and then it is repopulated. I'll put the
complete routine at the bottom.

And, yes, I've tried running the code separate from the report, instead
of in the Report Open event. Didn't make any difference.

Here's the complete code that's run in Report Open:

CurrentDb.Execute "Delete * from t_labels", dbFailOnError
CurrentDb.Execute "Delete * from t_labels_Final", dbFailOnError
CurrentDb.Execute "Delete * From T_Aversion_List_Member_Count",
dbFailOnError
CurrentDb.Execute "Delete * From T_Aversion_Meal", dbFailOnError
CurrentDb.Execute "Delete * From T_Member_Aversion_List", dbFailOnError
CurrentDb.Execute "Delete * From T_Member_Food_Aversions",
dbFailOnError

'Populate T_Labels
CurrentDb.Execute "QR_Labels_Start", dbFailOnError

'Populate T_Labels_Final
CurrentDb.Execute "qr_Meal_Labels", dbFailOnError

strSQL = "INSERT INTO T_Aversion_Meal SELECT * FROM Q_Aversion_Meal"
CurrentDb.Execute strSQL, dbFailOnError

'*** EVERYTHING UP TO THIS POINT WORKS FINE.
'*** ALL TABLE ARE POPULATED
'*** EACH OF THE 4 QUERIES APPEND 0 RECS 1ST TIME
'*** 2ND TIME THE REPORT IS OPEN, THEY APPEND >0
'*** EACH OF THE 4 APPENDS A DIFFERENT SELECTION
'*** FROM T_LABELS AND MAIN TABLES
For i = 1 To 4
'Each of the 4 appends to T_Member_Aversion_List
CurrentDb.Execute "qapp_Member_Aversion_List_" & i, dbFailOnError
Next

strSQL = "INSERT INTO T_Member_Food_Aversions SELECT * FROM
Q_Member_Food_Aversions"
CurrentDb.Execute strSQL, dbFailOnError

'Populate T_Aversion_List_Member_Count
CurrentDb.Execute "Q_Aversion_List_Member_Count", dbFailOnError

 

[Neil]

Access runs action queries asynchronously; my guess is that it has LAUNCHED
the five queries that fill the temp tables, but that they are still running in
the background and have not finished.

Try putting line

Application.Idle

after the code that launches the queries but prior to opening the report.
It'll pause execution until the work is all done.

Really? I didn't know that. It'll execute the next line of code before
the query called on the previous line is done executing?? That doesn't
make a whole lot of sense. When you write code you expect one thing to
be done before moving on to the next. Or maybe I'm misunderstanding?...

Anyway, found the bug thanks to Patrick's note, so that's good. But
still curious about what you wrote.

BTW, found an article on the Idle method here:
http://msdn.microsoft.com/en-us/library/office/bb221047(v=office.12).aspx .

Neil

 

[John W. Vinson]

Really? I didn't know that. It'll execute the next line of code before
the query called on the previous line is done executing?? That doesn't
make a whole lot of sense. When you write code you expect one thing to
be done before moving on to the next. Or maybe I'm misunderstanding?...

No, you're not, and that's exactly correct. An Action query could take a LONG
time - for linked large tables, it could take many minutes. The rationale is
that you can start the query in the background and then do other things (open
forms, display data, ...), and if you know you need the results of the queries
you can force a pause to let them finish.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Neil]

No, you're not, and that's exactly correct. An Action query could take a LONG
time - for linked large tables, it could take many minutes. The rationale is
that you can start the query in the background and then do other things (open
forms, display data, ...), and if you know you need the results of the queries
you can force a pause to let them finish.

The interesting thing about that article about the Idle method I linked
to is that it says, "You don't need to use this method in single-user
environments unless multiple instances of an application are running."
So that would seem to imply that it doesn't operate in that way (waiting
for one task to finish before moving on). Not sure.

Also, what about DoEvents? Does that not have any effect on waiting for
the process to finish before moving on?

Thanks.

 

[John W. Vinson]

The interesting thing about that article about the Idle method I linked
to is that it says, "You don't need to use this method in single-user
environments unless multiple instances of an application are running."
So that would seem to imply that it doesn't operate in that way (waiting
for one task to finish before moving on). Not sure.

That may be a change with 2010 - which would explain why it once worked and
now doesn't. I'm not sure either.

Also, what about DoEvents? Does that not have any effect on waiting for
the process to finish before moving on?

I understand that DoEvents waits for OTHER programs - not Access - to
relinquish control of the CPU.

Others know a lot more than I do about this level of programming though!
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com