Date field validation on an UNbound form

[rfoye]

I have the code that validates my field (see below), but I also want to delete the date that was entered so that the user has a blank field to start entering a complete date (instead of editing the previous incorrect date). Trying to set the value (e.g. txtDate=Null) is not allowed because the field value has not been committed yet.
txtDate.Undo seems to bypass the entire validation and actually commits the incorrect value. Is there any way to clear the field to start again?

Private Sub txtDate_BeforeUpdate(Cancel As Integer)
    Dim dtstart As Date
    If IsNull(txtDate) Then
        Cancel = True
        Exit Sub
    End If
    dtstart = txtDate
    If DatePart("W", dtstart, vbMonday) > 5 Or DLookup("holHolidate", "tblHolidays", _
        "[holHoliDate]= #" & CStr(dtstart) & "#") Or IsNull(txtDate) Then

        MsgBox "The date you entered is not a business day." & _
            vbCrLf & "Please enter a new date.", vbOKOnly, "Not a business day"
        Cancel = True
    End If

-------------------
Rob Foye
Database Management
Regions Bank

 

[PHV]

txtDate.Undo seems to bypass the entire validation
Really ?
even like this ?
...
Me!txtDate.Undo
Cancel = True
Exit Sub
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rfoye]

Yes, it is committing the date.
I am entering "06/25/06" in the date field.
With just the Cancel=True, the result has the cursor blinking in the field with the same entry value. If I leave this value, no matter how many times I try to leave the field, the validation procedure will run.

If I include me!txtDate.Undo (either before OR after the Cancel), the date has been changed to the full "06/25/2006" and the entire field is selected (but not cleared). If I now press tab to leave the field, the validation procedure (or, more accurately, the BeforeUpdate event) is not triggered.

I'm totally mystified.

-------------------
Rob Foye
Database Management
Regions Bank

 

[rfoye]

I used this routine in the AfterUpdate event instead, which allowed me to set txtDate=Null, although setting the focus back onto txtDate took some odd workarounds.

-------------------
Rob Foye
Database Management
Regions Bank

 

[TheAceMan1]

How are ya rfoye . . .

. . . and if you use [blue]Me.Undo[/blue] instead?

See Ya! . . . . . .