473,320 Members | 1,839 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Clear a date field

beacon
579 512MB
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
8 10948
NeoPa
32,556 Expert Mod 16PB
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
579 512MB
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
32,556 Expert Mod 16PB
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
579 512MB
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
32,556 Expert Mod 16PB
@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
579 512MB
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
32,556 Expert Mod 16PB
LOL - Be careful not to let your nemeses rule your life ;)
Feb 13 '09 #8
beacon
579 512MB
I'll do my best...although it's a constant struggle against good code and evil code.
Feb 15 '09 #9

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

Similar topics

2
by: Sez | last post by:
Hi, I'm not a programmer. I start working as text miner and as a first task I have given 1000 dirty files that needs to be cleaned before classification tasks. I have been told python is the...
3
by: BlackFireNova | last post by:
This concerns an Access 2002 (XP) database. There are two fields, "Notes" (Memo Field) and "Notes Update" on a form (among others) which I am concerned with here. Problem: I need to be able...
0
by: Doslil | last post by:
I have a form which is designed to only data entery.When i try to open to form in the run form I have the following errors 1. when i open the form I get an error message saying "Access can't...
10
by: Kenneth | last post by:
I have a Query that consist of a lot of different sales data, and one of the colums are different date. The date goes from 1jan2003 til 31jan2003. in this Query I only want the salesdata for...
3
by: captain | last post by:
Below is the sql for data with same date need to extract + or - 5 days data of same date also. How to also get data of + and - days related to same date. SELECT IM.Area, IM.Location,...
1
by: Liz Malcolm | last post by:
Hello and TIA. I have a DE form with an option group that if daily is selected todays date is used for start and end date, if weekly is selected Monday - Friday is used. I am trying to add a...
0
by: Cleako | last post by:
Here is my dilema. I have a validation summary that I use soley for my Required Field Validators. I have it setup to run from a Page.Validate call in the code-behind, this is because I need to...
6
by: Luvin lunch | last post by:
Hi, I'm new to access and am very wary of dates as I have limited experience in their manipulation and I know if they're not done properly things can turn ugly quickly. I would like to use a...
2
by: nmakhan | last post by:
I have a validation summary that I use soley for my Required Field Validators. That works fine if I do things that way up to a point, if by chance the validation summary says "Date field...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.