Cancel save after BeforeUpdate validation failure (1 Viewer)

spikepl

spikepl

Eledittingent Beliped
Local time
Today, 16:07
Joined
Nov 3, 2010
Messages
6,142
Cancel Close after BeforeUpdate validation failure

I have been completely stumped by a common problem.

I have a record with a check box and a date. If the checkbox is checked, the date MUST be filled in. The form can be closed by the user clicking the CLose button, or the cross in the corner. An update can be triggered by a refresh-button which is not on the form for other reasons (not shown in my attached db).

I do a check in BeforeUpdate of the form, and cancel the update if my condition is not fulfilled. The BeforeUpdate can be triggered by user closing the form, so I also want to cancel the close. To this end, I have a grlobal variable, that gets set in the BeforeUpdate, to remember if my current state is Ok to save or not. If not, I cancel the unload. I get a plethora of error messages that do npot make sense ... No current record, Records cannot be saved at this time and otehrs ....I have spent many hours on this now, with bits of code form here and there. I tried to trap messages in the forms OnError, but they did not come from there either. I am mentally stuck, but need this solved.

I need the following:

If condition is not fulfilled, ie. checkbox checked and no date:
1. the user is not allowed to save the record
2. user must either provide a date or uncheck the checkbox ( a message to that effect will be displayed)
3. an Undo of the entire record is not OK, I have about 60 other fields that must be preserved
4. the checkbox should remain as it was, until the user changes its state, so no undo for the checkbox either

Don't bother with messages to the user - that I can deal with. The issue is to obtain the above functionality, without any additional messages from the system itself.

I have shaved my problem down to the attached DB, and the code is here:

Code: 
Option Compare Database

Dim OKtoSave As Boolean

Private Sub cmdClose_Click()
    On Error GoTo cmdClose_error
        DoCmd.Close , ""
cmdClose_exit:
    Exit Sub
cmdClose_error:
    MsgBox Err.Number & " " & Err.Description
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    OKtoSave = True
    If Me.MyCheckBox And IsNull(Me.MyDate) Then
        Cancel = True
        OKtoSave = False
    End If
End Sub

Private Sub Form_Load()
    OKtoSave = True 'temporary measure, for if I get errors the thing won't save next time
End Sub

Private Sub Form_Unload(Cancel As Integer)
    If Not OKtoSave Then
        Cancel = True
    End If
End Sub
The problem is simple and idiotic, but I just haven't found the right pieces of code. Can you point me to a working sample?

If you want to have a look at the attached 2007-db: just open frm1, tick the box, leave date blank, and play with closing the form.
 

Attachments

  • testdb.zip
    38.9 KB · Views: 127
Last edited:
gemma-the-husky

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 15:07
Joined
Sep 12, 2006
Messages
15,653
do you still find the flag is set erroneously when the form is closed, or is it the messages.

It's a normalisation problem in a microcosm, though. You are trying to keep two fields in step, when one is not independent of the other, and it's easy to get inconsistent updates in those cases.

In fact, you possibly don't even need the checkbox at all. The date itself is evidence that the check box is apprvoed (unless you can enter the date without the check box being ticked .......)

Anyway try this. This should work. Make the checkbox UNBOUND, and have the REAL yesno field on the form, but not visible. Now in the date's AFTERUPDATE event, just set the REAL flag, depending on whether there is a date entered or not.

You will need some code in the current event to set the unbound check box.
 
spikepl

spikepl

Eledittingent Beliped
Local time
Today, 16:07
Joined
Nov 3, 2010
Messages
6,142
I agree with you, but I have inherited the DB, and the customer's workflows are so ingrained, that this double concept is immutable.

AfterUpdate? I dont quite follow : how does this solve my problem canceling the update itself? And the date's afterupdate does not fire, if the user forgot to enter the date, which is the case supposed to trigger the cancel of the update.

As to your question: yes , my OKtoSave flag does not get reset properly, since I get the variety of errors. I have a nasty suspicion that the sequence of events, and the various messages, do make sense once I understand them fully, which is obviously a future event too .. :)

Update: I got too stuck into my problem. I have no problem canceling the save, but a problem when Closing the Form. If the users clicks CLose-button, or closes the form in some other way, I wish to prevent the update, AND remain on the form, until the condition for checkbox and date is fulfilled. Sorry about the misdirected title.
 
