How to execute VBA Access module?

Question

Ok I'm using Access 2003 and I've just started learning Access and VBA a few days ago.
I wrote some code in a module and there are no errors when I press the play button on the debugger toolbar up top.
How do I actually execute this code on my database so that it will do something.
In other words how do I make use of the module?

Please help me out I'm struggling hard and the deadline for this is thursday!

Thanks!

Pankey

Answer 1

Well it depends on how you want to call this code.

Are you calling it from a button click on a form, if so then on the properties for the button on form, go to the Event tab, then On Click item, select [Event Procedure]. This will open the VBA code window for that button. You would then call your Module.Routine and then this would trigger when you click the button.

Similar to this:

Private Sub Command1426_Click()
    mdl_ExportMorning.ExportMorning
End Sub

This button click event calls the Module mdl_ExportMorning and the Public Sub ExportMorning.

Tapetum

The thing is I'm not using a form. I have a database with order information for some products. I need to cross-reference the products to see which ones are sold together most commonly. I thought this is too complicated to do a query so I thought I'd try doing it with VBA. Am I going completely wrong with this? Is there no way to just "call" the procedure on a table? – Pankey

You need to call it from something in order to execute it. I typically use a form, where I can use a Timer Event or a Click Event in order to call it. You can also make these calls through a macro in Access or from a Toolbar. I have never called VBA from a query but it might be possible, see the link included. social.msdn.microsoft.com/Forums/en-NZ/accessdev/thread/… – Tapetum

I see now, I googled running module through macro and found my answer. Thank you a lot! – Pankey

Answer 2

If you just want to run a function for testing purposes, you can use the Immediate Window in Access.

Press Ctrl + G in the VBA editor to open it.

Then you can run your functions like this:

• ?YourFunction("parameter")
  (for functions with a return value - the return value is displayed in the Immediate Window)
• YourSub "parameter"
  (for subs without a return value, or for functions when you don't care about the return value)
• ?variable
  (to display the value of a variable)

Maui

Strg is the equivalent of Ctrl on a US keyboard, by the way. – Exteriorize

Answer 3

You're not running a module -- you're running subroutines/functions that happen to be stored in modules.

If you put the code in a standalone module and don't specify scope in the definitions of your subroutines/functions, they will be public by default, and callable from anywhere within your application. This means that you can call them with RunCode in a macro, from the class modules of forms/reports, from standalone class modules, or for the functions, from SQL (with some caveats).

Given that you were trying to implement in VBA something that you felt was too complicated for SQL, SQL is the likely context in which you want to execute the code. So, you should just be able to call your function within the SQL statement:

  SELECT MyTable.PersonID, MyTable.FirstName, MyTable.LastName, FormatAddress([Address], [City], [State], [Zip], [Country]) As Address
  FROM MyTable;

That SQL calls a public function called FormatAddress() that takes as arguments the components of an address and formats them appropriately. It's a trivial example as you likely would not need a VBA function for that purpose, but the point is that this is how you call functions from within a SQL statement.

Subroutines (i.e., code that returns no value) are not callable from within SQL statements.

Hardtack

Answer 4

I had this same problem and found a helpful article here: https://www.fmsinc.com/microsoftaccess/macro/index.htm

In the end, I ended up changing "sub" to "function" within my module. I was then able to execute the module by name in a macro by using the "RunCode" function. Hope this further helps novices like me who are reading through this! :)

Johnsson

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Semasiology