Run queries in sequence

[Smilie]

I have a macro that is running a series of queries to append order information to an archive table. Since the order info is held in 3 one-to-many tables (Order, Line, Lot), they MUST run in sequence or else there will be a key violation. In other words, the Order must be written to the order table so that the line information can be written to the line table, etc...

Is there a way to create conditions in my macro, so that it will wait to run a query until the previous query is finished? I would greatly prefer this to not involve any user prompts.

Thanks in advance!

 

[Fuga]

I didn´t quite understand the key violation problem, but anyway:

If you put this in a module:

sub runquery()

docmd.setwarnings false
docmd.openquery "query1"
docmd.openquery "query2"
docmd.openquery "query3"
docmd.setqarnings true

end sub

then, instead of having macro1 behind your button, you put "runquery".

Does this help?

Fuga.

 

[Smilie]

Thanks for the reply, but your way is more or less what the database is currently doing. The problem is that I need the query that is appending the Order table (query1) to FINISH BEFORE the second query STARTS.

The way the macro/script is read, all of the queries are run at the same time. Since there is not yet an order appended to the order table, there can be no orderdetails in the Line table.

I know that you can put conditions on each of the steps of the macro but I can't figure out what conditions I would need to stipulate. I don't know a way to say "don't start until the previous step is finished". The only way I can think to do it is to have the user prompted to click a button (or some other user activity) after each query is finished, but that is unnecessary work for the user.

Any one else have an idea????

 

[Mike375]

I was not aware that the macro would run all the queries as the same time. In my Access 95 I have a few macros that run a series of queries which are slow to run and as they run I see the little notice at the bottom of the screen and the series of little blue squares going along for each query. But that does not help you

Perhaps one way would be to make a macro for each OpenQuery and then a final macro that only has RunMacro actions.

Another way would be to have the maco do RunMacros and base it on the OnTimer event for a form and have each macro make an entry in an unbound text box and that entry is the condition for the RunMacro action. The last macro that runs could mkae an entry in the text box that would allow a Close Form action to run.

You could also have a couple of forms and each with OnTimer even that runs a macros that opens the query, then closes the form and opens the next form which has a macro opening a query on OnTimer and so on.

Mike

 

[Smilie]

It seems that I was just making life more difficult than it should be!

The real problem that I was experiencing was a faulty relationship. I ran several different tests and realized that it worked sometimes, and not others. The difference was in the cascading relationship in connection with the order of the queries run.

It was a silly mistake that took me complaining about to see the solution!!!

So, thanks for making me think it out PAST where I was going initially.

 

[Fuga]

I used to have that kind of problem, but since I started to use VBA in stead of macros, I haven´t seen it.

Anyway, I seem to remember that there is a "pause" function somewhere that you can use in a module. Either here or in microsoft kb.

Fuga.

 

[Mike375]

I used to have that kind of problem, but since I started to use VBA in stead of macros, I haven´t seen it.

Anyway, I seem to remember that there is a "pause" function somewhere that you can use in a module. Either here or in microsoft kb.

Fuga.

Fuga,

There is a "wait" option for the macro action Keystrokes.

I have had the reverse to you in that code I have attached to OnClick for labels would not run in the right order.

What I have had, but only a couple of times is a macro that appeared to be corrupt and I had to physically write the macro again. In the couple of cases that I had this problem the macro was not reading the conditions correctly. The conditions were [FieldName] Is Null and also [FieldName] Is Not Null.

A bug in Access 95 is that if you have some fields in a form that are based on query fields that are made using the Switch function, then if another form is opened and then closed all the Switch function fields are full of Error.

Mike