Lesson 7

Module 2 focused on a practical goal: improve data quality at the table level while keeping data entry fast and predictable. In Access, the “table design” layer is where you set the rules that every form, query, import, and user action must follow. When you apply validation rules, input masks, lookup strategies, and correct data types in the table itself, you reduce cleanup work later and you prevent subtle query bugs caused by inconsistent values.

In this module you learned how to:

1. Use field-level validation (Validation Rule) to block invalid values before they are saved.
2. Improve error feedback with Validation Text so users see a clear, polite “what to do next” message.
3. Control input format with the Input Mask Wizard for values like SSNs, phone numbers, and postal codes.
4. Limit choices with lookups by using combo boxes/list boxes (preferably through normalized lookup tables and forms).
5. Choose correct data types and field properties (Field Size, Required, indexing) for performance and query correctness.

If you take only one idea from this module, make it this: store data in the most accurate native type, then reinforce entry behavior with field properties. This combination is what gives Access databases long-term reliability.

Another table-design enhancement worth understanding is how macros help you enforce rules and automate behavior. Access has supported macros for decades, but modern versions (including Access 365) make them more useful for real applications because macros can be organized, reused, and combined with table-level logic.

Access supports two macro categories:

1. UI macros (interface automation)
2. Data macros (table-event automation, similar to triggers)

The key design idea is the same as the rest of this module: when your rules live close to the data, you get more consistent results. Use the UI layer for a smooth user experience, and use table-level logic to ensure the data remains correct no matter how it is edited.

UI macros automate user-interface behavior—opening forms, navigating records, validating inputs, running commands, and responding to button clicks or control events. Modern UI macros are easier to maintain because the editor supports structured logic, error handling, and reusable macro actions.

UI macros are most effective when they:

• guide users through correct data entry (for example, focusing a control after an error),
• reduce repetitive steps (open related forms or filter records automatically), and
• work alongside table rules (so the UI is friendly, but the table is authoritative).

Data macros attach logic directly to table events, centralizing business rules in one place: the table. Conceptually, they are similar to triggers in SQL Server. This matters because forms are not the only way data enters a table—queries, imports, append operations, and VBA can all modify data too. A table-level rule helps ensure that the table stays consistent regardless of the entry path.

Common table events include:

1. BeforeChange
2. BeforeDelete
3. AfterInsert
4. AfterUpdate
5. AfterDelete

You can also create a Named Data Macro and call it from other data macros, UI macros, or VBA. This is a clean way to reuse logic—for example, recalculating derived values, standardizing text, or enforcing cross-field rules that go beyond a simple Validation Rule expression.

Design guideline: use data types + relationships + field properties as your first line of defense. Use data macros when you need table-level logic that can’t be expressed cleanly with field properties alone.

Click the Quiz link below to answer a few questions about the entire module.
Field Level Properties Validation Purposes - Quiz