Save Record in Access VBA

Written by

Mel Jenkins

Reviewed by

Steve Rynearson

Last updated on July 9, 2022

This article will explain how to save a record in Access VBA.

When entering a record into a table or form in Access, the record is automatically saved once the user moves off that particular record. The only way a record would not be saved is if the user pressed the Esc key on the keyboard in which case the data would be lost.

However, when creating a user friendly form in Access, it is nice to have a save button on the screen to give the user the choice to either save or discard the record. Occasionally when a user is entering data using a user form, data may be lost. A save button is one way to make sure that the data is saved.

Creating a Save Button

Firstly, let us put a new button on our form, and give it the caption “Save Record”

In design view in your form, in the Ribbon, select Form Design > Button.

access save button

Click and drag in your form to create the button.

access save button create

In the Properties of the Save button, click on the All tab and then change the Name and Caption properties of the button.

access save button properties

Coding the Save Button

In the Properties of the Save button, click on the Event tab, and then, in the On Click Event, select Event Procedure from the drop down list.

access save button event

The cmdSave_Click event will automatically be created. Type the following line into this procedure:

DoCmd.RunCommand acCmdSaveRecord

access save button click event

This will automatically run the save command. To make the code more user friendly, we can add an if statement.

Private Sub cmdSave_Click()
  If MsgBox("Do you wish to save the record?", vbYesNo + vbQuestion, "SAVE RECORD?") = vbYes Then
    DoCmd.RunCommand acCmdSaveRecord
  End If
End Sub

When the user now clicks the save button in the form, a message box will ask them if they are sure they wish to save the record.

access save button msgbox

If they click Yes then the record will be saved, but if they click No, then the Undo method will be sent to the record and the record will not be saved.



VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)

Free Download

Return to VBA Code Examples