running queries one after another

[johnmerlino]

Hey all,

I'm trying to figure out a simple way to run queries one after another. For example, I have these queries:

defense_query (a create query)
append to defense (append query)
finalize defense (create query)


There's several other queries. But they will all still run successively. I did some online investigation on how to do this and all I came up with was one tutorial where guy said something like this:

Docmd.OpenQuery "defense_query"
DoEvents
Docmd.OpenQuery "append to defense"
DoEvents
Docmd.OpenQuery "finalize defense"

I don't know where I would put this and I don't know where I would execute and I'm not even sure if this correctly will run the queries one after the other.

Thanks for response.

 

[John Big Booty]

You would put that code behind a button or the event that you wished to trigger the code.

I don't believe you need the DoEvents function in there.

Your queries would be run consecutively in the order they appear in the code.

 

[Mike375]

Best to also add at the start of the code

DoCmd.SetWarnings False


And add at the end of the code

DoCmd.SetWarnings True

That stops the "warnings" such as You are about to Run and Append Query etc

The Warnings are turned back on at the end of the code

 

[John Big Booty]

Best to also add at the start of the code

DoCmd.SetWarnings False


And add at the end of the code

DoCmd.SetWarnings True

That stops the "warnings" such as You are about to Run and Append Query etc

The Warnings are turned back on at the end of the code

Nice catch Mike

 

[johnmerlino]

You would put that code behind a button or the event that you wished to trigger the code.

Thanks for responses. When you say event, so basically I can double click the first query and in the vba editor I specify that on that click, then do something?

 

[John Big Booty]

OK at what point do you wish this set of queries to run?

Step us through the process and explain when you want the query to fire.

 

[johnmerlino]

Basically, person opens access database, there's a list of queries on the left hand side. They double click first one and then the other queries execute one after another. Of course, not all the queries would execute. But a good 15 for one task and then a good 15 for another task. For that other task, I would expect that I could double click another query and it will execute its "group" of queries.

 

[John Big Booty]

Put your code in the Double click event then, for what ever it is that the user is double clicking.

 

[vbaInet]

Basically, person opens access database, there's a list of queries on the left hand side. They double click first one and then the other queries execute one after another.

Queries don't have events so the code John has already given you can go in the Click event of a button.

If you already have a listbox with the list of queries then yes the Double Click event will work.

 

[John Big Booty]

Lets say you have a List box that has a list of the various groups of queries your user might want to run. Let's also say that his list is feeding from a table that has an IDnumber (PK that is in column zero {hidden} of your list box) and has a description of the queries the user might want to run (this is displayed in the list box {column one}). The code in the list box's On Double Click event might look something like;

Dim strDocName1 As String
Dim strDocName2 As String
Dim strDocName3 As String
Dim strDocName4 As String

strDocName1 = "Query1"
strDocName2 = "Query2"
strDocName3 = "Query3"
strDocName4 = "Query4"

DoCmd.SetWarnings False

Select Case Me.ListBoxName 
     Case 1
          Docmd.OpenQuery strDocName1
          Docmd.OpenQuery strDocName3
     Case 2
          Docmd.OpenQuery strDocName2
          Docmd.OpenQuery strDocName3
          Docmd.OpenQuery strDocName4
     Case 3
          Docmd.OpenQuery strDocName4
          Docmd.OpenQuery strDocName3
          Docmd.OpenQuery strDocName1
          Docmd.OpenQuery strDocName2
End Select

DoCmd.SetWarnings True

You will need as many cases as you have options in your List Box, and you can set up each case to run any set of queries in any order you desire.

 

[jdraw]

Nice catch Mike

This is a nice catch --once you have the code debugged, tested and confirmed working. If you're starting out, make sure that the code works before turning off the warnings.

Just my 2 cents on a great catch and feature -- when used at the right time.

 

[vbaInet]

Or as we all know, use CurrentDb.Execute "QueryName", dbFailOnError which will bypass the error messages and also safely carry out the transaction.

 

[johnmerlino]

Lets say you have a List box that has a list of the various groups of queries your user might want to run.

I'm confused the listbox only allows me to input values from a table in a box:

The wizard creates a listbox which displays a list of values from another table or query. I will type in the values that I want.
*I want the listbox to get the values from another table or query.
* I will type in the values that I want.

So I select

*I want the listbox to get the values from another table or query

and it only allows me to choose one table or query. And then it has me selec a value from field. I don't understand how this achieves what I'm trying to do.

What I'm basically trying to do is choose a group of queries so that I can have them run in sequence.

Thanks for response.

 

[johnmerlino]

I also tried to create a button and then try miscellanous > run query but it only lets me run 1 query.

 

[johnmerlino]

Is there a way to make this easy? Stick visual basic code in a module and then double click the module and have the queries run in sequence? I'm not looking to have a nice looking form or anything. I just want to double click something so the queries run.

 

[John Big Booty]

I'm confused the listbox only allows me to input values from a table in a box:

The wizard creates a listbox which displays a list of values from another table or query. I will type in the values that I want.
*I want the listbox to get the values from another table or query.
* I will type in the values that I want.

So I select

*I want the listbox to get the values from another table or query

and it only allows me to choose one table or query. And then it has me selec a value from field. I don't understand how this achieves what I'm trying to do.

What I'm basically trying to do is choose a group of queries so that I can have them run in sequence.

Thanks for response.

My suggestion was that you have an additional table that list the various combination of queries your user may wish to run, and then put the code I presented in the On Click event of a button.

 

[John Big Booty]

I also tried to create a button and then try miscellanous > run query but it only lets me run 1 query.

Yes this is correct.

However there is nothing that is stopping you modifying that code, as I demonstrated here; Is there

If you want to move beyond the basics of what is achievable with the built in wizards, you need to start thinking outside the box.

 

[John Big Booty]

Is there a way to make this easy? Stick visual basic code in a module and then double click the module and have the queries run in sequence? I'm not looking to have a nice looking form or anything. I just want to double click something so the queries run.

Que?

why do you want to complicate things with modules when this can all be achieved with VBA and the events available on your form?

 

[johnmerlino]

Ok Im now trying to add it to button. I think it might hvae worked.

 

[johnmerlino]

Thanks for all your responses. The easiest thing for me to do that seemed to work is create a button and have this vb:

Public Function initializeFirstSet()
 
DoCmd.SetWarnings False
 
DoCmd.OpenQuery "defense_query"

DoCmd.OpenQuery "CREATE possibles"

DoCmd.OpenQuery "APPEND to possibles"

DoCmd.OpenQuery "FINALIZE possibles"

DoCmd.SetWarnings True
 
End Function

By the way, is there a way to display a popup saying "query complete" when the queries finish running?

Thanks for response.