Calling a Function from Macro Design In Access

2
votes

I have written some sql queries, some sub procedures and have some saved imports.

Now I am trying to use the Macro Design in Access to provide a run button which will run these objects sequentially.

However I dont see any command for running a sun procedure, I can see OpenVisualBasicModule and Runcode.

OpenVisualBaicModule is only opening my sub procedures and Run Code is asking for a function only.

I created a function with all the sub procedure call inside. But thats not working while individually all of them work.

Any suggestion what to do.

2
ms-accessvbams-access-2007
Using RunCode and having your Function procedure call sub-procedures should work. Place a MsgBox as the first line of your Function to test if it runs at all. Otherwise, post the full code of your function, and the text that you use in the Macro's "Function Name" argument. - Andy G

2 Answers

6
votes

Make sure your VBA code is placed inside a Function (not a SUB, that won't work). Create your function like:

Function DoSomething()
  'Do your stuff
End Function

Or if you want to execute a Sub, just create a Function that calls your sub, like:

Function DoSomething()
   Call YourSub()
End Function

Inside the Macro Design, add the 'RunCode' action (Macro Commands > RunCode) and call your function: 

DoSomething()

And voila, you're done.

0
votes

As you are already using code, the Macro seems unnecessary. In the Property Sheet for the Button, find the OnClick row and type in there:

=YourFunctionName()

(this also has to be a Function, not a Sub-procedure; this is a peculiarity of Access)

In your function you can use DoCmd to call any Macro Actions that you are using in your current Macro. Throw a MsgBox as the first line of your function to check that it is running.

You may be less familiar with the code, than a macro, but you will be able to add good comments to it, step-through and debug the code, use sensible error-handling - far easier than you can with a Macro.