running multiple querys in order 3

[Greg553]

Hello again,
i have three querys

Qry_updateGrades
Qry_AppendPlayerInfo
Qry_DeleteGraduates

each query does what it says, how do i go about having just one button ( it says End of year update )runthese three querys in the order i have posted,

will the boxes still come up saying you area bout to update so many records, append and delete..

Greg

 

[dhookom]

DoCmd.OpenQuery "Qry_updateGrades"
DoCmd.OpenQuery "Qry_AppendPlayerInfo"
DoCmd.OpenQuery "Qry_DeleteGraduates"

This will display the "boxes". Is this what you want?

Duane
Hook'D on Access
MS Access MVP

 

[nguenthe]

I would caution you on this. I have gotten mixed results when doing sequential queries via code like this because sometimes it seems that query #2 might start before #1 completes. I do not know if this is a perception or if you can just add do events in between the queries. Not sure if Access can run multithreading (or if that is the right terminology). All I know is that sometimes after all of the processing was completed it seemed that the results indicated Query 2 had started before Query 1 had finished. I would like more discussion on this if anyone has experienced this before. (PS - a note of advice if you do take this approach. Build an admin table and after the conclusion of each query write out a record with the date & time stamp to your admin table so that if your program quits or has an interruption you have visibility to where the process died and can easily resume it.)

 

[dhookom]

If you don't set warnings off, these should finish one before moving on to the next.

Duane
Hook'D on Access
MS Access MVP

 

[Greg553]

Thanks guys, was a big help.. i can see the problems etc.
I will try your ideas.

What is the admin table with date and time stamp. How do i go about this...

 

[TheAceMan1]

How are ya Greg553 . . .

If any timing problem should arise (I doubt it) try [blue]dhookom's[/blue] slightly modified code below:

[blue]   DoCmd.OpenQuery "Qry_updateGrades"
   DoEvents
   DoCmd.OpenQuery "Qry_AppendPlayerInfo"
   DoEvents
   DoCmd.OpenQuery "Qry_DeleteGraduates"
   DoEvents[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[nguenthe]

I would make sure that you do adequate testing. I have used the Do Events and found that it did not let one finish before the next started. That being said, I did/do turn warnings off so maybe that is the issue. I am not sure what good it would be to do a series of events with the warnings on as you would be required to babysit each step to click 'OK' when prompted to do updates defeating the purpose of using code to run it in the first place...

 

[Greg553]

Thanks for all the ideas.
i know that most coaches who will be using the software would not rather sit a bay sit through all the warnings. THEY want to hit one button and BAM it's done....HaHa

I could set up just one warning maybe that all this stuff is going to happen i guess before they do it.
Also i would i go about doing that time and date stamp talked about above just incase something goes wrong.

Also, i am learning as i go. How do you set up a place to back everything up like say the A drive or usb drive within the program.
thanks again as this has been a big help

 

[dhookom]

Greg553,
Backups are typically done from outside the application. It might depend on the version of Access.

Do you have other questions? It isn't at all clear if you need more assistance.

Duane
Hook'D on Access
MS Access MVP

 

[Greg553]

No i think that i have a lot of information that will see mw through.
thanks for all the help

 

[nguenthe]

Here is a place to find how to add a record from vba:

http://www.functionx.com/vbaccess2003/howto/addrecord.htm

Here is a place that teaches you to add default fields w/ date & time functions:

http://databases.about.com/od/tutorials/ht/Access_2010_Timestamps.htm

Create a function called 'TimeStamp' or some such thing and make it have a variable string you pass in. at the end of each of your queries, call the function and pass in the name of the query. with the default values for now/date you should be able to capture when the record is created and when each query finished.
You will want to purge the table each time you start the process unless you are trying to keep a running history of every time you rerun it in which case you would add a field to your audit table.

It sounds more complicated than it really is. Just do it in steps and it will come together.

 

[Greg553]

I have one more question, I have set up 3 Buttons

cmd_updategrades
cmd_Appendhistory
Cmd_DeleteGrads

what i want to do is have the first button active, the other two not active, after the first one is done running the update i would like to go to the next one and make it active and the one just pressed not active and so on to the next one.
This way they have todo them in order and i won't run into any issue of starting the next query before one is done running.

Every time they open the form it will only allow the first button to run and so on.

 

[sxschech]

You could use Me.ButtonName.Enabled = False to make it inactive and Me.ButtonName.Enabled = True to make it clickable (active). You may need additional code to handle situations if something happens in the middle of a process or user cancels. As well as set up code in the Open or Load event so that the correct button is enabled.