Microsoft Access: Intro to VBA Programming

​By Lee Allard, Director of Institutional Effectiveness, New Mexico Highlands University

Many of us in the field of institutional research rely on Microsoft Access for various applications, including the extraction of data from enterprise applications like Banner.

While the standard Access tools (e.g. the drag and drop query builder interface) can be very flexible and very useful, they do have certain limitations.  

Let’s say we are interested in our students who change their declared major. We might run a query (against Banner or another database) which produces the following output.  

tt1aug2018.png

This is useful information, but maybe what we need is a summary of the change and its effect. For example, for each major change we might want a simple narrative like “Mike Jones switched from Math to Physics in fall 2015. His GPA changed from 3.1 to 2.4.”

There are some indirect (and clumsy) ways to do this using standard Access queries, but a more efficient and flexible way is to take advantage of the Visual Basic for Applications (VBA) capabilities of Access. (Visual Basic for Applications is a Microsoft Office programming language that is available in all Office products.) VBA programming in Access is similar in general terms to VBA programming in Excel, but has its own special features.

The first step is to create an output table that will store the summary records. We can do this using the “Create Table” command in standard Access (using the command ribbon). We’ll call the table “AIR Tech Tip Output.” All we need is a very simple table with a single field we will call “VarMajorChange.”

Now we’ll dive into some very basic Access VBA programming. To start, we’ll create a module from the command ribbon:

tt2aug2018.png
In the above screen shot, at the very right we’ll click the button labeled “Module”. This will open up the Developer window, where we will see a blank form for entering our code. It should look something like this:

tt3aug2018.png
We can start typing our code right where the cursor is. For starters, we’ll declare our function name (“MajorChange” – must be one word but can be anything you want) and then declare the variables that we will use in the module using the Dim statement. At this point our code will look like this.

tt4aug2918.png
Next, let’s save and rename our module (right now it will have a generic name like “Module1”). Just hit ctrl-S and at the prompt enter any name you like, e.g. “AIR_tip_mjr_chg”. (As an aside, the module should now appear on the list of Access objects in your main Access screen, usually at the bottom of the list.)

Now we’ll add some simple code to delete any existing records in the Access table “AIR Tech Tip Output”, which we will use to store the output records that we produce from the macro. The code now looks like this (I’m no longer showing the code in the VBA Developer window, as above, but of course that is where you would enter the code):

Function MajorChange()
 
Dim rst1 As Recordset
Dim dbs As Database
Dim i, j, k, FirstRec
 
DoCmd.SetWarnings False
 
SQL_del_str = "DELETE [AIR tech tip output].* FROM [AIR tech tip output]"
DoCmd.RunSQL (SQL_del_str)
 
End Function

The “DoCmd.SetWarnings False” statement turns off various warning messages. It’s an optional statement; if you leave it out of the code you may see various warning messages before the system performs certain types of operations (e.g. deleting records).

The next two lines provide a simple example of how to run a piece of SQL code in VBA. Just type in the SQL code enclosed in quotes, then assign it to a variable, in this case we’ll call SQL_del_str. To run the SQL script, use the DoCmd.RunSQL command, with the SQL variable name in parentheses.

The next piece of code is as follows (this will come between the DoCmd.RunSQL command and the End Function statement in the above script):

Set dbs = CurrentDb
Set rst1 = dbs.OpenRecordset("SELECT * FROM [AIR tech tip data]")
 
If rst1.RecordCount Then
    rst1.MoveLast
    rst1.MoveFirst
End If
 
Total = rst1.RecordCount
MsgBox "Total number of records: " & Total
 
rst1.MoveFirst
PriorName = rst1![Stdt_name]
PriorMajor = rst1![Major]
PriorGPA = rst1![Term_GPA]

What we’re doing here is basically creating a “virtual” dataset based on the table “AIR tech tip data,” which stores our original data (as seen in the table at the beginning of our Tech Tip, above). We’ll call this virtual dataset (or “recordset” in VBA lingo) rst1. Note how we use a simple SQL query in the “Set rst1=” statement to call all the records from the table.

The next few lines of code above simply count the number of records in the recordset and assign that value to a variable names “Total.” We can use the MsgBox command to tell us what the value of total is just to make sure things are running right. In our example Total will have the value of 19, corresponding to the number of records in the source table.

Then we initialize three variables (PriorName etc.) which we will use to evaluate the records as we read them.

Our basic approach here – and this is part of the power of Access VBA – is that we will read each record in the source table individually, evaluate that record, and determine our course of action before reading the next record. It give us total control over how we read the records and what we do with them.

Now we will write the processing loop to examine the records and produce the output. Basically, as we read each record we want to compare it to the previous record to determine if the major has changed, assuming of course it’s still the same student.

For i = 2 To Total + 1
    If rst1![Stdt_name] = PriorName And rst1![Major] = PriorMajor Then PriorGPA = rst1![Term_GPA]
    If rst1![Stdt_name] = PriorName And rst1![Major] <> PriorMajor Then
        VarMajorChange = rst1![Stdt_name] & " switched from " & PriorMajor & " to " & rst1![Major] & _
            " in " & rst1![Term] & ". Term GPA changed from " & PriorGPA & " to " & rst1![Term_GPA] & "."
        SQLstr = "INSERT INTO [AIR tech tip output] ( VarMajorChange ) SELECT '" & VarMajorChange & "' as VarMajorChange"
        DoCmd.RunSQL (SQLstr)
        PriorMajor = rst1![Major]
        PriorGPA = rst1![Term_GPA]
    End If
    If rst1![Stdt_name] <> PriorName Then
        PriorName = rst1![Stdt_name]
        PriorMajor = rst1![Major]
        PriorGPA = rst1![Term_GPA]
    End If
    rst1.MoveNext
