Report Query Works 2nd Time, But Not 1st

Discussion in 'Access General' started by Neil, Oct 16, 2013.

  1. Neil

    Neil Guest

    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
     
    Neil, Oct 16, 2013
    #1
    1. Advertisements

  2. Neil

    Phil Guest

    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
     
    Phil, Oct 16, 2013
    #2
    1. Advertisements

  3. Neil

    Neil Guest

    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
     
    Neil, Oct 16, 2013
    #3
  4. Neil

    Phil Guest

    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
     
    Phil, Oct 16, 2013
    #4
  5. Neil

    Neil Guest


    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, Oct 18, 2013
    #5
  6. Neil

    Neil Guest


    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.
     
    Neil, Oct 18, 2013
    #6
  7. Neil

    Phil Guest

    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
     
    Phil, Oct 18, 2013
    #7
  8. 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
     
    John W. Vinson, Oct 18, 2013
    #8
  9. Neil

    Phil Guest

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

    Phil
     
    Phil, Oct 18, 2013
    #9
  10. John W. Vinson, Oct 19, 2013
    #10
  11. Neil

    Neil Guest

    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, Oct 19, 2013
    #11
  12. Neil

    Neil Guest


    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
     
    Neil, Oct 19, 2013
    #12
  13. 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
     
    John W. Vinson, Oct 20, 2013
    #13
  14. Neil

    Neil Guest

    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.
     
    Neil, Oct 21, 2013
    #14
  15. That may be a change with 2010 - which would explain why it once worked and
    now doesn't. I'm not sure either.
    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
     
    John W. Vinson, Oct 21, 2013
    #15
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.