Messages after running queries

[Tony Williams]

I have three queries, 2 update and 1 append that run after users have
clicked on command buttons. The queries have to be run in a specific order.
Is there any way to popup a message after a query is run successfully so
that the user knows when to run the next one?
Thanks
Tony

 

[Guest]

I'm not sure if this will be appropriate for your application, but you could
change the command buttons for each query into a single command button that
would run a macro that would run the queries in the appropriate order.

Alternatively, you could use VBA and the DoCmd.runSQL command to run the
queries in a VBA module which would allow you to display a MsgBox after the
queries were finsihed executing.

HTH.

 

[Tony Williams]

Thanks for that suggestion. The problem is that although the queries have to
be run in order each one needs date parameters entering. I like the idea of
the macro and will investigate that. I'm a relative newbie so not too strong
on the runSQL of VBA. If I put a message box after each query in a macro eg
run query1
msgbox
run query2
msgbox
run query3
msgbox

Would the message boxes only appear if the query has run successfully?
Thanks
Tony

 

[Guest]

If you use the format you mentioned, the MsgBox would show after every query
regardless of whether or not it was successful.

A way around this is to use error handling to provide a different message
box and exit the function if there is an error. This is probably very
appropriate since it appears that the later quiers are based on the succesful
completion of the earlier queries so you would want the function to stop
immediately if any of the queires failed. Here's a quick and rough outline
of how the error handling would look:

Function runQueries
On Error GoTo query1_Error_Handler
'code to run query 1
Msgbox "Query1 has run successfully"
Resume_query1_error:
Exit Function
query1_Error_handler:
msgbox "Query 1 failed - the process will stop"
Resume Resume_query1_error

The code would be repeated for queries 2 and 3 also.

For more info on error handling search the VBA help files for
Elements of Run-Time Error Handling

HTH!

 

[Tony Williams]

Thanks for that. It's 20.18 in the UK here so I think I'll call it a day and
try that over the weekend. I'll post back with the results probably on
Monday.
Thanks again
Tony