Next i

There are a few things going on here so we’ll break it up into pieces. First, note how the overall loop works: a “For” statement at the beginning and a “Next” statement at the end (just like in Excel VBA). The first statement in the loop is a simple If-Then statement that just stores the current GPA value, assuming the student remains the same. This will allow us to compare the most recent term GPA value for the previous major to the Term GPA value when the major changes.

The next If-Then statement is more complex; note that it is set off with multiple lines of coding ending with an “End If” statement. This piece of code tests to see if the major has changed for that student. If so, then the code creates a variable called “VarMajorChange” which basically strings together the student’s name, their previous and new major, and the change in GPA. This variable is then output to the table “AIR tech tip output” using another simple SQL statement. (Note that we’ve used the same variable name in the output table as in the code, namely “VarMajorChange”. The variable names could of course be anything you want, subject to VBA naming constraints.)

At the end of the If-then block we redefine the values of the PriorMajor and PriorGPA variables (basically we’re saying that the current value are now the “old” values for purposes of comparing them to subsequent records).

A couple of syntax notes here:

  • In Microsoft VBA the concatenation character is the & sign.

  • If you have a long line of code and wish to break it up into multiple lines for easier readability, use the underscore character (_) to break up the lines.

  • Note the curious syntax that must be used in Access VBA when referring to a variable name in an SQL statement:  ‘ “ & varname & “ ‘

Finally, the last If-then block is process when the student’s name changes. All of the variables are re-initialized for the next round of process with a new student.

The rst1.MoveNext command instructs the system to process the next record in the recordset.

And voila! That’s it. When the code is through running, the output table will look like this:

tt5aug2018.png
To run the macro, there are two options:

  • Under the “Run” option on the Developer screen (i.e. when you are in the VBA editor – see below), click “Run Sub/UserForm”. If you encounter any problems while running the code, click “Reset” before you try again.

tt6aug2018.png

  • You can also create a macro from the standard Access screen. Once you’re in the create macro screen (see below) just select the “RunCode” command and type in the function name as specified in the code (in this case you would type in “MajorChange()” including the parentheses).

tt7aug2018.png
Below is the complete VBA code. One caution: if you copy this directly into Microsoft Access, you may find that you will have to re-type all quotation marks in order for the code to work. Also be cautious with the line continuation character (i.e. the underscore) which may not work properly when copied over. (However, when I tried cutting and pasting the code, everything worked fine.) Also, make sure that the output table is closed before you run the macro.

Function MajorChange()
 
Dim rst1 As Recordset
Dim dbs As Database
Dim i, j, k, FirstRec
 
DoCmd.SetWarnings False
 
SQL_del_str = "DELETE [AIR tech tip output].* FROM [AIR tech tip output]"
DoCmd.RunSQL (SQL_del_str)
 
Set dbs = CurrentDb
Set rst1 = dbs.OpenRecordset("SELECT * FROM [AIR tech tip data]")
 
If rst1.RecordCount Then
    rst1.MoveLast
    rst1.MoveFirst
End If
 
Total = rst1.RecordCount
MsgBox "Total number of records: " & Total
 
rst1.MoveFirst
PriorName = rst1![Stdt_name]
PriorMajor = rst1![Major]
PriorGPA = rst1![Term_GPA]
 
For i = 2 To Total + 1
    If rst1![Stdt_name] = PriorName And rst1![Major] = PriorMajor Then PriorGPA = rst1![Term_GPA]
    If rst1![Stdt_name] = PriorName And rst1![Major] <> PriorMajor Then
        VarMajorChange = rst1![Stdt_name] & " switched from " & PriorMajor & " to " & rst1![Major] & _
            " in " & rst1![Term] & ". Term GPA changed from " & PriorGPA & " to " & rst1![Term_GPA] & "."
        SQLstr = "INSERT INTO [AIR tech tip output] ( VarMajorChange ) SELECT '" & Va
 
 

 Comments

 
To add a comment, Sign In
Total Comments: 4
 
Joe posted on 8/16/2018 3:17 PM
Thanks Lee, this is a nice example that covers a fair bit of ground. VBA removes a lot of limitations in MS Office! (A side note - it may just be my browser, but the for loop just above appears to have been truncated - but I can get the rest from the 'next code up')
Way cool!
Vennessa posted on 8/16/2018 4:37 PM
This is a great example, Lee! I wonder how many folks are familiar with VBA and its application to the MS suite. Maybe you should consider putting out feelers to gauge interest in an Intro to VBA pre-conference workshop?
Lee posted on 8/19/2018 12:15 PM
As a couple of astute readers noted, the last piece of code did get truncated. Here is the rest of the code, starting with the last (incomplete) line above. Thanks for the alert!

SQLstr = "INSERT INTO [AIR tech tip output] ( VarMajorChange ) SELECT '" & VarMajorChange & "' as VarMajorChange"
DoCmd.RunSQL (SQLstr)
PriorMajor = rst1![Major]
PriorGPA = rst1![Term_GPA]
End If
If rst1![Stdt_name] <> PriorName Then
PriorName = rst1![Stdt_name]
PriorMajor = rst1![Major]
PriorGPA = rst1![Term_GPA]
End If
rst1.MoveNext
Next i

DoCmd.SetWarnings True

End Function
Shawn posted on 8/20/2018 10:15 AM
Thank you, Lee! This is a very helpful intro for MS Access VBA.