The Magic Behind Microsoft Access Form Object Properties in VBA

In Microsoft Access, when you create a form and set its RecordSource, Access automatically generates form object properties for each field in the RecordSource and each control on the form.

When you set the RecordSource for a form, Access analyzes the structure of the underlying table or query and generates the necessary code to create a corresponding form object property for each field. These properties are then accessible through VBA, allowing you to manipulate and retrieve data from the form.

Let's take a closer look at how this process works.

Accessing Form Object Properties in VBA

To access the form object properties created by Access, you can use the Me keyword in the form's VBA "code-behind" module. The Me keyword refers to the current instance of the form.

For example, if you want to retrieve the value of the "FirstName" field from the RecordSource, you can use Me.FirstName in your VBA code.  You need to be careful, though, as such code can break if a new textbox named "FirstName" is added to the form, especially if the ControlSource is slightly different (e.g., =[Title] + ' ' & [FirstName]).  For more details, see the section on resolving names below.

Debug.Print Me.FirstName

These properties are read-write (assuming the underlying recordset is updateable), so you can also update their values in code like so:

Me.FirstName = "Mike"

Benefits of Automatic Form Object Properties

The automatic creation of form object properties in VBA provides several benefits:

• Simplified data manipulation: You don't need to manually write code to bind each control to its corresponding field or retrieve data from the form.
• Time-saving: The automatic generation of form object properties saves you from writing repetitive code for each control and field on the form.
• Easy maintenance: If you make changes to the RecordSource or the controls on the form, Access will automatically update the form object properties accordingly.

Gotchas and Caveats

While Microsoft Access automatically creates form object properties in VBA for each field in the RecordSource and each control on the form, there are some important considerations to keep in mind.

Hierarchy for Resolving Names

Understanding the hierarchy for resolving names will help you avoid some potentially nasty surprises:

• Built-in Form Properties: The highest priority is given to built-in form properties. These properties, such as Caption or BackColor, are predefined by Access and have specific meanings and behaviors. If you use a name that conflicts with a built-in property, the built-in property takes precedence.
• Control Names: The next level in the hierarchy is control names. If you have a control on the form with the same name as a field in the RecordSource, the control name will take precedence when referencing the name in VBA code.
• Field Names: The lowest priority is given to field names from the RecordSource. If there is no control with the same name as a field, you can use the field name to reference the data in VBA code.

Resolving Naming Conflicts

If you encounter a conflict between a control name and a field name, consider renaming either the control or the field to avoid ambiguity.  

Personally, I prefix all my control names with standard prefixes based on the control type, such as:

• lbl: Label
• tb: Text Box
• cb: Combo Box
• lb: List Box
• btn: Command Button
• img: Image

Unfortunately, Access makes this problem worse when you use the "Add Existing Fields" tool window to create controls because it sets the control name equal to the field name.

Being Explicit to Avoid Naming Conflicts

If you need to access a field directly–and don't want to risk the addition or renaming of a control in the future clobbering your statement–then you should be explicit.  

Instead of Me.FirstName (which could be a control or a field) use the form's Recordset property to access the field, like so:

Me.Recordset.Fields("FirstName") = "Mike"

To access a control, use the form's Controls collection, like so:

Me.Controls("FirstName").Value = "Mike"

Using Aliases

Another way to avoid naming conflicts is by using aliases in your SQL queries. Aliases allow you to provide alternative names for fields in the RecordSource.

By assigning aliases to fields, you can ensure that there are no conflicts between control names and field names in your VBA code.  When using aliases, remember to use the alias name instead of the original field name when referencing the data in VBA code.

Best Practices

• To minimize naming conflicts and improve code readability, it's a good practice to use consistent naming conventions for controls, fields, and form object properties.
• Consider prefixing control names with a specific identifier, such as "tb" for textboxes or "cb" for combo boxes, to distinguish them from field names.
• Using meaningful and descriptive names for controls and fields can help you avoid confusion and make your code more maintainable.
• Avoid table, field, or control names that conflict with SQL, VBA, and Access reserved words.  It's a bit dated now, but Allen Browne has the most complete list of "bad words" that I'm aware of.

Conclusion

Microsoft Access "magically" creates form object properties in VBA for each field in the RecordSource plus each control on the form through data binding.

This feature simplifies data manipulation, saves time, and makes it easier to maintain your forms and interact with the underlying data.  

To use it safely, though, you need to be aware of the potential pitfalls and understand how Access resolves naming conflicts.

Acknowledgements

• Article title generated with the help of ChatGPT
• Article excerpt generated with the help of ChatGPT
• Portions of this article's body generated with the help of ChatGPT
• Cover image created with Microsoft Designer

UPDATE [2023-09-18]: Fixed language in the introductory section to clarify that each field in the RecordSource corresponds to one property on the form object (not multiple properties per field as the original language suggested).  Thanks to eagle-eyed reader Eric Blomquist for spotting the error.