Skip to content
Tutorials Center
My learning

Topics

Lesson 20 of 36 ยท Macros and VBA

VBA Basics for Access

Start programming Microsoft Access with VBA. Learn the VBA editor, modules, variables and data types, Sub and Function procedures, conditions and loops.

VBA (Visual Basic for Applications) is the programming language built into Access. Where a macro is a menu-driven list of actions, VBA gives you variables, loops, functions, error handling and the full object model of Access and the other Office programs. Most professional Access applications are a mix: tables and queries for the data, forms and reports for the interface, and VBA for everything that needs real logic.

VBA does not run on this site, so the code blocks here are read-only and each lesson pairs them with a small JavaScript or SQL stand-in that shows the same logic. The syntax in the blocks is real VBA that you can paste into Access.

The VBA editor

Press Alt+F11 (or choose Database Tools > Visual Basic) to open the Visual Basic Editor. The important windows are:

  • Project Explorer (Ctrl+R): lists your forms, reports and modules.
  • Code window: where you type code.
  • Immediate window (Ctrl+G): runs one-line commands and shows Debug.Print output. It is the best place to experiment.
  • Locals and Watch windows: show variable values while debugging.

Code lives in two kinds of modules. A standard module (Insert > Module) holds shared procedures you can call from anywhere. A class module belongs to one form or report (its code-behind) and holds event handlers such as cmdSave_Click. Put reusable logic in standard modules and form-specific handlers in the form's own module.

Always put Option Explicit at the top of every module (or tick Require Variable Declaration under Tools > Options). It makes VBA reject misspelled variable names instead of silently creating new empty ones.

Variables and data types

Declare variables with Dim and give them a type:

VBA
Option Explicit

Sub VariablesDemo()
    Dim customerName As String
    Dim quantity As Long
    Dim unitPrice As Currency
    Dim orderDate As Date
    Dim isPaid As Boolean

    customerName = "Maria Lopez"
    quantity = 12
    unitPrice = 4.5
    orderDate = Date            ' today
    isPaid = False

    Debug.Print customerName & " owes " & Format(quantity * unitPrice, "Currency")
End Sub

The types you use most: String, Long (whole numbers, use it instead of Integer), Double, Currency (exact money arithmetic), Date, Boolean, and Variant (anything, including Null, which is how database values arrive). Join text with &, not +. Constants are declared with Const TAX_RATE As Double = 0.2. A comment starts with an apostrophe.

Database fields can be Null, and a String variable cannot hold Null. Wrap risky reads with Nz(): Nz(rs!Phone, "") returns an empty string when the phone is missing.

Procedures: Sub and Function

A Sub performs actions. A Function returns a value and can be used in code, in queries and in control sources.

VBA
Public Function LineTotal(qty As Long, price As Currency, _
                          Optional discount As Double = 0) As Currency
    LineTotal = qty * price * (1 - discount)
End Function

Public Sub ShowTotal()
    MsgBox "Total: " & Format(LineTotal(3, 9.99, 0.1), "Currency")
End Sub

Assigning to the function's own name sets the return value. A public function in a standard module can be called from a query, for example SELECT LineTotal([Qty],[Price]) AS Total FROM OrderLines, which is how Access queries use custom logic. ByVal passes a copy of an argument, ByRef (the default) lets the procedure change the caller's variable. Use ByVal unless you want the change.

Decisions and loops

VBA
Sub Classify(total As Currency)
    Select Case total
        Case Is >= 1000
            Debug.Print "Large"
        Case 100 To 999.99
            Debug.Print "Medium"
        Case Else
            Debug.Print "Small"
    End Select
End Sub

Sub CountDown()
    Dim i As Long
    For i = 5 To 1 Step -1
        Debug.Print i
    Next i

    Dim n As Long
    Do While n < 3
        n = n + 1
    Loop
End Sub

If ... Then ... ElseIf ... Else ... End If handles two-way and multi-way choices; Select Case is cleaner for many ranges. Loops are For...Next, For Each...Next (over collections such as a form's Controls) and Do...Loop with While or Until. Exit early with Exit For or Exit Do.

The stand-in below mirrors the order-size logic with a JavaScript function so you can experiment with the boundaries:

JS Stand-in for Select Case and a loop

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Calling procedures from Access

Three common ways to run your code:

  1. Events: select a control, open the Event tab in the property sheet, choose [Event Procedure] and click ... to jump to the handler (see events and DoCmd).
  2. Macros: the RunCode action calls a function, for example =ShowTotal().
  3. Immediate window: type ShowTotal or ?LineTotal(3, 9.99, 0.1) and press Enter.

Name things consistently: Public for shared procedures, Private for handlers, prefixes such as txt, cmd, cbo for controls, and verbs for procedures (RecalculateTotals).

Domain aggregate functions

VBA can ask the database a quick question without opening a recordset. DLookup, DCount, DSum, DMax and friends take a field, a table or query, and an optional criteria string:

VBA
Dim openOrders As Long
openOrders = DCount("*", "Orders", "Status = 'Open'")
Debug.Print "Open orders: " & openOrders

They are convenient but slow if called per row in a query; for heavy work use a query or recordset. The SQL equivalent of the call above is runnable here:

SQL SQL behind DCount and DSum

Press Run (or Ctrl + Enter) to see the result here.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does Option Explicit do?

    With Option Explicit a misspelled variable name is a compile error instead of a silent new Variant.
  2. 2. Which is the right way to join two strings in VBA?

    & always concatenates as text; + can add numbers or fail with Null.
  3. 3. How does a Function return its result?

    VBA functions assign to their own name, for example LineTotal = qty * price.
  4. 4. Which window lets you test a one-line expression and shows Debug.Print output?

    The Immediate window (Ctrl+G) runs statements directly and receives Debug.Print.

Summary

  • Open the editor with Alt+F11; use standard modules for shared code and form modules for event handlers.
  • Start every module with Option Explicit; use Long, Currency, Date, String, Boolean and Nz() for Nulls.
  • Sub does work, Function returns a value and can be called from queries.
  • Use If, Select Case, For, For Each, Do for logic and DCount/DSum for quick lookups.

Next: Events and DoCmd in forms