Lesson · 26 min

Introduction to VBA

Write your first VBA procedures, and understand when code is the right tool and when a macro or a query is better.

By the end of this lesson you can

Explain what VBA is and where it lives in Access
Write a simple Sub procedure and run it
Use variables, an If statement, and a loop
Judge when VBA is warranted over a macro or a query

📘 Reading Lesson

Lesson Notes

Read through the key concepts before you try the challenge.

When the list of actions runs out

On the job

You extend the practice database at Lakeside Medical Associates.

The manager wants a button that checks every supply item, finds those below their reorder level, builds a single order list grouped by vendor, and emails it. Macros can open forms and apply filters. They cannot loop through records making a decision about each one.

Your task: Learn enough VBA to recognize when it is genuinely needed, and to write a simple procedure.

VBA — Visual Basic for Applications — is the programming language built into Access, Excel, Word, and the rest of Office. It lives in the Visual Basic Editor, opened with Alt+F11, and it can do things the macro action list cannot: loop through records, handle errors properly, and express arbitrary logic.

Sub CountLowStock()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim lowCount As Integer

    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * FROM Supplies")

    lowCount = 0
    Do Until rs.EOF
        If rs!Quantity < rs!ReorderLevel Then
            lowCount = lowCount + 1
        End If
        rs.MoveNext
    Loop

    MsgBox lowCount & " items are below reorder level."

    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

Read it as a sequence. Dim declares variables. Set assigns object references. The Do Until loop walks through every record until it reaches the end of file. The If statement tests each record and increments a counter. MsgBox reports the result. The final lines release the objects, which matters because Access does not always clean them up promptly.

Key terms

Sub: A procedure that performs actions and returns nothing. Most VBA you write in Access is a Sub.
Function: A procedure that returns a value, so it can be used in a query or a control's expression.
Dim: Declares a variable and its type. Type declarations catch mistakes at compile time rather than at runtime.
Recordset: A set of records your code can move through one at a time — the object that makes looping possible.
EOF: End Of File. True when the recordset has passed its last record; the standard loop condition.
Event procedure: Code that runs in response to something happening — a button click, a form opening, a value changing.

Put Option Explicit at the top of every module, and turn on Tools > Options > Require Variable Declaration so it is added automatically. Without it, a mistyped variable name silently creates a new empty variable instead of raising an error, and the resulting bug is genuinely hard to find. This one setting prevents more wasted hours than any other habit in VBA.

Worked example

Choosing between a query, a macro, and VBA

Decide the right tool for four tasks in the practice database.

1
'Show all items below reorder level' — use a query.
This is a question about data with no action attached. A query answers it, is easier to maintain than code, and anyone can open it. Reaching for VBA here would be building a program to do a query's job.
2
'Open the appointments form filtered to today' — use a macro.
A fixed sequence of standard actions with no logic. The macro action list covers it, and a macro is far easier for the next person to read and modify than equivalent code.
3
'Check every item, group by vendor, and email the order list' — use VBA.
This requires looping through records, making a decision per record, building output, and calling another application. Macros cannot loop, so this is genuinely past what they can express.
4
'Prevent saving a record with a quantity below zero' — use table validation first.
The cheapest correct answer is often not code at all. A validation rule on the field enforces this everywhere the data is touched, including direct table entry, where form-level VBA would be bypassed entirely.

Result: Each task solved with the simplest tool that can do it correctly.

Reach for the simplest tool that works: validation, then query, then macro, then VBA. Code is the most powerful option and the most expensive to maintain.

Check your understanding

Which task genuinely requires VBA rather than a macro?

Challenge

Apply what you've learned in this lesson.

Work on a copy of your database. Code that manipulates data deserves the same caution as an action query.

Open the VBA editor with Alt+F11 and enable Require Variable Declaration. Confirm Option Explicit now appears at the top of new modules.
Type the CountLowStock procedure from this lesson into a new module, adapting the table and field names to your database. Run it and confirm the count matches what a query returns.
Modify it so that instead of counting, it builds a message listing each low-stock item's name. You will need to concatenate strings inside the loop.
For each of the four tasks in the worked example, write one sentence justifying the tool chosen. Then add a fifth task from your own database and choose a tool for it.

Finished this lesson?

Progress is saved in this browser only. It is not a grade — official progress lives in Brightspace.

← Automating Tasks with Macros