Run 4 queries sequentially with one "click"

[James Ivey]

Access Newbie. Not a programmer.

Got 4 queries, call them Query1, Query2, Query3 and Query4

I need some code, a module or something, that will fire off the queries one
after another in sequence.

Start
run Query1 on the whole database
run Query2 on the whole database
run Query3 on the whole database
run Query4 on the whole database
Stop

Albert, are you out there? )

James

 

[fredg]

Access Newbie. Not a programmer.

Got 4 queries, call them Query1, Query2, Query3 and Query4

I need some code, a module or something, that will fire off the queries one
after another in sequence.

Start
run Query1 on the whole database
run Query2 on the whole database
run Query3 on the whole database
run Query4 on the whole database
Stop

Albert, are you out there? )

James

DoCmd.OpenQuery "Query1"
DoCmd.OpenQuery "Query2"
DoCmd.OpenQuery "Query3"
DoCmd.OpenQuery "Query4"

Place the above in a procedure.

 

[James Ivey]

DoCmd.OpenQuery "Query1"
DoCmd.OpenQuery "Query2"
DoCmd.OpenQuery "Query3"
DoCmd.OpenQuery "Query4"

Place the above in a procedure.

Thanks Fred.

So it looks like this:

Public Sub Update_Status()
DoCmd.OpenQuery "qryExpired_to_Inactive"
DoCmd.OpenQuery "qryActiveOrNewMember_to_Expired"
DoCmd.OpenQuery "qryPendingActive_to_Active"
DoCmd.OpenQuery "qryPendingNew_to_NewMember"
End Sub

Look right?

So how do I actually run the thing? )

Do I have to open the module, then F5, then step through all those
confirmations?

James
total Newbie

 

[James Ivey]

Thanks Fred.

So it looks like this:

Public Sub Update_Status()
DoCmd.OpenQuery "qryExpired_to_Inactive"
DoCmd.OpenQuery "qryActiveOrNewMember_to_Expired"
DoCmd.OpenQuery "qryPendingActive_to_Active"
DoCmd.OpenQuery "qryPendingNew_to_NewMember"
End Sub

Look right?

So how do I actually run the thing? )

Do I have to open the module, then F5, then step through all those
confirmations?

James
total Newbie

Ok, I think I've got it. First, turn off the confirmations. Then just open
the module and F5.

Works like a champ!

Is that the best way to do it?

James

 

[Dale Fye]

James,

The title of your subroutine implies that these are action queries(INSERT,
APPEND, DELETE, UPDATE) queries, rather than SELECT queries? If this is the
case, then use Currentdb.Execute to run the query instead of Docmd.OpenQuery.

You can turn the warnings off and on right in the code:

Private Sub Update_Status()

Docmd.Setwarnings False
currentdb.execute "qryExpired_to_Inactive"
...
Docmd.SetWarnings True

end Sub

You have to be careful when you turn the warnings off to turn them back on
again as soon as you can to prevent your missing a warning.

Another way to run this code (more useful) would be to create a command
button on a form and put this code in the click event of the form.

HTH
Dale

 

[James Ivey]

Sounds good Dale. Thank you.

James

James,

The title of your subroutine implies that these are action queries(INSERT,
APPEND, DELETE, UPDATE) queries, rather than SELECT queries? If this is
the
case, then use Currentdb.Execute to run the query instead of
Docmd.OpenQuery.

You can turn the warnings off and on right in the code:

Private Sub Update_Status()

Docmd.Setwarnings False
currentdb.execute "qryExpired_to_Inactive"
...
Docmd.SetWarnings True

end Sub

You have to be careful when you turn the warnings off to turn them back on
again as soon as you can to prevent your missing a warning.

Another way to run this code (more useful) would be to create a command
button on a form and put this code in the click event of the form.

HTH
Dale

 

[Rick Brandt]

James,

The title of your subroutine implies that these are action
queries(INSERT, APPEND, DELETE, UPDATE) queries, rather than SELECT
queries? If this is the case, then use Currentdb.Execute to run the
query instead of Docmd.OpenQuery.

You can turn the warnings off and on right in the code:

Private Sub Update_Status()

Docmd.Setwarnings False
currentdb.execute "qryExpired_to_Inactive"
...
Docmd.SetWarnings True

end Sub

You have to be careful when you turn the warnings off to turn them
back on again as soon as you can to prevent your missing a warning.

Another way to run this code (more useful) would be to create a
command button on a form and put this code in the click event of the
form.

The Execute method doesn't raise warning messages like OpenQuery does so if
you use Execute you do not need to turn them off and on.

 

[Dale Fye]

Rick

Good point. I knew I was forgetting something.

 

[Tony Toews [MVP]]

"Pieter Wijnen"

You appear to be two days ahead of everyone else.

Time warp? <smile>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/