databasedev.co.uk - database solutions and downloads for microsoft access

Microsoft Access VBA

Prompt For Saving a Record

Microsoft Access automatically saves any changes we make to a record, whether we close the form or move to a new record.

I will show you a way of prompting a user to save the changes.

Here is my form before I make any changes to the record.

Microsoft Access Form

Now I add the word ‘Test’ into the ‘Last Name’ field.

I then move to the next record and get the message informing me that the record has changed and do I want to save it.

If I answer ‘Yes’ the changes will be saved.

If I answer ‘No’ my changes will not be saved.

In order to do this I added a bit of VBA code.

Go into form design and bring up the form properties.

Click on the top part of the form:

Form Properties

Go up to the ‘View’ menu and select ‘Properties’:

We want to look at the event properties, specifically the ‘Before Update’ event:

Before_Update Event Procedure

Click the small button with the dots, the ellipsis (...), to bring up the code window:

You will see the code procedure for the ‘Before Update’ event:

Private Sub Form_BeforeUpdate(Cancel As Integer)


End Sub

Add the following code between the Private Sub Form and the End Sub:

   'If the form data has changed a message is shown asking if
   'the changes should be saved. If the answer is no then
   'the changes are undone
 
   On Error GoTo BeforeUpdate_Error
  
   If Me.Dirty Then
    'if record has been changed the dirty property
    ' is set to true Display message to save the record
      If MsgBox("The record has changed - do you want to save it?", _
      vbYesNo + vbQuestion, "Save Changes") = vbNo Then
         Me.Undo
      End If
   End If

BeforeUpdate_Exit:
   Exit Sub

BeforeUpdate_Error:
   MsgBox Err.Description
   Resume BeforeUpdate_Exit

You should end up with:

Private Sub Form_BeforeUpdate(Cancel As Integer)

   'If the form data has changed a message is shown asking if
   'the changes should be saved. If the answer is no then
   'the changes are undone
 
   On Error GoTo BeforeUpdate_Error
  
   If Me.Dirty Then
    'if record has been changed the dirty property
    ' is set to true Display message to save the record
      If MsgBox("The record has changed - do you want to save it?", _
      vbYesNo + vbQuestion, "Save Changes") = vbNo Then
         Me.Undo
      End If
   End If

BeforeUpdate_Exit:
   Exit Sub

BeforeUpdate_Error:
   MsgBox Err.Description
   Resume BeforeUpdate_Exit

End Sub

Now save the code and close the window.

Run the form.

Enter something in your form fields and try moving to another record or even closing the form.

You should get the message asking if you want to save changes.

Summary

Although having Microsoft Access automatically save our changes is nice, sometimes we may wish to have greater control on this.

By using the ‘Me.Dirty’ property in code we can check for changes and control if they get saved.

Access Database Systems - Solutions for your business

http://access-databases.com

We specialise in a number of Microsoft Access database development areas including human resources, ticket helpdesk software and also invoicing. We are continually looking at new markets for Microsoft Access databases and are open to suggestions.Our Microsoft Access database applications have helped hundreds of companies and individuals worldwide. We also offer you the chance to learn Microsoft Access with a fantastic Microsoft Access training course.

© 2009 - Paul Barnett.

All Rights Reserved.

No part of this publication may be reprinted or reproduced without permission.