exit form button and BeforeUpdate form event

[lambertsm via AccessMonster.com]

I have an exit form button where I just want to confirm with the user if they
want to save or not, if they don’t it closes the form, but if they do it
saves the record before closing. Things have worked great in other forms
with a simple BeforeUpdate event programmed on the form:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim intresponse As Integer

intresponse = msgbox("Save changes to this record?", vbYesNo, "Confirm Save")
If intresponse = vbNo Then
Cancel = True
End If

End Sub



But now I have a particular form where I want to do some error checking
before the record is saved so I added an if statement to the BeforeUpdate
event:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim intresponse As Integer

intresponse = msgbox("Save changes to this record?", vbYesNo, "Confirm Save")
If intresponse = vbNo Then
Cancel = True
Else
'Check the data for errors before saving
If (IsNull(Forms![ProductSKU].Active)) Then
' Warns the user of missing data.
Beep
msgbox "You need to assign an active or inactive status before
saving", vbExclamation, ""
Cancel = True
End If
End If

End Sub

It still works like a charm in that it doesn’t save if the user hasn’t filled
in the active status, but it also closes the form because, of course, they
clicked the exit form button. What I would like to do is give the user an
opportunity to fill in the active status before the form closes on them.
After all, the user did click that they wanted to save the record they just
failed the error check.

I thought about putting the error checking in the exit button but then that
would be annoying for the users who didn’t want to save the record as they
would have to fill in the active status just so they could not save.

Is there a way to get it to abort the code for the exit form button in the
BeforeUpdate code? Or is there a better way to accomplish what I need?

 

[Allen Browne]

If you want the exit to continue after you cancel Form_BeforeUpate, you need
to undo the form:
Me.Undo

By "exit button", I assume you mean a command button on the form. If you use
the Close action (in a macro) or method (in VBA code), Access silently loses
the record if there is some reason it cannot be saved:
http://allenbrowne.com/bug-01.html

The crucial thing would be to make your exit button force the save before
you try to close the form. You need to use an approach that generates an
error if the save fails, so you can trap it with an error handler, and not
execute the Close. This kind of thing:

Private cmdExit_Click()
On Error Goto Err_Handler
'Force the save
If Me.Dirty Then Me.Dirty = False
DoCmd.Close acForm, Me.Name

Exit_Handler:
Exit Sub

Err_Handler:
Select Case Err.Number
Case3314, 2101, 2115 'can't save errors
strMsg = "Record cannot be saved." & vbCrLf & _
"Fix the entry, or press Esc to undo."
MsgBox strMsg, vbExclamation, "Cannot exit"
Case Else
strMsg = "Error " & Err.Number & ": " & Err.Description
MsgBox strMsg, vbExclamation, "Cannot exit
End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String

If IsNull(Me.ProductSKU) Then
Cancel = True
strMsg = "Status missing." & vbCrLf & _
"Fix the entry, or press Esc to undo."
MsgBox strMsg, vbExclamation, "Invalid data"
Else
strMsg = "Save?"
If MsgBox(strMsg, vbOkCancel, "Confirm") <> vbOk Then
Cancel = True
End If
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have an exit form button where I just want to confirm with the user if
they
want to save or not, if they don’t it closes the form, but if they do it
saves the record before closing. Things have worked great in other forms
with a simple BeforeUpdate event programmed on the form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim intresponse As Integer
intresponse = msgbox("Save changes to this record?", vbYesNo, _
"Confirm Save")
If intresponse = vbNo Then
Cancel = True
End If
End Sub


But now I have a particular form where I want to do some error checking
before the record is saved so I added an if statement to the BeforeUpdate
event:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim intresponse As Integer

