Month: August 2017

MS Access can be used as an extremely powerful scripting environment that can tie together manipulation of data using VBA functions and SQL.

Before you can really use this power however you need to know three things
1.Where to put functions
2.How to run them
3.How to string multiple functions together (or script them)

Here is a simple user defined function that we will use for demonstration purposes.

Public Function DisplayCompleteTime()
Dim X As Date
X = Now()
MsgBox "Finished Function at " & X
End Function

1.Where to put Functions?
There is more than one place to place functions. They can be placed in forms, in modules or in class modules. Here I describe how to place them in a module in a MS Access 2003 database called ScriptDatabase the menu path of how you get to the module section varies from Access version to access version but they are very very similar.

Open the Database window

Click on Module and then Click on New a new modal pop up screen should appear

Take your Function and place it in the module
I like to type Option Explicit at the start of every function.

Using the menus save the module – I saved my module prior to taking the snapshot of the screen – if you haven’t done this it will automatically ask you to save the module on exit.

2. Running a Function
OK so you have a function in a module which is saved but you want to run it.
Go back into the module with your function and navigate the menus View / Immediate Window.

How the immediate window displays is a bit unpredictable sometimes it comes up as a modal sometimes it is placed within the module screen and squeezes other sections out either way is should be a blank area with flashing cursor at the beginning.

Now to run your function or functions within the Immediate Window type a question mark followed by the function you wish to run in our case DisplayCompletionTime then simply press return

?DisplayCompleteTime

on return you should get something like this

Congratulations you can now run any function from the immediate window#

3. How to run multiple Functions consecutively

Do same as step 1 but this time type in the following

Public Function MultipleLinkedFunctions()

Call DisplayCompleteTime
Call DisplayCompleteTime
Call DisplayCompleteTime

End Function

as per 2 open the immediate window and then type in ?MultipleLinkedFunctions and press return.

Hitting return will display the complete time this is run three times because you are using a function that asks the function to display three times.

Congratulations you have just run a script in MS Access.