Not saving to database on closing a form

[Guest]

Hello,

If you enter data into a form, and then close that form, the data is saved
into the underlying table.

I would like to force the users to use the 'save record' button in order to
save the recird, and have all changes to the record cancelled if the user
exits the form in any other way.

Is there a way of doing this.

thx in advance

Baard

 

[John Vinson]

Hello,

If you enter data into a form, and then close that form, the data is saved
into the underlying table.

I would like to force the users to use the 'save record' button in order to
save the recird, and have all changes to the record cancelled if the user
exits the form in any other way.

Is there a way of doing this.

thx in advance

Baard

You need some VBA code to do this. One way is to put a global variable
in the Form's module by opening the code window; and right up at the
top, right after the default lines (which you should add if they
aren't there)

Option Explicit
Option Compare Database

put:

Dim bOKToClose As Boolean

This will be a yes/no variable indicating that the form can be closed.
Set it to FALSE in the form's Current event; set it to TRUE in the
Click event of the Save Record button; and check it in the form's
BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim iAns As Integer
If Not bOKToClose Then
iAns = MsgBox("Record not saved; click OK to return to form, " _
& "Cancel to exit without saving", vbOKCancel
Cancel = True
If iAns = vbCancel Then
DoCmd.Close acForm, Me.Name
End If
End If
End Sub


John W. Vinson[MVP]

 

[Guest]

Thank you for taking the time to to help me with my problem, and, though it
has brought me further along, there are still som issues.

When I get the message box saying I can press ok to return to the form, it
works fine (except that I also get the standard access message before I can
return to the form). However, when I press cancel, I get an error message,
and it doesn't seem to recognize the line 'DoCmd.close ac form, me.name'.

At first, I thought that I was supposed to swap the word 'Name' with the
name of the form, but that didn't work. I triad all sorts of other
variations, but I can't figure it out.

What am I doing wrong?

Baard

 

[John Vinson]

Thank you for taking the time to to help me with my problem, and, though it
has brought me further along, there are still som issues.

When I get the message box saying I can press ok to return to the form, it
works fine (except that I also get the standard access message before I can
return to the form). However, when I press cancel, I get an error message,
and it doesn't seem to recognize the line 'DoCmd.close ac form, me.name'.

At first, I thought that I was supposed to swap the word 'Name' with the
name of the form, but that didn't work. I triad all sorts of other
variations, but I can't figure it out.

What am I doing wrong?

If what you posted is actually the code that you entered, what you're
doing wrong is putting a space in the middle of the Access keyword
acForm.

DoCmd.Close acForm, Me.Name

means

Execute the Command

named "Close"

specifying that the type of object you're closing is an Access Form

whose Name property is the Name of the currently active form

You could also say

DoCmd.Close acForm, "frmMyForm"

if frmMyForm were the name of your form, but this code would not be
portable from form to form; Me.Name lets you write generic code which
can close any form.

John W. Vinson[MVP]