Save Record in Access VBA

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.

Click and drag in your form to create the button.

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

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.

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

DoCmd.RunCommand acCmdSaveRecord

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
  Else
    Me.Undo
  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.

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.