Last edited:
spikepl

spikepl

Eledittingent Beliped
Local time
Today, 16:07
Joined
Nov 3, 2010
Messages
6,142
Aiiiiii this is nasty: if I capture error 2169 on the forms OnError, and in the cmdClose click event, I can stop the close, but the thing does an undo on the entire record, so whatever else the user input in the remaining 50-odd fields is lost. Surely this cannot be?
 
spikepl

spikepl

Eledittingent Beliped
Local time
Today, 16:07
Joined
Nov 3, 2010
Messages
6,142
I have some working code now, than seemingly does what is required, at least for the DB attached previously

Code: 
Option Compare Database

Dim OKtoSave As Boolean

Private Sub cmdClose_Click()
  On Error GoTo cmdClose_error

        DoCmd.Close , ""

cmdClose_exit:
    Exit Sub
cmdClose_error:
  '  MsgBox Err.Number & " " & Err.Description
    'here we also get error 2169, if trying to close by clicking the button
    'end then canceling the close in the FormUnload
    Resume cmdClose_exit
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    OKtoSave = True
    If Me.chbMyCheckBox And IsNull(Me.txtMyDate) Then
        Cancel = True
        OKtoSave = False
    End If
End Sub

Private Sub Form_Current()

    OKtoSave = True 'temporary measure, for if I get errors the thing won't save next time

End Sub

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    'this one catches the Close from the cross in the corner
    Const NOSAVE_ON_CLOSE As Long = 2169 ' borrowed from http://www.access-programmers.co.uk/forums/showpost.php?p=1054513&postcount=28
    
    If Not OKtoSave Then
        If DataErr = NOSAVE_ON_CLOSE Then
            Response = acDataErrContinue
        End If
    End If
End Sub


Private Sub Form_Undo(Cancel As Integer)
    'this is necessary to preserve the user's input (the Cancel of close spawns an Undo)
    Cancel = True
End Sub

Private Sub Form_Unload(Cancel As Integer)
    If Not OKtoSave Then
        Cancel = True
    End If
End Sub
 
gemma-the-husky

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 15:07
Joined
Sep 12, 2006
Messages
15,653
spikepl said:
I agree with you, but I have inherited the DB, and the customer's workflows are so ingrained, that this double concept is immutable.

AfterUpdate? I dont quite follow : how does this solve my problem canceling the update itself? And the date's afterupdate does not fire, if the user forgot to enter the date, which is the case supposed to trigger the cancel of the update.

As to your question: yes , my OKtoSave flag does not get reset properly, since I get the variety of errors. I have a nasty suspicion that the sequence of events, and the various messages, do make sense once I understand them fully, which is obviously a future event too .. :)

Update: I got too stuck into my problem. I have no problem canceling the save, but a problem when Closing the Form. If the users clicks CLose-button, or closes the form in some other way, I wish to prevent the update, AND remain on the form, until the condition for checkbox and date is fulfilled. Sorry about the misdirected title.
Click to expand...



Right - they want to keep both - but what ther users tick doesn't HAVE to be the real bound value.

As I say, let them tick an unbound checkbox. When they enter the date - in the after-update for the date, you set the REAL checkbox to yes, in a little bit of code.

that way, if they dont enter the date, the real checkbox doesnt get set either.


---------
the only other thought is maybe to remove the close button on this form - so you can control the close behaviour more precisely. And give them a purpose built close button.

Alternatively, you can cancel the unload action, which stops the the form closing - but I think this occurs AFTER the record save stuff take place.
 
spikepl

spikepl

Eledittingent Beliped
Local time
Today, 16:07
Joined
Nov 3, 2010
Messages
6,142
#6 Thanks for your input - I guess it must have crossed with my #5.
 
You must log in or register to reply here.

Similar threads

oxicottin
Check/Uncheck boxes on subform based on main form
Replies
5
Views
294
pbaldy
pbaldy
A
Disallowing Edits Not Working
2
Replies
39
Views
514
Pat Hartman
P
mloucel
Solved Navigation Buttons on a form
Replies
3
Views
125
Gasman
Gasman
J
macro or function preventing saving date to a field.
Replies
9
Views
263
John Sh
J
M
Generic Form with "Save", "Cancel" buttons
2
Replies
38
Views
1,429
jdraw
jdraw

Users who are viewing this thread

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