How to Create a Prompt 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. It gives an option to the users to save or cancel the change. It will make your program looks professionally.
How It Works:
- Open the form showing the information before changing
- I make a change on Zip field, changing the zip code from 93250 to 93720.
- Click Add New or Close Form 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 93720 will be saved.
o If the answer is ‘No’ then it will undo the zip code 93720 and remain as 93250.
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 a form, you can learn from my How To: http://www.iaccessworld.com/how-to-create-form-for-beginner/.
Step 2. Update the Before Update Event Procedure
- Open the form Design View
- Add two buttons: Add New Record and Close Form
- Open the Form Property Sheet
- Click on Event Tab
- Select [Event Procedure] of Before Update
- Click on three dots (…) next to Event Procedure to open the VBA
Step 3. Enter VBA code under On Form Load Event Procedure
Private Sub Form_BeforeUpdate(Cancel As Integer) If Me.Dirty = True Then If MsgBox("Do you want to save the changes for this record?", _ vbYesNo + vbQuestion, "Save Changes?") = vbNo Then Me.Undo End If End If End Sub
Save VBA and open in form view. Now you can test it by changing some information on form then click on Add Record or Close Form as mentioned above.