Custom Access Database Development In Arizona

Validation Rules Vs. VBA Validation: Choosing The Right Layer

One of the questions that comes up in almost every custom database build is where to put the data validation. Access gives you two main options: field-level validation rules set directly on the table, and VBA code that runs when a form event fires. Both work, and both have real tradeoffs that affect how the database behaves over time.

Table-level validation rules are simple and reliable. You set a rule directly on a field, and Access enforces it no matter how the record is changed. That includes imports, append queries, and any back-end updates that bypass the form. The downside is that the error message Access shows is generic and often confusing to users. You can write a custom validation text, but it still triggers after the fact, not during entry.

VBA validation in a form event gives you much more control. You can check multiple conditions, cross-reference other tables, display a clear message that explains the problem, and stop the save without disrupting what the user typed. An Arizona logistics company we worked with needed to prevent dispatch records from being saved without a valid driver assignment. A table rule could not check a related table. VBA could, and it showed a clear message the dispatcher understood immediately.

The practical answer is usually both. Use table-level rules for simple constraints like required fields, number ranges, and date logic. Use VBA for anything that involves multiple fields, related tables, or a message that needs to make sense to the person seeing it. Building both layers into the database from the start means the data stays clean even when someone finds a way around the form.

Subform Link Master/Child Field Mistakes And What They Look Like

Subforms are one of the most useful things in Access and also one of the most common sources of strange behavior in databases we are asked to fix. When a subform is linked to its parent form incorrectly, the symptoms are not always obvious. Sometimes the subform shows all records instead of just the related ones. Sometimes it shows nothing at all. Sometimes it shows the right records on some forms and the wrong records on others.

The link is controlled by two properties: Link Master Fields and Link Child Fields. The master field is the field on the parent form. The child field is the matching field on the subform. They need to point to the same data value, usually the primary key of the parent record and the matching foreign key in the child table. Where it goes wrong is usually one of three things: the field names are different but pointing at the same underlying data and Access gets confused, someone typed a field name slightly wrong, or the subform is based on a query that does not include the linking field in its output.

We repaired a database for an Arizona contractor where the subform for job line items was showing every line item in the database, not just the ones for the current job. The link was set to the job number field on the parent form, but the query behind the subform was filtering by a different field name that did not match. Fixing the query output and correcting the child field name resolved it. The table structure was fine the whole time. The problem was in how the subform was wired together.

When setting up a subform, verify that the linking field is included in the subform's record source, that both field names match exactly (including spelling and capitalization), and that the data types on both sides are the same. A number field linking to a text field will not work the way you expect, even if the values look identical on screen.

Replacing Macros With VBA For Long-Term Maintainability

Access macros are fine for simple tasks. Open a form, run a query, print a report. They are easy to set up and do not require knowing VBA. The problem shows up when the database grows and the macros start doing things they were not designed for. Error handling in macros is limited. Debugging is harder. And when something breaks, the macro often just stops without telling anyone why.

VBA gives you tools that macros do not have. You can write error handlers that catch problems and keep the database from crashing instead of letting it stop cold. You can use variables, loops, and conditions to handle situations that change based on the data. You can call external systems, write to log tables, and send emails without relying on Office automation prompts that may or may not fire depending on security settings.

Converting macros to VBA is usually not complicated. Access has a built-in converter that does most of the work. What the converter does not do is improve the logic or add error handling, so running the converter is the starting point, not the end of the job. We review converted code and add the error handling and logging that makes it usable long-term. An Arizona healthcare office came to us after a macro stopped running mid-process and corrupted a batch of records. The converted VBA with proper error handling caught the same condition on the next run and flagged it without touching the data.