intresponse = msgbox("Save changes to this record?", vbYesNo, "Confirm
Save")
If intresponse = vbNo Then
Cancel = True
Else
'Check the data for errors before saving
If (IsNull(Forms![ProductSKU].Active)) Then
' Warns the user of missing data.
Beep
msgbox "You need to assign an active or inactive status before
saving", vbExclamation, ""
Cancel = True
End If
End If

End Sub

It still works like a charm in that it doesn’t save if the user hasn’t
filled
in the active status, but it also closes the form because, of course,
they
clicked the exit form button. What I would like to do is give the user an
opportunity to fill in the active status before the form closes on them.
After all, the user did click that they wanted to save the record they
just
failed the error check.

I thought about putting the error checking in the exit button but then
that
would be annoying for the users who didn’t want to save the record as they
would have to fill in the active status just so they could not save.

Is there a way to get it to abort the code for the exit form button in the
BeforeUpdate code? Or is there a better way to accomplish what I need?

 

[lambertsm via AccessMonster.com]

Well, now it won’t let you exit unless the status is filled in, fine if you
want to save but annoying if you don’t want to save as now you have to fill
in the status just so you can not save the record and leave. ?????? I
want to give the users the choice when they close the screen if they want to
save the record or not. But if they do want to save I need it to run error
checking that stops both the record save and the form from closing.

If you want the exit to continue after you cancel Form_BeforeUpate, you need
to undo the form:
Me.Undo

By "exit button", I assume you mean a command button on the form. If you use
the Close action (in a macro) or method (in VBA code), Access silently loses
the record if there is some reason it cannot be saved:
http://allenbrowne.com/bug-01.html

The crucial thing would be to make your exit button force the save before
you try to close the form. You need to use an approach that generates an
error if the save fails, so you can trap it with an error handler, and not
execute the Close. This kind of thing:

Private cmdExit_Click()
On Error Goto Err_Handler
'Force the save
If Me.Dirty Then Me.Dirty = False
DoCmd.Close acForm, Me.Name

Exit_Handler:
Exit Sub

Err_Handler:
Select Case Err.Number
Case3314, 2101, 2115 'can't save errors
strMsg = "Record cannot be saved." & vbCrLf & _
"Fix the entry, or press Esc to undo."
MsgBox strMsg, vbExclamation, "Cannot exit"
Case Else
strMsg = "Error " & Err.Number & ": " & Err.Description
MsgBox strMsg, vbExclamation, "Cannot exit
End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String

If IsNull(Me.ProductSKU) Then
Cancel = True
strMsg = "Status missing." & vbCrLf & _
"Fix the entry, or press Esc to undo."
MsgBox strMsg, vbExclamation, "Invalid data"
Else
strMsg = "Save?"
If MsgBox(strMsg, vbOkCancel, "Confirm") <> vbOk Then
Cancel = True
End If
End If
End Sub

I have an exit form button where I just want to confirm with the user if
they

[quoted text clipped - 53 lines]

Is there a way to get it to abort the code for the exit form button in the
BeforeUpdate code? Or is there a better way to accomplish what I need?

 

[lambertsm via AccessMonster.com]

OK - I think I have made this way to hard. I just accomplished what I wanted
by getting rid of the BeforeUpdate event and programming everything on the
exit command.

Well, now it won’t let you exit unless the status is filled in, fine if you
want to save but annoying if you don’t want to save as now you have to fill
in the status just so you can not save the record and leave. ?????? I
want to give the users the choice when they close the screen if they want to
save the record or not. But if they do want to save I need it to run error
checking that stops both the record save and the form from closing.

If you want the exit to continue after you cancel Form_BeforeUpate, you need
to undo the form:

[quoted text clipped - 52 lines]

 

[Allen Browne]

OK - I think I have made this way to hard. I just accomplished what I
wanted
by getting rid of the BeforeUpdate event and programming everything on the
exit command.

Not sure that's a good idea.

The test will not execute if the record gets saved by any other means than
your buttons (e.g. pressing Shift +Enter, tabbing past the last control on
the form, saving through the menu, closing Access, applying a filter,
changing the sort order, etc, etc.)

The MsgBox text I posted in the previous reply informs the use to press Esc
if they wish to discard the record without filling it out.

Alternativley, you can use a MsgBox with vbYesNoCancel, and handle the 3
states. In my view, that's less clear, but you can do it.