Create Save button and a Notification to Save a Change

How to Create a Save button and a Notification to Save a Change

With the bound fields on form, Microsoft Access will automatically save any changes that we update on fields because the control source of data is linked to the field in table. Whether we close the form or move to a new record it will automatically save the changes. In this How To, I will show you a way of creating a notification to save the changes with a Save button. It gives an option to the users to save or cancel the change if the user forgot to click Save first. It will be no notification if the user click Save first and click Add Record or Close Form buttons. The Save button will be loaded as disabled and will be enabled if there is any change on form.

How It Works:

Open the form showing the information before changing. A Save button is disabled.

I make a change on Zip field, changing the zip code from 90000 to 93700 and state of CO to CA.

After the information is changed under the State and Zip fields, the Save button now is enabled to click to save new information.

If I click Save button, the state CA and zip 93700 will be saved without showing any message. The Save button now become disabled because new information is saved. If I click Close Form button, the form will close without asking to save the change because I just click Save button before clicking on Close Form button.

If I click Add New or Close Form button without clicking on Save button, the message will pop-up asking the users to save the changes or not.

o If the answer is ‘Yes’ the new zip code 93700 and state CA will be saved.

o If the answer is ‘No’ then it will undo the zip code to 90000 and state to CO.

How to Create It:

Step 1. Create a Customer Form

You will need a form to test the notification function. For this example, I will use the Customer form to test it. If you don’t know how to create the Access form, you can learn from my How To: http://www.iaccessworld.com/how-to-create-form-for-beginner/.

Step 2. Add Buttons

Open the form Design View
Add three buttons: Add Record, Save, and Close Form
Double click on Save button to open the Property Sheet
Select ‘No’ for Enabled under the Data tab

Name the Save button as cmdSave

Step 3. Enter VBA code under On Dirty Event Procedure

When form is loaded, form is considered ‘Not Dirty’ and the value of Me.Dirty is ‘Fault’. The Save button is also disabled when form is loaded because the Data Enabled property is set to ‘No’ per Step 2. If there is any keystroke and change in form then we want the Save button to be enable to click save. So we need to change the Enabled property = True when form is dirty under the Form Dirty Procedure.

Open the Form Property Sheet
Click on Event Tab
Select [Event Procedure] of On Dirty
Click on three dots (…) next to Event Procedure to open the VBA

VBA Code:

Private Sub Form_Dirty(Cancel As Integer)
    Me.cmdSave.Enabled = True
End Sub

Step 4. Set Private Boolean

In this method, we will need to set up a Private Boolean to track the status of form dirty or not. We need to set the Private Boolean word at the top under the Option Compare Database before any procedure.

Option Compare Database

Private Saved As Boolean

Step 5. Enter VBA code under On Click Event Procedure of Save button

Double click on the Save button to open the Property Sheet
Click on Event Tab
Select [Event Procedure] of On Click
Click on three dots (…) next to Event Procedure to open the VBA

VBA Code:

Private Sub cmdSave_Click()
   Saved = True
   DoCmd.RunCommand(acCmdSaveRecord)
   Me.cmdSave.Enabled = False
   Saved = False
End Sub

Explanation:

When the Save button is clicked, it will set the Saved Boolean to True, save the changes, and become disabled by setting the Enabled property of Save button to False again. At the end, it will save the Saved Boolean back to False as original like new loaded form.

Step 6. Enter VBA code under On Before Update Event Procedure

Open the Form Property Sheet
Click on Event Tab
Select [Event Procedure] of On Before Update
Click on three dots (…) next to Event Procedure to open the VBA

VBA Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Response As Integer
If Saved = False Then
    Response = MsgBox("Do you want to save the changes on this record?", vbYesNo, "Save Changes?")
    If Response = vbNo Then
       Me.Undo
    End If
    Me.cmdSave.Enabled = False
End If
End Sub

Explanation:

We will use the Before Update event procedure to track if there is any change on form. If the user click on Add Record or Close Form buttons without clicking on Save button first, the Save Changes message window will popup. It will ask the user to save the changes or not. If the answer is ‘Yes’ then it will save the changes. If the answer is ‘No’ then it will undo or cancel the changes. The Save button will become disabled again whether the answer is Yes or No.

Save VBA and open in form view. Now you can test it by changing some information on form then click on Save, Add Record, or Close Form as mentioned above.

Search This Blog

MS Access