Undo, won't Undo - Do I understand right?

[lightray]

Hi, I have found the answer to most of my problem, thanks to previous posts.
I am using the Undo command on a Date field. However it seems that my code is maybe not staying on the event to action the undo.

After it runs the Focus goes to the next field Me.LeaveDays and the value is automatically entered, unless I comment back in the Goto's in which case the value will be zero.

I want to be able to Undo the EndDate (and preferable the StartDate, and Setfocus to StartDate) EndDate will do if not possible.

Don't want to Undo the whole form, although that could be a last resort.

My code is in the before_update event, image posted (ignore faded fields, not visible/needed in final solution)

Private Sub EndDate_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_EndDate_BeforeUpdate

    Dim intLeaveLeftAL As Integer
    Dim intLeaveLeftSL As Integer
    Dim intLeaveDays As Integer
    Dim dtNullDate As Date
    Dim varNullField As Variant
    
    intLeaveLeftAL = Nz(Forms!frmAddLeaveRecords.fsubLeaveAgTots.Form![DailyALInc] - (Forms!frmAddLeaveRecords.fsubLeaveAgTots.Form![ALDays] - Forms!frmAddLeaveRecords.fsubLeaveAgTots.Form![ACDays]), 0)
    intLeaveLeftSL = Nz(10 - Forms!frmAddLeaveRecords.fsubLeaveAgTots.Form![SLDays], 0)
    
'   Use the WorkingDays function to calc the number of Leave Days
'   set LeaveDays and requery the form
    intLeaveDays = WorkingDays(Me.StartDate, Me.EndDate)
    
    If Me.LCode = "LC41" Then   ' Sick Leave
    
        Select Case intLeaveDays
            Case Is > intLeaveLeftSL
            MsgBox "Employee does not have enough Leave" & vbCrLf & "to take as Sick Leave.", vbCritical + vbOKOnly + vbDefaultButton1, "Entry in Error"
            varErrorCondition = True
            Me.EndDate.Undo
            GoTo Exit_EndDate_BeforeUpdate
            Case Else:
        End Select
        
    ElseIf Me.LCode = "LC11" Or Me.LCode = "LC13c" Then ' Annual or C/Fwd Leave
    
        Select Case intLeaveDays
            Case Is > intLeaveLeftAL
            MsgBox "Employee does not have enough Leave" & vbCrLf & "to take as Annual Leave.", vbCritical + vbOKOnly + vbDefaultButton1, "Entry in Error"
            varErrorCondition = True
            Me.EndDate.Undo
            GoTo Exit_EndDate_BeforeUpdate
            Case Else:
        End Select
     End If
    
    Me.LeaveDays = intLeaveDays
    Me.LeaveDays.Requery
    varErrorCondition = False
    
Exit_EndDate_BeforeUpdate:
    Exit Sub
 
Err_EndDate_BeforeUpdate:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_EndDate_BeforeUpdate

End Sub

The ifs and cases work fine, just like to send the User back a few steps
appreciated

 

[MStef]

Tray this:

Me.EndDate = ""

instead of Me.EndDate.Undo
If you don't want to save this record put next:

Cancel = True

 

[lightray]

I don't think that was a good solution: Me.EndDate = "" gives me the dreaded;
runtime error #-2147352567 (80020009) "the macro or function set to the beforeupdate or validation rule property is preventing [mdb] from saving the data in the field.
or was that what you were expecting?
lot of posts about this error, learn't that you can't put text into a datefield

 

[gemma-the-husky]

try

enddate = enddate.oldvalue to reset it.

oldvalue is the orignal value before the edit started, which is how the undo works!

you might not get away with this in a before update though


thinking again, the best place to put this testing is probably in the AFTER UPDATE event
then you can reset the date to whatever you want, and move the focus afterwards.

----------------------------------------------------------------
normally the beforeupdate is to test the validity of the entered value. if its not valid, then you set

cancel=vbcancel
exit sub

in the before update, which then refuses to accept the data as entered, and prompts the user again.

 

[Rich]

Use the BeforeUpdate event of the Form, not a control,

 

[lightray]

Thanks Guys & gals, I'll sort it in the morning ZZZzzzzz %)

 

[lightray]

Gemma: Thanks, your suggestion has come with measured success in the After event. I have wound up with the dreaded runtime error #-2147352567 (80020009). mainly because I decided to set the startdate to oldvalue as well,(and setfocus there, from Exit event), and startdate is a required field. I made it a required field to prevent the user opting out mid-entry and saving the record. If I get the issue sorted with your technique I probably don't need it to be required? or is it safer to trap for the expected error?
The text message of the erro ris "the field startdate cannot contain a null value because the Required property is set to True. Enter a value in this field"
However it stops on the enddate field (also Required) and doesn't blank them

any further ideas

Rich: not sure what you are getting at

 

[Rich]

The before update event of the Form is the only sure fire way of enforcing validation of multiple controls, in any event if a user simply tabs through controls your before update or even after update events will not fire.
Use the BeforeUpdate event of the Form and Cancel=True if you want to prevent the update, the AfterUpdate event is too late to trap any entry errors

 

[gemma-the-husky]

rich is right, but it depends in sopme measure on what you are trying to do

the BEFOREUPDATE event of A CONTROL is normally used to test the validity of the input for that control. The AFTERUPDATE enables you to tidy up other things afterwards

the FORM itself has BEFOREUPDATE and AFTERUPDATE events which occur before the entire record is saved to the table etc, and as Rich says, if you want to validate a lot of stuff, this is the best place to put it.

 

[lightray]

Thanks again, Rich, Gemma. moved back to the before, (before I moved it to the After) ...
it's settling in but I have a few other scrub fires to sort! Will post final solution when all done. (one each) lightray