Preventing auto save on a bound form Microsoft Access

This article is about to manually save the record in Microsoft Access using VBA. A bound control is one that is bound to a field in a table or to a function. Bound controls are easy to identify as they will contain the field names from the table in design view. Bound elements are linked directly to the tables, and when you change any data within forms, your changes are immediately written to the tables. This can often lead to user questioning why "when I close a form does it save the changes?" This is because this is bound behavior of form with table. To prevent any updates you must use procedures such as Before Update to cancel if necessary. In this article we are stop the updating automatically.

To stop bound forms update automatically we have to create a table as shown in Fig 1.1.

How to Stop Bound Forms from Updating Automatically. Fig-1.1


Then we have to create a form using table by selecting Form wizard from the ribbon. This will enable the automatic update option. Now add button using enabling control wizard and in command button wizard select the save record option. Now open the Form in Form view. You will see if we make changes in the text-box fields the record will automatically save without any prior notification. Now for stopping automatic update we have to add one more unbound button as shown in Fig 1.2. And write code for stop automatic update as shown end if this article.

How to Stop Bound Forms from Updating Automatically. Fig-1.2


After completion of coding we have to test it. For this change the value from the text box and click on save record button. As you click on that a message will pop up as shown in Fig 1.3. This is the confirmation message. If you will close it the record not altered and if after press ok and then pressing on manual save button, then only the record will save.

How to Stop Bound Forms from Updating Automatically. Fig-1.3


You can check also the table the record will update as shown in Fig 1.4.

How to Stop Bound Forms from Updating Automatically. Fig-1.4


VBA Code

Option Compare Database
Option Explicit
Private btnflag As Boolean
Private Sub Command10_Click()
btnflag = True
Call DoCmd.RunCommand (acCmdSaveRecord)
btnflag = False
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim MSSG As String
If Not btnflag Then
Cancel = True
MSSG = " Please use the Save button to update the changes," & "or Escape to reset them."
Call MsgBox(Prompt:=MSSG, Title:="Before_Update")
End If
End Sub


It is advised that the information provided in the article should not be used for any kind formal or production programming purposes as content of the article may not be complete or well tested. ERP Makers will not be responsible for any kind of damage (monetary, time, personal or any other type) which may take place because of the usage of the content in the article.