How to Populate an Unbound Field in Access Report With Specific Text

by 
# Postmortem: Incorrect Conditional Logic in Access Report Field Population

## Summary
An attempt to dynamically populate an unbound text box on an Access report based on record-level conditions resulted in all records showing the same error message ("Missing General Citation"), regardless of actual field values.

## Root Cause
- The conditional logic in the `Report_Load` event used `If...ElseIf` blocks that only evaluated one condition per record.
- When multiple conditions were true concurrently (e.g., both `SpecificCitation` *and* `GeneralCitation` null), the code would only execute the first matching `ElseIf` branch.
- Structural issue: `Report_Load` runs once at report initialization, not per record. Field checks require per-record evaluation.

## Why This Happens in Real Systems
- Report-level events are often mistaken for record-level events.
- Complex conditional chains without exhaustive checks cause "fall-through" bugs.
- Handling nulls inconsistently across report sections.
- Time constraints lead to insufficient testing of boundary cases.

## Real-World Impact
- Incorrect QA/QC reports flag wrong issues.
- Masking multiple data issues on a single record.
- Reduced trust in reporting accuracy.
- Waste of QA effort investigating false positives.

## Example or Code
**Original Flawed Logic:**
```vba
Private Sub Report_Load()
    If IsNull(SpecificCitation) Then
        ErrorTextBox = "Missing Specific Citation"
    ElseIf IsNull(GeneralCitation) Then
        ErrorTextBox = "Missing General Citation"
    End If
End Sub

Corrected Logic:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
    Dim errors As String
    errors = ""

    If IsNull(Me!SpecificCitation) Then
        errors = errors & "Missing Specific Citation; "
    End If
    If IsNull(Me!GeneralCitation) Then
        errors = errors & "Missing General Citation; "
    End If

    ErrorTextBox = IIf(errors <> "", Left(errors, Len(errors) - 2), "")
End Sub

How Senior Engineers Fix It

  1. Use Record-Loop Events: Implement logic in Detail_Print or Detail_Format instead of report-level events.
  2. Build Compound Errors: Accumulate multiple errors per record using string concatenation.
  3. Leverage Report Sections: Bind ErrorTextBox to the Detail section and reference controls via Me!ControlName.
  4. Explicit Null Handling: Replace ElseIf with independent If checks to capture concurrent issues.
  5. Add Context: Include field names in validation messages.
  6. Sanitize Output: Trim trailing delimiters for clean presentation.

Why Juniors Miss It

  • Confusion between report-wide and section-specific VBA events.
  • Assuming report headers/footers inherit detail section logic.
  • Over-reliance on simplistic examples using If/ElseIf.
  • Testing only one failure mode per record.
  • Not accounting for combinatorial data issues.
  • Misunderstanding Access’s event execution sequence.