Running queries, VIA VB, but not running in Order

[mike02]

Hey, I have this set up to run a sequence of events on a button click.



all events run, however not in the proper order. how can adjust it to go in the right order? (I have it listed in the order I want)

'Run Append Temp Monthly Returns,
'Delete NonNumeric Values
DoCmd.SetWarnings False
CurrentDb.Execute "qryDeleteTempNullReturns", dbfailonExecute
DoCmd.OpenQuery "qryAppendTempMonthlyReturns", acViewNormal
CurrentDb.Execute "qryAppendMonthlyReturns", dbfailonExecute
DoCmd.OpenQuery "qryDeleteTempMonthlyReturns", acViewNormal
DoCmd.SetWarnings True

thanks,

[Dal Jeanis]

Shouldn't that be dbFailonError?
http://stackoverflow.com/questions/1...being-executed

[orange]

As Dal said, it should be dbFailonError. And With

CurrentDb.Execute "qryDeleteTempNullReturns", dbfailonError

you should not be using the SetWarnings False. SetWarnings True
That's a feature of dbFailOnError, if an error, then you'll get a message, if no error no message.

Good luck.

[Missinglinq]

...you should not be using the SetWarnings False. SetWarnings True
That's a feature of dbFailOnError, if an error, then you'll get a message, if no error no message.

But he's not using CurrentDb.Execute to run all of his Queries, is he?

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.

An prime example of this would be the very situation you describe; a button that runs a series of Queries where all but the first Query is dependent upon the previous Query being completed before it starts to execute. The following VBA 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.

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 this type of timing issues.

Linq ;0)>

[orange]

But he's not using CurrentDb.Execute to run all of his Queries, is he?

Not all, but he is doing some of each

DoCmd.SetWarnings False 
CurrentDb.Execute "qryDeleteTempNullReturns", dbfailonExecute 
DoCmd.OpenQuery "qryAppendTempMonthlyReturns", acViewNormal 
CurrentDb.Execute "qryAppendMonthlyReturns", dbfailonExecute 
DoCmd.OpenQuery "qryDeleteTempMonthlyReturns", acViewNormal 
DoCmd.SetWarnings True

[mike02]

Thanks for the help!

[JrMontgom]

Thanks for the help!

I was wondering if you could call a function with a Boolean return value for example [Code]
(This is pseudocode)
If Func1Qry1 then
If Func2Qry2 then
If Func3Qry3 then
Where Func1Qry1(qry as Query)as Boolean etc. Can this avoid the asynchronous behavior of MSAccess?