ACC: Changing Control Properties Affects All Records in a Form

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

Changing a control's property setting (such as the BackColor property setting) in a continuous form causes all instances of that control in all records to be changed.

RESOLUTION

You may be able to use the Format property to alter the color of numeric data in a text box based on the data's value (positive, negative, zero, or null), instead of changing the control's property setting.

STATUS

This behavior is by design.

MORE INFORMATION

Microsoft Access stores a single set of property settings for each control on a form. In order for a control to appear differently from record to record on a form, Microsoft Access would have to store and maintain property settings for each control for every record in the form.

If a control has approximately 50 property settings, a form with 1000 records would need to maintain 50,000 property settings. Maintaining this much information for controls would severely impact performance.

Steps to Reproduce Behavior

1. Open the sample database Northwind.mdb (or NWIND.MDB in Microsoft Access 1.x and 2.0)

2. Create the following new macro and save the macro as Highlight:

         Macro Name   Action     Description
         --------------------------------------------------
         On           SetValue   Change BackColor to Yellow
         Off          SetValue   Change BackColor to White
   
         Highlight Actions
         ---------------------------------------------
         SetValue
            Item: [Screen].[ActiveControl].[BackColor]
            Expression: 8454143
         SetValue
            Item: [Screen].[ActiveControl].[BackColor]
            Expression: 16777215
   
   

3. Create a new form based on the Customers table. Add three or four fields from the table to the form. Make sure the form's Default View property is set to Continuous Forms.

4. On the View menu (or Edit menu in Microsoft Access 1.x and 2.0), click Tab Order. Note the name of the first control listed then close the dialog box. Set the following properties for every text box control on the form except the first control listed in the Tab Order dialog box:

         OnEnter: Highlight.On
         OnExit: Highlight.Off
   

5. Save the form as Test.

6. View the form in Form view. Use the TAB key to move among the controls on the form. Note that as you move from control to control, the BackColor property for all controls, not just the active control, is changed.

REFERENCES