Use controls, not fields

Sometimes we need to use fields from a RecordSource that aren’t shown on the form. It is legal to reference them directly even if they don’t have a control. For example, we can have a form bound to a RecordSource like so:

And only display CompanyName in a textbox but not show the CompanyID.

In code, we can reference CompanyID:

The CompanyID object is actually an Access.AccessField and it exposes only one property, the Value.

However, if the form has its RecordSource changed, then what happens to the Access.AccessField objects? Thus, references to those fields are weak and can break quite easily with cryptic errors. The best method to avoid this particular problem is to create a hidden control as shown:

Usually we set 4 properties:

  • Visible: No
  • Back Color: #FF0000
  • Fore Color: #FFFF00
  • Name: <same as Control Source>

The main reason for making it red/yellow is to help bring attention to the developer that there’s more controls than is visible during the normal form. Having a hidden control provides stability to the code because if RecordSource changes, the code still has valid references and doesn’t break, especially after distributing it to other computers.