Function to run query and pass query name to message box

[nggman]

I am not too skilled in VBA. I want to make a simple VBA function that will run a query and then pass the name of that query into a message box letting me know the query has completed. What is the easiest way to do this? Thank you.

[Micron]

What type of query? How are you running or calling it? Is it a stored query? You want the function to be able to accept any query name and do this, or is it for one particular named query?

[nggman]

I have several named queries that I have to run by hand. Some are update queries and some make tables. I am making some macros to make my life easier but I have to be careful that each query runs successfully before the next starts. The message box is just to let me know that a given query is done.

[Micron]

I and many others who respond here are not big on macros, preferring vba code instead. I think you can use the message box function after the Open Query action in a macro, but I'm pretty sure you'll get the message regardless of whether the query was successful or not. Since some of these are action queries, that might not be very useful to you. If you can create a standard module (e.g. mdlCommonFunctions) to hold a function, I can whip up something and show you how to call it from code. You could keep a lot of useful functions in this module that you can call from any place in the db. An example of a stored query name would help.

Otherwise, the macro action to open a message box is all I can think of.

[nggman]

I understand that. I am not a hard core developer though and try to do what I can to make things easier. I am actually using a combination of macro and simple VBA. Some of the VBA I call from the macro. I have created some simple modules that I can put functions in. 001-Make total Orders is one of the names of a query I run. The process I am doing was created before I started here. I am just trying to make it a little easier for myself as I have to do it monthly.

[Micron]

You can use the OpenQuery method to run any query but then have to deall with turning warnings on and off when the query is an action query. Plus, that method won't tell you anything if the query fails completely or even partially. The following deals with another method that dispels with the warnings plus can tell you how many records were affected. What's NOT here is the ability to roll back the changes if there is any type of failure. That's a bit more complex and isn't present because I have no idea if it's important.

To call for running action query and not utilize the function return value:
RunActionQuery "001-Make total Orders"

To call same but utilize the return value:
If RunActionQuery "001-Make total Orders" = False Then...
- do whatever, such as Exit Sub. Similarly, a test for True could be used if more compatible with whatever follows it.
NOTE: requires a reference to the DAO library in the vb editor (Tools, References)

Function RunActionQuery (qryName As String) As Boolean
Dim db As DAO.Database

RunActionQuery = False 'ensure return value of function is False
On Error GoTo errHandler
Set db = CurrentDb
db.Execute qryName, dbFailOnError
msgbox db.RecordsAffected & " records were affected."
RunActionQuery = True 'if no error, function returns True

exitHere:
Set db = Nothing
Exit Function

errHandler:
'IFs or Select Case block could be added here to customize messages
msgbox "Error " & err.Number & ": " & err.Description
Resume exitHere 'Function return value remains False

End Function

To call non-action query: 0=can add only; 1=can add, edit, view; 2=read only I left off the view mode option, assuming the default would suffice.
Notes:
- warnings don't apply as this isn't an action query
- this could be a sub instead of function, since it isn't returning anything. If a return value is needed (say to suspend further code from executing in the module that called this procedure, you have the example above on how to modify it to be a function instead.

RunSelQry "qryYourSelectQueryName", 2

Sub RunSelQuery (qryName As String, Mode As Long)

On Error GoTo errHandler
DoCmd.OpenQuery qryName,,Mode

exitHere:
Exit Sub

errHandler:
'IFs or Select Case block could be added here to customize messages
msgbox "Error " & err.Number & ": " & err.Description
Resume exitHere

End Sub

BTW - spaces, names, numbers, special characters (save for underscore _ ) for any object or variable name is just a bad idea - https://access-programmers.co.uk/for...d.php?t=225837

[nggman]

Many thanks!! This is very handy and I will use it.

[Micron]

You're welcome. Good luck with your project.