Before Update/Close (1 Viewer)

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 01:28
Joined
Feb 5, 2019
Messages
292
Hello forums,

I have the below code in my form BeforeUpdate event.

If Me.Dirty = True Then
Select Case MsgBox("Do you want to save changes to this record?", vbYesNoCancel, "Save Changes")
Case vbYes
DoCmd.RunCommand acCmdSaveRecord
Case vbNo
Screen.ActiveForm.Undo
Case vbCancel
DoCmd.CancelEvent
End Select
End If

So the user clicks close and then this event fires. Is there a way that if they click vbCancel that the DoCmd.Close command from my close button does not fire?

I know I can use the above code as part of the close button, but I wondered if there is a way to do it like this.

~Matt
 

vba_php

Forum Troll
Local time
Yesterday, 20:28
Joined
Oct 6, 2019
Messages
2,884
assuming ur code actually works as is, and it is what ur using behind ur close button, then prolly this:
Code:
If Me.Dirty = True Then
Select Case MsgBox("Do you want to save changes to this record?", vbYesNoCancel, "Save Changes")
Case vbYes
DoCmd.RunCommand acCmdSaveRecord            
docmd.close 'HERE
Case Screen.ActiveForm.Undo
docmd.close 'HERE
Case vbCancel
DoCmd.CancelEvent
'DO NOT PUT docmd.close code here!  put nothing here other than what you have already
End Select
End If
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:28
Joined
Oct 29, 2018
Messages
21,358
Hi Matt. One possible approach is to use a Form level variable. I call mine Ok2Close, and it's a Boolean. So, when the form opens, you can set the variable to True. But when the user clicks Cancel on the prompt, you can set it to False. Then, in your Close button code, you can check the variable. If False, don't close the form. However, you'll need to make sure it's reset to True somewhere. For example, when the user clicks Yes in your prompt.
 

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 01:28
Joined
Feb 5, 2019
Messages
292
assuming ur code actually works as is, and it is what ur using behind ur close button, then prolly this:
Code:
If Me.Dirty = True Then
Select Case MsgBox("Do you want to save changes to this record?", vbYesNoCancel, "Save Changes")
Case vbYes
DoCmd.RunCommand acCmdSaveRecord            
docmd.close 'HERE
Case Screen.ActiveForm.Undo
docmd.close 'HERE
Case vbCancel
DoCmd.CancelEvent
'DO NOT PUT docmd.close code here!  put nothing here other than what you have already
End Select
End If

This is how I had it before, the Me.Dirty was part of the close button, not the BeforeUpdate event. I was trying to steer away from this as when the forms are closed they run other events that I didn't want duplicate code for behind each vbOption.
 

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 01:28
Joined
Feb 5, 2019
Messages
292
Hi Matt. One possible approach is to use a Form level variable. I call mine Ok2Close, and it's a Boolean. So, when the form opens, you can set the variable to True. But when the user clicks Cancel on the prompt, you can set it to False. Then, in your Close button code, you can check the variable. If False, don't close the form. However, you'll need to make sure it's reset to True somewhere. For example, when the user clicks Yes in your prompt.

That sounds like what I am after. I shall give it a try when back at work tomorrow.
 

missinglinq

AWF VIP
Local time
Yesterday, 21:28
Joined
Jun 20, 2003
Messages
6,423
BTW...the If Me.Dirty = True Then...End If is useless in the Form_BeforeUpdate event. If the Form isn't Dirty, the Form_BeforeUpdate event will not fire!

Linq ;0)>
 

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 01:28
Joined
Feb 5, 2019
Messages
292
Thank you all for your help. In the end I moved the Me.Dirty code onto the close button and my other events onto the Form_Close event and this is working exactly as I needed.

~Matt
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:28
Joined
Oct 29, 2018
Messages
21,358
Thank you all for your help. In the end I moved the Me.Dirty code onto the close button and my other events onto the Form_Close event and this is working exactly as I needed.

~Matt
Hi Matt. Glad to hear you got it sorted out. Did you take out all code from the BeforeUpdate event? If so, what happens if the users moves to a different record? Just checking...
 

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 01:28
Joined
Feb 5, 2019
Messages
292
Hi Matt. Glad to hear you got it sorted out. Did you take out all code from the BeforeUpdate event? If so, what happens if the users moves to a different record? Just checking...

I removed all the code from the BeforeUpdate event. My forms do not allow movement to next or previous records. They have only 2 options when opening a form. Add or Edit. If they are on frmCustomerList they can double click the list box which will open frmCustomerOverview showing that customer data only. If they edit any fields they are now given the Save Changes message, but the only way to leave that form is the Close button and there is no way to change records without closing it and then opening a new customer record from the customer list. The database I have designed has grown over the 15 years I have been building it and now has 54 forms, 159 queries and 65 tables. Everything I have learned, I have done so in forums like these so I appreciate all the help I get from you all.

~Matt
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:28
Joined
Oct 29, 2018
Messages
21,358
Hi Matt. Congratulations! Continued success with your project.
 

Users who are viewing this thread

Top Bottom