ACC1x: How To Determine If a Record Is Being Edited

• Microsoft Access versions 1.0, 1.1

SUMMARY

In Microsoft Access version 1.x, there is no built-in functionality to determine if the current record on a form is "dirty," or being edited. This article demonstrates a method of determining if the current record is being edited.

Note that in Microsoft Access version 2.0, you can use the Dirty property to determine if a record is being edited.

MORE INFORMATION

This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information on Access Basic, please refer to the "Introduction to Programming" manual.

When you are editing a record, the record selector on the left side of the form becomes a pencil. The pencil indicates that changes have been made to the record that have not yet been saved.

If the record is being edited, and you try to save it, Microsoft Access will run the form's BeforeUpdate event before saving the record. Microsoft Access will not run the BeforeUpdate event if the record is not dirty.

The following example demonstrates how to test whether Microsoft Access needs to run the BeforeUpdate event to determine whether the record is dirty:

1. Create a new Access Basic module with the following functions and Declarations section:

         *********************
         Declarations Section
         *********************
         Option Explicit
         Dim FormToTest As Form
         Dim fNotDirty
   

         *********************
         Function IsDirty()
         *********************
         Function IsDirty (F As Form)
            ' Store the form to test
            Set FormToTest = F
   
            ' Set the flag indicating we want to test to see
            ' if the form is dirty or not
            fNotDirty = True
   
            ' Activate the form so the DoMenuItem will work
            DoCmd SelectObject A_FORM, F.FormName
   
            ' Save the current record to see if BeforeUpdate event fires
            On Error Resume Next
            DoCmd DoMenuItem A_FORMBAR, A_FILE, A_SAVERECORD
   
            ' If record is dirty, control goes to TestIsDirty function
   
            ' Return the flag result; if dirty, TestIsDirty will have
            ' fired and toggled the flag to False and will have
            ' canceled the save record (BeforeUpdate) event.
            IsDirty = Not fNotDirty
   
            ' Reset the flag
            fNotDirty = False
         End Function
   
         **********************
         Function TestIsDirty()
         **********************
         Function TestIsDirty ()
            ' If flag is set, we are testing for Edit Mode
            If fNotDirty Then
               ' Indicate we are testing for Edit Mode
               TestIsDirty = True
   
               ' Toggle the flag to indicate to IsDirty that
               ' the form is, in fact, dirty
               fNotDirty = False
   
               ' Cancel the BeforeUpdate event
               DoCmd CancelEvent
   
               ' Control goes back to IsDirty function
           End If
         End Function
   
   

2. Open the form you want to test in Design view and examine the form's BeforeUpdate property. If the BeforeUpdate property is empty, add the following line to the property:

         =TestIsDirty()
   

   If the BeforeUpdate property has a macro listed, open the macro and add the following action as the first action to be executed in the macro or the macro group. This action must appear above any other actions in the macro or group:

         Condition       Action
         -------------------------
         TestIsDirty()   StopMacro
   
      If the BeforeUpdate property has an Access Basic function listed, open
      the function and add the following Access Basic statement as the first
      line in the function. This statement must run before any other statement
      in the function:
   
         If TestIsDirty() Then Exit Function
   
   

3. Call the IsDirty() function to test whether the record is dirty. With the form open in Design view, open an Immediate window and type the following, where <FormName> is the name of your form:

         ?MsgBox (isdirty(Forms!<FormName>))
   

   A message box will display either a 1 (the form is dirty) or a 0 (the form is not dirty).

How to Use the IsDirty() Function with a Command Button

The following example demonstrates how to add a command button to a form that you can use to determine if the current record is being edited: