Skip to content
Tutorials Center
My learning

Topics

Lesson 21 of 36 ยท Macros and VBA

Events and DoCmd in Access Forms

Make Access forms respond to users. Learn form and control events, their firing order, DoCmd methods, validation in BeforeUpdate and safe SQL from VBA.

Access is event driven. Nothing in a form runs until something happens: the form opens, the user types in a box, a record is saved, a button is clicked. Each of those moments is an event, and you attach code to it with an event procedure. Knowing which event fires when is the single most important skill in Access VBA, because putting code in the wrong event is the source of most "why does this run twice?" bugs.

This lesson covers the key events, the DoCmd object that performs Access actions, validating data before it is saved, and running action queries from code without opening yourself to SQL-injection or quoting mistakes.

Common form and control events

Object Event Fires when
Form Load the form opens, before the first record is shown; set up defaults
Form Current the user moves to a different record (also on open)
Form BeforeUpdate the record is about to be saved; can be cancelled
Form AfterUpdate the record has been saved
Form Close, Unload the form is closing; Unload can be cancelled
Control BeforeUpdate a value changed and focus is leaving; can be cancelled
Control AfterUpdate the new value was accepted
Control Change every keystroke in a text box
Control Click, DblClick mouse clicks (buttons mostly use Click)
Control GotFocus, LostFocus, Enter, Exit focus moves
Combo box NotInList the user typed a value that is not in the list

Opening a form fires Open, Load, Resize, Activate, then Current; closing fires Unload, Deactivate, Close. Moving between records fires Current each time, so use Current (not Load) for anything that depends on the displayed record, such as enabling a Delete button.

A typical handler

Select a button in Design view, set On Click to [Event Procedure], click ... and Access creates the stub:

VBA
Private Sub cmdOpenOrders_Click()
    If IsNull(Me.CustomerID) Then
        MsgBox "Save the customer first.", vbExclamation, "Orders"
        Exit Sub
    End If
    DoCmd.OpenForm "frmOrders", acNormal, , "CustomerID = " & Me.CustomerID
End Sub

Me refers to the form that owns the code. Controls are read with Me.txtName or Me!txtName. The fourth argument of OpenForm is a Where condition: the same text you would write after WHERE, without the word. For text values, wrap the value in single quotes ("City = '" & Me.City & "'"); for dates use hash signs ("OrderDate >= #" & Format(d, "yyyy-mm-dd") & "#").

DoCmd: the Access action object

DoCmd is the VBA twin of macro actions. Frequently used methods:

VBA
DoCmd.OpenForm "frmCustomers", , , "Country = 'Spain'"   ' filtered
DoCmd.OpenReport "rptInvoice", acViewPreview, , "OrderID = " & lngId
DoCmd.GoToRecord , , acNewRec                              ' new record
DoCmd.RunCommand acCmdSaveRecord                           ' save the record
DoCmd.SetWarnings False                                    ' hides prompts: restore it!
DoCmd.Close acForm, "frmOrders", acSaveNo
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryReport", path, True

DoCmd.SetWarnings False silences the "You are about to update 12 rows" prompts, but if your code fails before SetWarnings True, the warnings stay off for the whole session and users lose their safety net. Prefer CurrentDb.Execute (below), which does not show prompts at all.

Validating before saving

Use the form's BeforeUpdate event to check the whole record and set Cancel = True to stop the save:

VBA
Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Nz(Me.txtEmail, "") <> "" And InStr(Me.txtEmail, "@") = 0 Then
        MsgBox "That email address looks wrong.", vbExclamation
        Me.txtEmail.SetFocus
        Cancel = True
        Exit Sub
    End If
    If Me.txtQuantity <= 0 Then
        MsgBox "Quantity must be positive.", vbExclamation
        Cancel = True
    End If
End Sub

Field-level rules that never change belong in the table instead (a Validation Rule, Required, a unique index) because they protect the data no matter which form, query or import writes it. Form code is for rules that need messages, context or several fields.

The stand-in below simulates the cancel-able event pattern. The runner for the form is a function that returns Cancel, exactly the contract of BeforeUpdate:

JS Stand-in for a cancel-able BeforeUpdate

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

No output.

Running action queries from VBA

CurrentDb.Execute runs an INSERT, UPDATE or DELETE string, with no confirmation prompts, and dbFailOnError makes it raise an error (and roll back) if any row fails:

VBA
Private Sub cmdArchive_Click()
    Dim sql As String
    sql = "UPDATE Orders SET Archived = True " & _
          "WHERE OrderDate < #" & Format(DateAdd("yyyy", -2, Date), "yyyy-mm-dd") & "#"
    CurrentDb.Execute sql, dbFailOnError
    MsgBox CurrentDb.RecordsAffected & " orders archived."
End Sub

Concatenating user input into SQL is fragile (an apostrophe in O'Brien breaks it) and unsafe. Use a parameter query: create a saved query with parameters, then fill them through a QueryDef:

VBA
Dim qd As DAO.QueryDef
Set qd = CurrentDb.QueryDefs("qryUpdateEmail")   ' PARAMETERS pId Long, pEmail Text(255); UPDATE ...
qd.Parameters("pId") = Me.CustomerID
qd.Parameters("pEmail") = Me.txtEmail
qd.Execute dbFailOnError

The same idea, parameters instead of pasted text, applies everywhere in SQL. Here is the pattern in SQLite, where the quote problem is visible:

SQL Why quoting matters in SQL strings

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

In VBA the quote-doubling idiom is Replace(name, "'", "''"), but a parameter query avoids the whole problem.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which form event fires every time the user moves to another record?

    Current fires on each record change (and when the form opens). Load fires once.
  2. 2. How do you stop a record from being saved in Form_BeforeUpdate?

    BeforeUpdate has a Cancel argument; setting it to True aborts the save.
  3. 3. Why is CurrentDb.Execute with dbFailOnError better than DoCmd.RunSQL plus SetWarnings False?

    Execute never prompts, so you don't have to switch warnings off, and dbFailOnError reports failed rows.
  4. 4. What goes in the Where condition argument of DoCmd.OpenForm?

    It is only the filter, for example "CustomerID = 5".

Summary

  • Forms respond to events; use Current for per-record logic and BeforeUpdate with Cancel for validation.
  • Me is the current form; Where conditions are plain filter text (quote text, hash dates).
  • DoCmd mirrors macro actions; prefer CurrentDb.Execute ..., dbFailOnError for action queries and parameter queries for user input.
  • Put permanent rules in the table, contextual rules in form code.

Next: DAO recordsets and error handling