Preventing a record update when a form closes ...

[Jay]

Hi,

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
clicked.

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
way.

Thanks.


----------------------------------------------------------
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_AddRecordBtn_Click:
Exit Sub

Err_AddRecordBtn_Click:
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"
Me.Undo
Cancel = Not OkToAdd
End Sub

 

[pietlinden]

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
Me.Undo
MsgBox "Insert was cancelled", vbOKOnly + vbInformation
End If

End Sub

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

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).