By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,830 Members | 682 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,830 IT Pros & Developers. It's quick & easy.

Clear a date field

beacon
100+
P: 579
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub AuditedDate_BeforeUpdate(Cancel As Integer)
  2.  
  3. If (Me!AuditedDate < Me!DischargeDate) then
  4. MsgBox "Invalid Date", vbExclamation + vbOkOnly, "Error"
  5. Cancel = True
  6. Me!AuditedDate = Null
  7. Me!AuditedDate.SetFocus
  8. End If     
  9.  
Feb 13 '09 #1
Share this Question
Share on Google+
8 Replies


NeoPa
Expert Mod 15k+
P: 31,418
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.
Feb 13 '09 #2

beacon
100+
P: 579
If line #6 says:
Expand|Select|Wrap|Line Numbers
  1. Me!AuditedDate = Null
  2.  
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:
Expand|Select|Wrap|Line Numbers
  1. Me!AuditedDate.Value = Null
  2.  
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.
Feb 13 '09 #3

NeoPa
Expert Mod 15k+
P: 31,418
Thanks for that. It makes the situation much clearer.

You appreciate now (I suspect) that the earlier point I made is actually your problem?
Feb 13 '09 #4

beacon
100+
P: 579
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)?
Feb 13 '09 #5

NeoPa
Expert Mod 15k+
P: 31,418
@beacon
Not a problem at all. Admitting we don't understand is half way to understanding.
@beacon
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.
@beacon
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.
Feb 13 '09 #6

beacon
100+
P: 579
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.
Feb 13 '09 #7

NeoPa
Expert Mod 15k+
P: 31,418
LOL - Be careful not to let your nemeses rule your life ;)
Feb 13 '09 #8

beacon
100+
P: 579
I'll do my best...although it's a constant struggle against good code and evil code.
Feb 15 '09 #9

Post your reply

Sign in to post your reply or Sign up for a free account.