Access automatically generates form object properties in VBA based on the form's controls and RecordSource fields:
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
AccessFieldhas only one member,
Valuebut 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
AccessFieldobjects, but frankly it's too bobbly-wobbly for my liking, hence the recommendation of using a hidden control instead of referencing a field directly.
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,
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:
I added a text box named
tbFirstName bound to the field
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
Next, I selected
FirstName from the Members list and confirmed that it is an
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
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