Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2011
    Posts
    11

    Unanswered: Run multiple queries in sequence on click

    I'm coming into a new position with an existing db that needs to be streamlined...

    There is an existing form with multiple (6) steps to go through in order to update my database from two other db's. Each step is a button attached to a query.

    I would like for my users to click only one button to achieve the same results.

    Can anyone help with VBA to run all of the queries in order?

    Queries:
    Statistics 1
    Statistics 2
    Statistics 3
    Statistics 3A
    Statistics 3B
    Statistics 4

    Thank you!!

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Delete 5 of the 6 command buttons. In the one remaining one, use the statement below multiple times, by varying only the query name.
    Code:
    CurrentDb.QuertDefs("FirstQueryNameGoesHereInQuotes").Execute
    CurrentDb.QuertDefs("NextQueryNameGoesHereInQuotes").Execute
    Etc.
    Sam

  3. #3
    Join Date
    Jun 2011
    Posts
    11

    So simple...

    Love it! Thanks!!

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    You are welcome.

    SL

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Access is asynchronous, which is to say, if given a series of commands, it starts to execute one, moves on to the next one and starts executing it, and so forth. It doesn't wait for the first command to be completed before starting the second one, and this can cause timing problems, especially when running a series of Reports or Queries, depending on how long they take to complete and whether or not they access the same Tables.

    An example would be exactly what you're trying to do here, have a single button run a series of Queries. The following VBA code
    Code:
    DoCmd.OpenQuery "QueryA"
    DoCmd.OpenQuery "QueryB"
    DoCmd.OpenQuery "QueryC"
    will immediately run all three, not waiting for one to finish executing before starting the next one. The answer to halting the code in this type of situation is to use DoEvents.
    Code:
    DoCmd.OpenQuery "QueryA"
    DoEvents
    DoCmd.OpenQuery "QueryB"
    DoEvents
    DoCmd.OpenQuery "QueryC"
    DoEvents returns control to Windows, allowing QueryA to complete running before starting to run QueryB. It then allows QueryB to finish running before starting QueryC.

    DoEvents is an easy, safe bet when encountering what seems to be timing issues, and you need to keep this in mind if you run into problems stacking the Queries, as Sam has suggested.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •