Preventing a record update when a form closes ...




I'm new to access and have been trying to understand the event model for
forms in access and am having trouble understanding some of the basics.

For example, I created a simple database just for testing purposes, it has
one table. This table has just two fields, an autonumber field used as the
primary key, and a text data field.

Then I created a form that was bound to the table. The form has a text box
control bound to the data field in the table and a command button for
adding a new record to the table.

The intention was to create a form where the only way a new record would be
entered into the table was if the "Add Record" button on the form was

Here is the code that I have to try and accomplish this. Basically I am
using a variable called OkToAdd that is checked in the form before_update
procedure to determine whether or not to cancel the event. When the form
is loaded OkToAdd is set to false, and is only set to true in "Add Record"
buttons on_click event handler.

This seems to work in that it does prevent the new record from being saved
in the table, but when the form is closed by hitting the close button on
the forms title bar, I get a warning message from Access saying :

"You can't save this record at this time. Microsoft access may have
encountered an error while trying to save a record. If you close this
object now, the data changes you made will be lost. Do you want to close
the database object anyway"

If I say Yes then the form closes and the record is not added, but how do I
prevent this message from popping up.

Is this an appropriate approach to forcing the user to click the "Add
Record" button before any table additions are made, or is their a better


Option Compare Database
Option Explicit
Dim OkToAdd As Boolean

Private Sub AddRecordBtn_Click()
On Error GoTo Err_AddRecordBtn_Click

MsgBox "entering add record button click event handler"
OkToAdd = True
DoCmd.GoToRecord , , acNewRec

Exit Sub

MsgBox Err.Description
Resume Exit_AddRecordBtn_Click

End Sub

Private Sub Form_AfterUpdate()
MsgBox "entering form after update event handler"
OkToAdd = False 'setting up for the next possible record
End Sub


Private Sub Form_BeforeUpdate(Cancel As Integer)
MsgBox "entering form before update event handler"
Cancel = Not OkToAdd
End Sub


Not sure why your code didn't work. I tried this and it worked...

Private Sub Form_BeforeUpdate(Cancel As Integer)
If MsgBox("Save this record?", vbYesNo) = vbNo Then
Cancel = True
MsgBox "Insert was cancelled", vbOKOnly + vbInformation
End If

End Sub

I guess if you define blnOKToAdd as a form-level variable, it should be

But you probably need to handle the insert of the record that would be
caused by the Form_close event. you might need to disable some of the
options on your form - like displaying the close button so the user has
no choice but to use your button (so your code always runs).

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question