Results 1 to 8 of 8
  1. #1
    nggman is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    27

    Function to run query and pass query name to message box


    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.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    nggman is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    27
    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.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.

  5. #5
    nggman is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    27
    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.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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)

    Code:
    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

    Code:
    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

  7. #7
    nggman is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    27
    Many thanks!! This is very handy and I will use it.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You're welcome. Good luck with your project.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Pass a variable to a Pass Through Query
    By violeta_barajas in forum Access
    Replies: 2
    Last Post: 01-26-2017, 07:59 PM
  2. Replies: 5
    Last Post: 04-27-2015, 02:40 PM
  3. Replies: 7
    Last Post: 03-11-2015, 12:48 PM
  4. Replies: 4
    Last Post: 06-11-2013, 01:23 PM
  5. How to Pass ListBox to Function?
    By RedGoneWILD in forum Programming
    Replies: 4
    Last Post: 03-05-2013, 12:13 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums