Custom cancel button not quite working (1 Viewer)

B

Bloodlvst

Registered User.
Local time
Today, 09:39
Joined
Nov 27, 2018
Messages
32
I have some VBA I've written for my form, which prevents the record from saving until the user clicks the "Save" button on the form. If they just close the form, it will prompt them if they want to save with a vbYesNoCancel.


The cancel prompt DOES work...sort of. The problem is that if a user clicks "cancel" on the message box, they then get the error shown in the attached screenshot. Is there a way around this, or is this just one of those Access caveats?

Code: 
Option Compare Database
Private UserSave As Boolean

Private Sub Cancel_Btn_Click()
    UserSave = True
    If Me.Dirty = True Then
        Me.Undo
    End If
    DoCmd.Close acForm, Me.Name, acSaveNo
End Sub

Private Sub Form_AfterInsert()
    CurrentDb.Execute "UPDATE Customers SET Customers.CustomerNumber = 'CL-" & Format([CustomerID], "000000") & _
    "' WHERE [CustomerID]= " & Me.CustomerID, dbFailOnError
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Not UserSave Then
        If Me.Dirty = True Then
            Dim UserResponse As Integer
            UserResponse = MsgBox("Save before closing?", vbQuestion + vbYesNoCancel, "Test")
            Select Case UserResponse
                Case vbYes
                Case vbNo
                    Me.Undo
                Case Else
                    Cancel = True
                End Select
        End If
    End If
End Sub

Private Sub Save_Btn_Click()
    UserSave = True
    If Me.Dirty = True Then
        Me.Dirty = False
    End If
    DoCmd.Close acForm, Me.Name, acSaveYes
End Sub
 

Attachments

  • error.png
    error.png
    5.5 KB · Views: 79
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 05:39
Joined
Oct 29, 2018
Messages
21,467
Hi,

I think you're getting the error because your code is trying to close the form (maybe). If so, you might need to pass a value to it to see if you could close the form. For example, if the user clicks on the Cancel button from the MsgBox, then perhaps pass a variable to skip the DoCmd.Close line.
 
Last edited:
J

JHB

Have been here a while
Local time
Today, 14:39
Joined
Jun 17, 2012
Messages
7,732
Try moving the code from "Before Update" to the form's "On Unload" event.
 
M

Micron

AWF VIP
Local time
Today, 08:39
Joined
Oct 20, 2018
Messages
3,478
JHB said:
Try moving the code from "Before Update" to the form's "On Unload" event.
Click to expand...
Rats. What I was going to say because I dealt with this recently. Can add that when the validation code warrants and you cancel the Unload (Cancel = True), there will be an error raised; I think 2501. Just trap it.
 
B

Bloodlvst

Registered User.
Local time
Today, 09:39
Joined
Nov 27, 2018
Messages
32
theDBguy said:
Hi,


I think you're getting the error because your code is trying to close the form (maybe). If so, you might need to pass a value to it to see if you could close the form. For example, if the user clicks on the Cancel button from the MsgBox, then perhaps pass a variable to skip the DoCmd.Close line.
Click to expand...

The code isn't what's trying to close the form. The prompt appears when you try to close the form. Sorry if I'm misunderstanding something?

Micron said:
Rats. What I was going to say because I dealt with this recently. Can add that when the validation code warrants and you cancel the Unload (Cancel = True), there will be an error raised; I think 2501. Just trap it.
Click to expand...

Wouldn't this cause the update to occur? Or does "On Unload" occur prior to the "Before Update" event? And trapping the event, I'm a bit noob at VBA, but I'm assuming I'd need to capture the error code and create and if statement to tell access to ignore the error code?
 
jdraw

jdraw

Super Moderator
Staff member
Local time
Today, 08:39
Joined
Jan 23, 2006
Messages
15,379
Bloodlvst,

Can you explain why you want an explicit Save button? I'd like to see the requirement for it.
In most cases the form BeforeUpdate event is the last spot to check for "valid values". Then if all is OK, the record is saved when moving off that record. If all is not OK, then undo any partial updates/edits.

More info here that may be useful.
 
Last edited:
M

Micron

