Clear a date field

[beacon]

Hi everybody,

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:

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

[NeoPa]

You're trying to update a control - from within the routine which fires on the update event of that control.

PS. For further discussion, it would be helpful to have the exact error details, including number, message & line it occurs on.

[beacon]

If line #6 says:

Me!AuditedDate = Null

then it gets highlighted and the error says:

Run-time error '-2147352567 (80020009)'
The macro or function set to the BeforeUpdate or ValidateRule property for this field is preventing Microsoft Office Access from saving the data in the field.


If I change line #6 to read:

Me!AuditedDate.Value = Null

then the error says:

Run-time error '2115':
The macro or function set to the BeforeUpdate or ValidateRule property for this field is preventing Microsoft Office Access from saving the data in the field.

[NeoPa]

Thanks for that. It makes the situation much clearer.

You appreciate now (I suspect) that the earlier point I made is actually your problem?

[beacon]

I'm not quite sure I understand and I'm sorry for that NeoPa.

Are you saying that because I'm trying to update the AuditedDate control from within the BeforeUpdate event, that this won't work?

If so, would a better solution be to allow the user to enter all of these dates and then validate when they try to submit (from the OnClick event of a command button)?

[NeoPa]

I'm not quite sure I understand and I'm sorry for that NeoPa.

Not a problem at all. Admitting we don't understand is half way to understanding.

Are you saying that because I'm trying to update the AuditedDate control from within the BeforeUpdate event, that this won't work?

Yes. The system is still waiting for the response from the previous attempt to change the value. Triggering the procedure again in that state COULD be made to work if the code were re-entrant (from the perspective of Access rather than your code). I'm guessing it wasn't designed that way though - it would certainly be more complicated to produce.

If so, would a better solution be to allow the user to enter all of these dates and then validate when they try to submit (from the OnClick event of a command button)?

A BeforeUpdate event procedure is there to check and validate. For changing the data you could consider using an AfterUpdate. Remember of course, if the BeforeUpdate sets Cancel to True, then the AfterUpdate won't fire.

[beacon]

I think in my case it may be better for me to use a single submit as a validation for each of the dates. I've got intentions of using some BeforeUpdate things so I'll take the safe route and not chance whether the AfterUpdate actually fires or not.

(AfterUpdate is my nemesis...I've never really found a way to use it properly and consistently. BeforeUpdate used to be my nemesis, but we're on good terms now)

Thanks for your help NeoPa. The solution isn't exactly what I had hoped for, but it will work.

[NeoPa]

LOL - Be careful not to let your nemeses rule your life ;)

[beacon]

I'll do my best...although it's a constant struggle against good code and evil code.