Setting a button to run a list of queries 1

[LMcleod]

Hi,

I have a table with 3 fields - one is the name of the query ( so each row has a different query name), another is an order number (ie. the order I want that query to be run - 1 = 1st query to be run, 2 = 2nd query to be run etc.)and the 3rd field is simply a check box.
My plan is to have a button that, when clicked, will look through this table and run any queries that are 'ticked' in the check box in that order.
I am no VBA pro, but would appreciate any help on this.

Thanks.

Louise

 

[scriverb]

I created a table called tblQueryList with three fields: QueryName Text(30), SortOrder Numeric Long Integer, and Selected Yes/No CheckBox. If you adjust your table to fit this table you won't have to make any adjustments to the code below.

Create a new query using the following SQL and name it qryQueryList:
SELECT A.QueryName
FROM tblQueryList As A
WHERE (((A.Selected)=True))
ORDER BY A.SortOrder;
The code in red needs to be changed to the name of your table and the field names need to be adjusted to your fieldnames.

The following code behind a command button on a form will execute the queries in the order indicated in the table that have been selected by checking the Selected check box.

Dim db as DAO.Database
Dim rs as DAO.Recordset
Set db = CurrentDB
Set rs = db.OpenRecordset("qryQueryList", dbOpenDynaset)
rs.MoveFirst
DoCmd.SetWarnings False
Do
DoCmd.OpenQuery rs("QueryName&quot
rs.MoveNext
Loop Until rs.eof
DoCmd.SetWarnings True
rs.close
db.close

Let me know if you need any further assistance understanding or setting this up.







Bob Scriver

 

[beetee]

Add some code that looks like this to your Button Click event:

Dim MyDb As DAO.Database
Set MyDb = CurrentDb

dim SqlStr as string
SqlStr = "SELECT QueryName FROM Query Table WHERE YesNoField=True ORDER BY ID"

Dim MyRst As DAO.Recordset
Set MyRst = CurrentDb.OpenRecordset(SqlStr)

Do While Not MyRst.EOF
docmd.openquery MyRst("QueryName&quot
MyRst.MoveNext
Loop

MyRst.Close
Set MyRst = Nothing

MyDb.Close
Set MyDb = Nothing
end sub

 

[LMcleod]

Scriverb - Thanks so much for you help. I'm currently trying your way. However,it seems to have a sytax error with AS in the FROM clause??? Any ideas?

 

[LMcleod]

Sorry - my mistake - everything is working fine now

Beetee - I'll be trying your suggestion now!

Thanks to you both

 

[beetee]

They are really pretty much the same solution; it just depends on whether you want to make the query 'persistent' or not.

Bob's answer is easier to work with and debug, Bill's answer slightly reduces database clutter.

Although Bill's answer has one slight improvement; it checks for EOF at the top of the loop, so that you won't get an error condition if no records are selected.

 

[Drepso]

Thanks Scriverb...exactly what I was looking for too! A star for your efforts!

2 Quick questions though:

I have set up the query list as you suggested and it runs perfectly via a command button. What I would like to do though is prompt the user after all the queries have been run.

Eg. One command button runs a list of delete queries and I would like the user to be prompted with "Confirm Deletion of Records Yes/No"

My second question, I would like display a "Status Meter" for the user to see as part of the above process. Ideally I would like the "Status Meter" to update as each of the queries are run, and at the end a warning asking them to click yes or no to confirm. I have completed the design of the Status Meter as a form but unsure of the code to run it.

Any help with the above would be appreciated as I have limited knowledge of modules and vb.

Thanks in advance

 

[scriverb]

The Docmd.Setwarnings false statement turns the warnings off. If you remove this statemnt each of the queries will prompt the user for completion. Append, Delete, Update queries will all prompt and ask the user if they want to execute them. You could add column to your table called Prompt Yes/No data type and set it to Yes for those queries that you want ACCESS to prompt them. Then while looping through the table with the code only turn the warnings off for those set to NO.

SELECT A.QueryName, A.Prompt
FROM tblQueryList As A
WHERE (((A.Selected)=True))
ORDER BY A.SortOrder;
Update your code with the red code below:[COLOR=blue[
Dim db as DAO.Database
Dim rs as DAO.Recordset
Set db = CurrentDB
Set rs = db.OpenRecordset("qryQueryList", dbOpenDynaset)
rs.MoveFirst
[b]REMOVE-->[/B]DoCmd.SetWarnings False
Do
If rs("Prompt&quot = True then
DoCmd.SetWarnings False
else
DoCmd.SetWarnings True
end if
DoCmd.OpenQuery rs("QueryName&quot
rs.MoveNext
Loop Until rs.eof
DoCmd.SetWarnings True
rs.close
db.close

As for the Status Meter you could fashion some sort of meter on the form with a number of boxes on it that start out invisible but after each query you make that box visible indicating progress of the process. That code for the boxes would go within the Do Loop in the code.


Bob Scriver

 

[Drepso]

Excellent, thanks Bob.
This is very helpful.

I would also like to give the user an option of cancelling the procedure after they have pressed the button which runs the list of queries on the form.

Ideally I would like to give the user the option of either saying Yes go ahead, or No Cancel at the end of the procedure.

The main reason for this I have set up some buttons which takes a little bit of time to run the queries and some others which run really quickly. At present I have no warnings to let the user know what is going on or cancelling if they have run the queries by mistake.

Any help with the above would be appreciated.

Thanks again