ACC1x: How To Determine If a Record Is Being Edited

ID: Q106298

The information in this article applies to:

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:

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

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 
```

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!&lt;FormName&gt;)) 
```
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:

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
      -----------------------------------------
      Categories
                     TestIsDirty()    StopMacro 
```

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!"
         Else
            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!

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 
```

Open the Categories form in Form view.

Click the Is Dirty? button. The result will be "Not Dirty!" since the record is not being edited.

Modify any field in the record, and then click the Is Dirty? button. The result will be "Dirty!" since the record is being edited.

From the Edit menu, choose Undo Record. Click the Is Dirty? button. The result will be "Not Dirty!"

Keywords : kbusage FmsProp
Version : 1.0 1.1
Platform : WINDOWS
Issue type : kbhowto