Jump Start VBA Skills with MS Access Macros

A macro is a construct to automate programming steps in an application. For example, suppose you want to go to different parts of a document, and change a number of things. Additionally, you want to do this for a number of similar documents. Then you would write a macro, which, by just pressing one button, makes all the changes you want on every document you want to change. Because of this capability, macros are extensively used. They are used in all office applications.

In the Microsoft world the different macros in the Office programs like Word, Excel, Access and others developed at different times, and existed independently. They found a common ground in Visual Basic for Applications, or simply VBA. Learning VBA will help you go a long way toward understanding VB, the programming language and VBScript, the scripting language for Internet applications.

This tutorial is about leveraging the built-in macro object in MS Access to learn VBA. This is not a compendium of VBA; it simply covers how to use a macro to learn VBA. In this tutorial I will show a few basic steps that you could adopt to find out more about VBA. Of course the value of books, both online and offline, cannot be overstated. As the title says, this is just a jump start. As a first step, we will create a few macros, which will be used to bring out the VBA program behind the macro.

Create an access database file Macro.mdb (this is my name for it) as shown. The left side shows all the Access objects that includes macros.

Click on Macros, and you will see that you are allowed only to create a New Macro, as this is a new file with no other objects. Now click on the tab with the icon New. This opens up the next window, the Macro Designer, where you can fashion the macro. It comes up with a default name, Macro1.

Later on, before you leave the macro designer, you will get a chance to save the macro with a name of your choice. Presently it has two columns, Action and Comment. Action is where you state what needs to happen when the macro runs. The comment is for your reference, and lets you explain it in a more descriptive form. Below the column you have the action arguments. Since there are no actions, there are no arguments. The two columns are there by default; you may add additional columns by going to tools->option->view as shown. Here  you can choose to show the Names column and the Conditions column.

This picture shows the macro designer with all four columns (you may have to close and open the macro designer to make the changes visible). In the Macro Name column you give the macro a name, and the condition is some condition that you may want to impose on the action. We will not use this column for this introductory tutorial.

Creating the first macro

Let's create a very simple macro. This is my favorite macro. It's more universal than 'Hello, world." You need to understand English to appreciate "Hello world." This macro, when it runs, simply 'beeps.' To create this macro go to the designer and click on the Action column's first row, where you find a drop-down arrow. From the list, click on Beep, and the designer should appear as shown. Read the explanation in blue at the bottom in the Action Arguments section.

Now you have created your first macro. Click on the designer's close button at the top right most point. The message asks you whether you want to save changes to the macro you created. Click on Yes.

This will bring up this little message asking you to change the name for the macro .

I just called it 'MyBeep.' This will close the message box and shows the main window with the newly created "MyBeep" displayed as shown.

Now you may "run" the macro by going to the main window's left most point and clicking on "!" or from the pop-up drop-down. This should produce a beep from your computer.

Right click on the macro and from the drop-down click on Save As. In the dialogue, using the drop-down, change Save Macro 'MyBeep': as Module.

In the message that follows, keep the defaults and click Convert. This will add automatic error handling code block to the VBA Code as well as the comments that were inserted earlier in the designer.

This gives you an audio alert followed by a new set of screens with a congratulatory massage feed back --"Conversion Finished!" -- as shown. (The congratulatory screen was turned off for this picture).

Now you have a VBA program which does the same thing as the macro under the Project - Macro. The project window of the Microsoft Visual Basic editor has all of the Microsoft Access Class objects, modules, and class modules in the acwztool set node and the macro program that you just converted.

Let's look at what you have created now and later -- much later -- look at the other aforementioned objects. Double click Converted Macro-MyBeep, this opens up the code window as shown. As you can see, the macro was converted to a function with the same name you gave earlier for the macro. The function calls the 'beep' procedure, which does the beeping through the hardware interface. You can also see the error handling code automatically added.

The logos below the menu bar help you to switch between Access, VB editor and graphic user designer pages quite easily. You can also use the icons to run, stop or pause the macro. Please refer to earlier Access tutorials to get a good handle on the menu objects and navigational possibilities. The editor window also gives you access to all the 'debugging' tools for looking through your programs.

Another utility of great value in learning VBA is the online help. Just remember the 'F1' key. It will open up the help file at the location highlighted prior to hitting the 'F1' key. Let's highlight 'beep' and hit 'F1'. This opens up the following page which describes what 'Beep' does.

This was too easy. Let's create a slightly more elaborate macro and see a lot more of VBA in the second macro.

Now I have a table imported into this program for demo purposes. It's called employees. I will create a query from this table called qryEmp (just to limit the columns to a few) and a report based on the query called Employee Report. Now let's create a macro which will open up the report we created. I have not shown all the steps for query and report creation, but you can get them from the ASP Free site under the MS Access sub-heading.

Click on Macro object in the main window and click new to open the macro designer. In the Action column, click on the arrow to pick from the list, Open Report. Add some comments.

The action, OpenReport, requires some arguments. The first argument should be which report, as there could be any number of reports. In the Action Arguments section, click on an empty space in the box Report Name and from the drop-down menu pick the item "Employee Report", which is the only item. There is just this one item now, but if there were many you would get to pick the one you want. Similarly you pick the Preview for the View and Normal for the Window Mode. For now, we are not using the Filter Name and Where Condition, since we want to keep discussion focused on conversion to VBA only. The text area filled with letters in blue accurately describes each item that you focus on, and you should take the time to read it.

Convert this macro to VBA as described earlier in the first macro. Double click this macro in the VB editor and you will see the following code.

Here is one of the important keywords, 'DoCmd'. This command opens up the report we created earlier whenever the macro is run. Highlight DoCmd and hit the key F1; this opens up the next window as shown.

Now you have hit the core of the application object. Experiment by creating various macros,  and see how it transliterates to VBA code. This is a good starting point. There are a number of macros, and you can learn a lot of VBA. If you want more, get into the online help screen with its innumerable links to fine grained description, often with fine examples. 

Now let's test the macro created. It can be run from the main window with all the objects, or it can be run from the VB Editor after bring up the Function and using the start, pause, and stop icons.

As soon as the macro runs, this report created earlier pops-up in the 'Normal' mode.

Summary

The macro route suggested in this tutorial is a good starting point for learning VBA.  Within a short time you could become very productive. The on-line help, and the context sensitive F1 key are invaluable. There is a lot more to macros than what I have shown here, but the basic scheme is the same.  This also helps with understanding VBScript and Visual Basic.