In this article I'll show you how to highlight edited fields on a form and help you understand some of the control and form events (see additional links for information on all the events.).

The first thing we need to understand is what are events. For the control (e.g. textboxes) events, we'll be focusing on the Change and BeforeUpdate events. [Note: Controls and Forms have many events in common, but there are some that are specific to each.]

In order to be able to know if a field value has changed we need some way to compare the before and after value of the field. For that we will use the .Value and .OldValue property.

When you edit a value in a control, you're only changing the value stored in that control not the value of the underlying bound field in the record. The change to the record (and its fields) isn't saved until you save the record. Once more, a record is a collection of fields; changes to the fields are not saved until the record is saved. So the record value (.OldValue) and the editted value (.Value) are available for you to compare only before you save the record. If no changes were made, the values are the same.

[Note: You can't directly use Null values in comparison, e.g. Null = Null and Null = 1 will both return Null instead of a TRUE or FALSE.]

This is what will allow us to highlight fields that have been edited and notifiy the user of any changes in value.

The heart of my approach is a subroutine that can be called from any control, i.e. sHighlightEdits(). You pass a contol the the subroutine. It checks to see if they are different, and if the value has been changed it will change the background to a highlighted color.

[Note: If you've added conditional formatting to change a control's backcolor while it has focus, that takes precedence over the highlighting code I'm presenting in this article.]

The one line of code you need to add to each control's AfterUpdate (happens after change to control, not record, is made) event is:

sHighlightEdits Screen.ActiveControl

VBA Code

Private Sub sHighlightEdits(ctlControl As Control)

    '*******************************************************************************************
    'Description: Code to highlight data entry form controls values that have been changed
    '© Azli Hassan, http://azlihassan.com/apps
    '*******************************************************************************************

    On Error GoTo ErrMsg

    Dim ctl As Control, blnChanged As Boolean

    'No need to highlight for new record.
    If Me.NewRecord Then Exit Sub
    
    Set ctl = ctlControl

    With ctl
		'Add more control types as needed
		'https://msdn.microsoft.com/VBA/Access-VBA/articles/label-controltype-property-access
        If .ControlType = acTextBox _
           Or .ControlType = acComboBox Then
            If .Enabled Then 'If it's disabled then there wouldn't be any ability to edit
                Debug.Print "ctl.Name = " & .Name
                'Check if value has changed
                'First, check that BOTH aren't null
                If Not (IsNull(.Value) And IsNull(.OldValue)) Then
					'Both are not Null
                    'Check that one is null (if so, went from a value to a null or vice versa)
                    If IsNull(.Value) Or IsNull(.OldValue) Then
                        blnChanged = True
                    Else
                        'Neither are null, so check if they are different
                        blnChanged = .Value <> .OldValue
                    End If
                Else
                    'Both are null, so they are the same
                    'Can't compare Null values. Go ahead and try the following:
                    ' - Debug.Print (Null = Null)
                    ' - Debug.Print (Null = 1)
                    ' - Debug.Print (1 = 1)
                    blnChanged = False
                End If
                'Highlight if value has changed
                If blnChanged Then
                    .BackColor = cHighlightColor
                Else
                    'Get original backcolor from the end of controls .Tag property
                    .BackColor = GetBackcolorCTag(.Tag)
                End If
            End If
        End If
    End With
    
ExitHere:
    Exit Sub

ErrMsg:
    MsgBox "An error has occurred in sHighlightEdits()." & vbNewLine & vbNewLine & _
           "Error Number:" & Err.Number & " (" & Err.Description & ").", vbCritical, "Error!"
    Debug.Print "sUpdateControlTipTextLabel()"
    Debug.Print "Err.Number = " & Err.Number
    Debug.Print "Err.Description = " & Err.Description
    Resume ExitHere
End Sub

There are a few things to know about using my code:

• Need to set up a few module-level constants.
• Understand that each control's Tag property will be used, so much be sure that you have enough space to store the the original backcolor in the tag and adapt any code you may have that uses the .Tag property acccordly as it is limited to a string expression up to 2048 characters long.
• Need to add event procedure code to certain events for all the controls that you want to highlight changes. Luckliy, it's the exact same line of code for each control.

When the form is opened, code in the form's Open event will add each control's current backcolor at the end of the .Tag property (see sample file for the code). A 'delimiter' is used to demark where to find the highlight color in the .Tag property.

Private Const cClrDelimiter As String = "#bc" 'Used to find stored current control backcolor in control's tag
Private Const cHighlightColor As Long = vbYellow 'Color used to highlight an editted control

A function, GetBackcolorCTag(), is used to strip the original color from the .Tag property when resetting the color back to the original color, i.e. unhighllighting.

Additional features included in sample file

I added code that unhighlights all the fields if the forms's Undo event is triggered, e.g. pressing ESC twice to undo all of the changes to a record (the first ESC only undoes the change in the field with focus if a change was made. If not, only one ESC keypress is needed.).

I've also added code, using a subroutine called sUpdateControlTip() called from the Change / GotFocus / Lostfocus control events, that changes text in a label (lblControlTip) to show you:

• What data field you're currently on, i.e. has focus. This uses the 'ControlTip Text' of the control as the text to be displayed.
  
  
  
• Whether you're currently editing the field, using the Change() event.
  
  
  
• Once you've made a change to the control (i.e. move to another control), and then move back to that control, the text will say what change you've made to the value (if any).
  

Just before you save any record changes by moving to another record another subroutine, sNotifyEdits(), will run that notifies you of the changes made using the form's BeforeUpdate event (happens before record is saved so that .OldValue and .Value are available to compare the before and after values). As this uses a VBA MsgBox to notify the user, it is limited to 'approximately 1024 characters, depending on the width of the characters used', so if you have many fields you may want to change it to only say which fields have changed. [Note: vbNewLine counts as 2 characters]

In a future article, I'll show you how to use more of the form events to control enabling/disabling of record navigation buttons and record operation (Add, Delete, Save, and Undo) buttons. The sample file for this article has a simple version of enabling/disabling code, but I didn't include save or undo buttons because that's beyond the intended scope of this article.

Caveat

While this method also works for continuous / tabular forms, it will highlight the field for ALL visible records since formatting applied to a control applies to that control for all records (unlike the built-in conditional formatting feature).

Additional Links

• Form Events (Access)
• TextBox Events (Access)
• Order of events for database objects
• Download the Sample File