I'm having a terrible time trying to accomplish what I thought would be extremely easy.
I have three date fields on a form (DischargeDate, ReceivedDate, and AuditedDate) and I'm trying to validate the AuditedDate and ReceivedDate fields compared to the DischargeDate field. Basically, any date entered into the AuditedDate or ReceivedDate must occur after the DischargeDate. Additionally, the AuditedDate has to occur after the ReceivedDate.
Currently, when the user enters a date into the AuditedDate field, if the date occurred prior to the DischargeDate an error message pops up. I know how to write the code to cancel the BeforeUpdate event for the AuditedDate field and that I could use Me!AuditedDate.Undo to revert back to the last acceptable date entered. However, I don't want to do this.
What I'd really like to do is clear the field completely, but if I try to set the field equal to Null or Empty, I get an error message (I think it's 2115, although it's alternated with a Run-Time error).
Is there any way to make a date field blank if it doesn't match the conditions I've setup?
The following gives me the error message, but should help illustrate what I'm trying to accomplish:
Expand|Select|Wrap|Line Numbers
- Private Sub AuditedDate_BeforeUpdate(Cancel As Integer)
- If (Me!AuditedDate < Me!DischargeDate) then
- MsgBox "Invalid Date", vbExclamation + vbOkOnly, "Error"
- Cancel = True
- Me!AuditedDate = Null
- Me!AuditedDate.SetFocus
- End If