AWF VIP
Local time
Today, 08:39
Joined
Oct 20, 2018
Messages
3,478
The buttton click code is trying to close the form.
acSave isn't required as it applies to the saving of form changes in design view. Unsaved record changes will automatically save if there's no violations.


If you initiate form close event, unload occurs before it. If you cancel the unload, execution will revert back to the close event and error 2501 is raised. There could be other events in use that can occur in between but you're not using any of them. Also, I don't get why you'd have a cancel option on an update event since closing the form isn't part of the event code. It's "do you want to save" not "do you want to save before closing". If it were a close option, then OK. Don't forget that this event will run every time you do anything to update the form, including just moving off the record. If a single record form, you may never notice a problem. If it were a datasheet or continuous, I think you would.

A save button isn't unusual IMHO, but the control box should be removed so that user can't close the form without using a button, unless they know how to right click and you don't prevent that as well.

Wouldn't this cause the update to occur?
Click to expand...
It should not if you are cancelling the update beforehand. Ignoring that for the moment, a simple example of what I was suggesting is for when the Unload event has been canceled. The error will be raised in the close event.

Code: 
Private Sub cmdClose_Click()
On Error GoTo errHandler

DoCmd.Close
Exit Sub
errHandler:
If Err.Number = 2501 Then Resume Next
End Sub
 
B

Bloodlvst

Registered User.
Local time
Today, 09:39
Joined
Nov 27, 2018
Messages
32
jdraw said:
Bloodlvst,

Can you explain why you want an explicit Save button? I'd like to see the requirement for it.
In most cases the form BeforeUpdate event is the last spot to check for "valid values". Then if all is OK, the record is saved when moving off that record. If all is not OK, then undo any partial updates/edits.

More info here that may be useful.
Click to expand...

The people using this wish to have more of a browser-like experience. So I wanted to prevent automatically saving the record upon closing the window (the form opens in a dialog/modal) and instead they can either explicitly save or cancel the entry/modification, or if they close they're prompted to save/not save/cancel(cancel in this case meaning "wait I'm not done yet, take me back")


Micron said:
The buttton click code is trying to close the form.
acSave isn't required as it applies to the saving of form changes in design view.
Click to expand...
This is good to know. For my comment, I misunderstood and thought we were talking about the cancel button click code.
Micron said:
Unsaved record changes will automatically save if there's no violations.

If you initiate form close event, unload occurs before it. If you cancel the unload, execution will revert back to the close event and error 2501 is raised. There could be other events in use that can occur in between but you're not using any of them. Also, I don't get why you'd have a cancel option on an update event since closing the form isn't part of the event code. It's "do you want to save" not "do you want to save before closing". If it were a close option, then OK. Don't forget that this event will run every time you do anything to update the form, including just moving off the record. If a single record form, you may never notice a problem. If it were a datasheet or continuous, I think you would.
Click to expand...
Okay, I think I understand what you're saying here. Essentially for what I'm trying to accomplish I'm using the entirely wrong event. These are single record forms for the record, but thank you for the datashet/continuous info, I wasn't aware of that so good to know for the future.

Micron said:
A save button isn't unusual IMHO, but the control box should be removed so that user can't close the form without using a button, unless they know how to right click and you don't prevent that as well.

It should not if you are cancelling the update beforehand. Ignoring that for the moment, a simple example of what I was suggesting is for when the Unload event has been canceled. The error will be raised in the close event.
Click to expand...
What control box are you referring to here?
 
M

Micron

AWF VIP
Local time
Today, 08:39
Joined
Oct 20, 2018
Messages
3,478
It is one of the options on format tab of property sheet in form design view. Set it to NO and the minimize, restore and close buttons disappear. By default, you can still close form with right click context menu though.
 
You must log in or register to reply here.

Similar threads

mloucel
Solved Value changed after Function
Replies
9
Views
169
mloucel
mloucel
mike60smart
Error 2501
2
Replies
29
Views
686
mike60smart
mike60smart
C
Solved FAYT Listbox crashes Access
Replies
10
Views
301
MajP
MajP
N
Solved Error Handler always triggering
Replies
14
Views
748
nashaz
N
C
Adding a new record and getting ID
Replies
14
Views
1,114
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom