Use Hidden Controls for More Reliable Code

Mike Wolfe -

Access automatically generates form object properties in VBA based on the form's controls and RecordSource fields:

The Magic Behind Microsoft Access Form Object Properties in VBA
By using the Me keyword, you can leverage the automatically generated properties to retrieve and set data in VBA.
No Longer SetMike Wolfe

Former Access MVP Ben Clothier offers some sage advice on why you are better off creating hidden form controls rather than directly relying on RecordSource field names:

If this is an Access.AccessField ---  a field without a control on the form, then I recommend that you discontinue the practice of referencing it in your VBA code. An AccessField has only one member, Value but it is ephemeral and will be changed whenever the recordsource changes and that can make it hard to validate the VBA codebase.

I recommend that you instead create a hidden control on the form and reference it via a control. I normally color my hidden control red background and yellow font so that it pops out when in design view and that avoids the error.

BTW, that isn't something specific to the add-in but rather the nature of referencing an ephemeral object because it depends on having the recordsource and that it can be resolved in order to build a field list and consequently the AccessField objects, but frankly it's too bobbly-wobbly for my liking, hence the recommendation of using a hidden control instead of referencing a field directly.

Access.AccessField?

When Ben refers to these things as Access.AccessField objects, he is referring to their type as defined in the Access object model.

Access.AccessField is an example of a hidden class.  To view the class and its members, you need to Show Hidden Members in the Object Browser ([F2]):

Here you see what Ben is talking about when he says, "[a]n AccessField has only one member, Value" (IsMemberSafe is a hidden function).

Contrast that with a TextBox object, which has many more:

Testing the AccessField Concept

In a sample database, I created a form bound to the Person.Person table in the AdventureWorks database.

The table has many fields, including the following ones:

  • FirstName
  • LastName

I added a text box named tbFirstName bound to the field FirstName:

I closed and saved the .accdb as AdventureWorksSample, then opened a different .accdb I had laying around (EmojiSample).  I went to the VBA editor, clicked Tools > References > Browse... and selected the  AdventureWorksSample.accdb file.  I then clicked on the Form_SampleForm in the AdventureWorksSample project of the Project explorer:

In the Object Explorer, I picked AdventureWorksSample from the dropdown and then selected tbFirstName which appears as a TextBox object:

Next, I selected FirstName from the Members list and confirmed that it is an AccessField object:

Finally, I selected LastName –which is a field that we did not explicitly add to the form in any way, other than the fact that it is one of the fields of the Person.Person table–and confirmed that it not only exists, but is also an AccessField object:

As you can see, the LastName and FirstName fields are automatically added as AccessField objects that are properties of Form_SampleForm.  However, those properties will cease to exist if Form_SampleForm's RecordSource changes, which is why Ben wisely advises to avoid relying on those properties when writing code.  Using a hidden form control as a replacement for these AccessField properties is a great alternative.  It's a technique I plan to adopt moving forward.

Cover image created with Microsoft Designer