Microsoft KB Archive/106298

From BetaArchive Wiki

ACC1x: How To Determine If a Record Is Being Edited


The information in this article applies to:

  • Microsoft Access versions 1.0, 1.1


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.


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:


    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:

  1. Open the sample database NWIND.MDB. Open the Categories form in Design view, and make the changes listed above to the BeforeUpdate event. In step 2, modify the Validate Primary Keys.Categories macro so that the first lines of the macro are as follows:

          Macro Name     Condition        Action
                         TestIsDirty()    StopMacro 

  2. Create a new Access Basic function called IsCategoriesFormDirty() or a new macro called IsCategoriesFormDirty. Note that the function is the preferable method, as the macro method requires IsDirty() to run twice.

    To use the function method, create the following Access Basic function:

          Function IsCategoriesFormDirty ()
             If IsDirty(Forms!Categories) Then
                MsgBox "Dirty!"
                MsgBox "Not Dirty!"
             End If
          End Function 

    To use the macro method, create the following macro:

          Condition                      Action    Described Below
          IsDirty(Forms!Categories)      MsgBox          1
          Not IsDirty(Forms!Categories)  MsgBox          2
          IsCategoriesFormDirty Macro Actions
          1. MsgBox
                Message: Dirty!
          2. MsgBox
                Message: Not Dirty! 

  3. Create a new button on the Categories form with the following properties:

          Caption: Is Dirty?
          OnPush: =IsCategoriesFormDirty() 

    NOTE: If you use the macro method, the OnPush property should be set to:

          OnPush: IsCategoriesFormDirty 

  4. Open the Categories form in Form view.
  5. Click the Is Dirty? button. The result will be "Not Dirty!" since the record is not being edited.
  6. Modify any field in the record, and then click the Is Dirty? button. The result will be "Dirty!" since the record is being edited.
  7. From the Edit menu, choose Undo Record. Click the Is Dirty? button. The result will be "Not Dirty!"

Keywords : kbusage
Issue type : kbhowto
Technology :

Last Reviewed: November 4, 2000
© 2001 Microsoft Corporation. All rights reserved. Terms of Use.