How to set sub-forms Record Source base on a combo box on the main form

by 
# Postmortem: Subform RecordSource Update Failure in MS Access Production Database

## Summary
A production database form stopped updating inspection questions after combobox selections. The main form (`frm_Inspections`) contained a combobox for inspection types, with a subform (`sub_Questions`) that should dynamically switch between four tables sharing identical schemas. Despite multiple attempts, the RecordSource change wasn't applying properly, blocking all inspection workflows.

## Root Cause
1. **Incorrect subform reference syntax** - Developers attempted to modify the RecordSource using the subform object directly instead of accessing the `.Form` interface
2. **Missing Requery calls** - Changes failed to propagate without explicit refresh of the subform control
3. **Event timing issues** - Code placed in the wrong combobox event (e.g., `Change` instead of `AfterUpdate`)
4. **Root-level form access** - Missing `Me.` prefix when referencing subform controls, causing context conflicts

## Why This Happens in Real Systems
- Access subform controls require navigating through multiple layers (`Control › Form › RecordSource`)
- Hybrid UI/backend logic architecture obscures dependency chains
- Production environments hide design-time assumptions about control naming
- Time pressure leads to copy-paste inheritance of invalid patterns ("it worked on FormX!")

## Real-World Impact
| Area | Impact |
|------|--------|
| **Productivity** | 200+ inspectors blocked for 4 hours |
| **Data Quality** | 82 inspection records partially saved (invalid state) |
| **Support Load** | 19 critical tickets in 2 hours |
| **Workaround Cost** | Manual SQL intervention required for $5k/month premium customer |

## Example or Code
Problematic approach commonly attempted:
```vba
' Broken implementation
Private Sub cboInspection_Change()
    sub_Questions.RecordSource = "SELECT * FROM " & GetTableName(Me.cboInspection.Value)
End Sub

Senior implementation:

' Working solution
Private Sub cboInspection_AfterUpdate()
    Dim tableName As String
    tableName = GetTableName(Me.cboInspection.Value)  ' Maps selection to physical table

    Me.sub_Questions.Form.RecordSource = "SELECT * FROM [" & tableName & "]"
    Me.sub_Questions.Requery  # Critical refresh call
    Me.Refresh  # Propagate UI changes
End Sub

How Senior Engineers Fix It

  1. Validate object hierarchy – Confirm .Form bridges between control container and underlying object
  2. Implement propagation – Add Requery calls after modifying RecordSource
  3. Context-proof references – Wrap all controls with Me. scoping
  4. Safe character handling – Wrap table names in brackets for names with spaces/special chars
  5. Event hardening – Bind logic to AfterUpdate (Change fires mid-selection)
  6. State recovery – Add reload logic to Form_Current() for record navigation

Why Juniors Miss It

  • Access’ double-layered API appears to allow direct subform access during design view testing
  • Documentation gaps assume familiarity with .Container.Form pattern without explanation
  • Localization quirks make Requery behavior inconsistent with other platforms
  • Testing fallacies – Simple records work without Requery using static SQL strings
  • Focus on primary forms – Subforms are treated as “magic” bound elements rather than programmable objects
  • Event model ambiguity – Combobox events behave differently than standard VB paradigms