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

Need help on Date field Validation

P: 26
Currently I use Access form/subform to design a database interface, one field is date type field, but I need if the user inputs the future date like later than system currently date, I want it pop up an error message to remind the user to re-entry the correct date, I donít know how to do it. Does it need write VBA code or modify the validation rule and how to do that,

Thank you
Sep 25 '08 #1
Share this Question
Share on Google+
15 Replies


missinglinq
Expert 2.5K+
P: 3,532
You can do this in Design View for the table by using this

<=date()

in the Validation Rule Property. If you go this route, be sure and enter a meaningful explanation in the Validation Text Property.

To do this at the form level, you'll need this code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub YourDate_BeforeUpdate(Cancel As Integer)
  2.   If Me.YourDate > Date Then
  3.    MsgBox "The Date Must Be Today's Date or an Earlier Date!"
  4.    Cancel = True
  5.   End If
  6. End Sub
Where YourDate is replaced by the actual name of your textbox.

Welcome to Bytes!

Linq ;0)>
Sep 25 '08 #2

beacon
100+
P: 579
Currently I use Access form/subform to design a database interface, one field is date type field, but I need if the user inputs the future date like later than system currently date, I want it pop up an error message to remind the user to re-entry the correct date, I donít know how to do it. Does it need write VBA code or modify the validation rule and how to do that,

Thank you
You do need some VBA code. Open up the properties for the text box that has the date in it and go to the onExit event. You will be taken to the VBA code and there will be a 'Private Sub' waiting for you.

Type in the following code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub myTextField_Exit(Cancel as Integer)  '<--- this should be there waiting
  2.      if myTextField.Value > Date then
  3.           msgbox "You've entered a future date", vbExclamation
  4.           myTextField.Value = " "
  5.      end if
  6. End Sub
  7.  
Sep 25 '08 #3

P: 26
thanks everybody's reply, I feel so lucky I found this useful forum.
Sep 25 '08 #4

missinglinq
Expert 2.5K+
P: 3,532
Glad we could help.

BTW, the code should go in the textbox BeforeUpdate event, rather than in the OnExit event. In the BeforeUpdate event, it will only fire if data is entered into the field. Placed in the OnExit event, it will fire every time the user tabs thru the textbox, even if no data is entered or edited, and this is a waste of time processor resources.

Linq ;0)>
Sep 25 '08 #5

P: 26
Glad we could help.

BTW, the code should go in the textbox BeforeUpdate event, rather than in the OnExit event. In the BeforeUpdate event, it will only fire if data is entered into the field. Placed in the OnExit event, it will fire every time the user tabs thru the textbox, even if no data is entered or edited, and this is a waste of time processor resources.

Linq ;0)>


thank you for such a detailed the answer...
Sep 25 '08 #6

beacon
100+
P: 579
Glad we could help.

BTW, the code should go in the textbox BeforeUpdate event, rather than in the OnExit event. In the BeforeUpdate event, it will only fire if data is entered into the field. Placed in the OnExit event, it will fire every time the user tabs thru the textbox, even if no data is entered or edited, and this is a waste of time processor resources.

Linq ;0)>
Hi Linq,

When I place this code in the BeforeUpdate event and type in a future date, all of the fields get cleared, not just the date field. Is this supposed to happen?

Here's my code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub DischargeDateAFP_BeforeUpdate(Cancel As Integer)
  2.     Dim myDate
  3.  
  4.     myDate = Me.DischargeDateAFP.Value
  5.  
  6.     If myDate > Date Then
  7.         MsgBox "You've entered a future date", vbOKOnly, "Future Date"
  8.         Me.Undo
  9.     End If
  10. End Sub
  11.  
If this violates the cross-posting policy, I apologize, but I thought, since I tried to answer jayjay's question, that I ought to try and determine why my answer was wrong (which you identified) and how I could get it to work appropriately...in case jayjay runs into the same problem.
Sep 25 '08 #7

missinglinq
Expert 2.5K+
P: 3,532
The problem, for you, is that you've replaced Cancel = True with Me.Undo.

Cancel = True, which was in the code I posted here, merely stops the data from being committed to the field. The data entered stays in the textbox.

Me.Undo, as you've used in your code, does just what you've seen, deletes all entered data from all textboxes! You need to replace the Cancel = True, and if you want to delete the entered date for this single field, you can add an Undo command, but just for the single textbox, not for the entire record!

See Lines # 8 & 9.

Expand|Select|Wrap|Line Numbers
  1. Private Sub DischargeDateAFP_BeforeUpdate(Cancel As Integer)
  2.     Dim myDate
  3.  
  4.     myDate = Me.DischargeDateAFP.Value
  5.  
  6.     If myDate > Date Then
  7.         MsgBox "You've entered a future date", vbOKOnly, "Future Date"
  8.         Cancel = True        
  9.         Me.DischargeDateAFP.Undo
  10.     End If
  11. End Sub
Linq ;0)>
Sep 26 '08 #8

beacon
100+
P: 579
The problem, for you, is that you've replaced Cancel = True with Me.Undo.

Cancel = True, which was in the code I posted here, merely stops the data from being committed to the field. The data entered stays in the textbox.

Me.Undo, as you've used in your code, does just what you've seen, deletes all entered data from all textboxes! You need to replace the Cancel = True, and if you want to delete the entered date for this single field, you can add an Undo command, but just for the single textbox, not for the entire record!
See Lines # 8 & 9.

Expand|Select|Wrap|Line Numbers
  1. Private Sub DischargeDateAFP_BeforeUpdate(Cancel As Integer)
  2.     Dim myDate
  3.  
  4.     myDate = Me.DischargeDateAFP.Value
  5.  
  6.     If myDate > Date Then
  7.         MsgBox "You've entered a future date", vbOKOnly, "Future Date"
  8.         Cancel = True        
  9.         Me.DischargeDateAFP.Undo
  10.     End If
  11. End Sub
That makes sense. I actually tried using the me.textbox.undo, but it didn't reset the textbox entirely. What I mean is that, if I typed in a legitimate date first, then came back and changed it to a future date, the date would just revert back to the last date that I entered correctly when I really wanted to textbox to clear out.

So I changed it to the following, but I want to make sure that my use of Null will not mess anything else up down the line:
Expand|Select|Wrap|Line Numbers
  1. If myDate > Date Then
  2.         MsgBox "You've entered a future date", vbOKOnly, "Future Date"
  3.         Cancel = True
  4.         Me.DischargeDateAFP.Value = Null
  5. End If
  6.  
Sep 26 '08 #9

missinglinq
Expert 2.5K+
P: 3,532
That's correct, Undo takes you back to the last saved data for a textbox or form, depending on the implementation you've used. You use of Null is alreight in this instance, I think. It appears to work OK in a field defined as Date/Time.

Linq ;0)>
Sep 26 '08 #10

beacon
100+
P: 579
Cool. Thanks again...
Sep 26 '08 #11

beacon
100+
P: 579
Sorry, but I stumbled upon one more question. If you don't know the answer it's ok, and if you already answered it, I didn't see it when I re-read the post.

The code that I posted before was actually for a command button to test the value of the textbox. When I use the Me.textbox.Value = Null for the BeforeUpdate event, I get error number 2115 about Access not being able to save the record.

Here's how I came up with the question. I have a user that opens the form, goes to the date textbox, enters a valid date (not a future date) and then goes about typing. Then, decides that they've entered the wrong date and try to retype it. When they retype it they enter in a future date.

The way the code is setup, the Me.textbox.undo only goes back one step, which doesn't completely remove the values from the textbox. I was hoping that I could use the Me.textbox.Value = Null to completely clear out the textbox so as to avoid a record being saved.

I guess my question is whether or not there's a way to check the value in the textbox, if it's greater than today's date notify the user, then completely clear the box out.

I thought that I might be able to write a loop that continues to use the Me.textbox.Undo until the value in the textbox equals Null or an empty string, but because it's a date field I think it has issues with that.

Here's my BeforeUpdate event:
Expand|Select|Wrap|Line Numbers
  1. Private Sub DischargeDateAFP_BeforeUpdate(Cancel As Integer)
  2.     Dim myDate
  3.  
  4.     myDate = Me.DischargeDateAFP.Value
  5.  
  6.     If myDate > Date Then
  7.         MsgBox "You've entered a future date", vbOKOnly + vbExclamation, "Future Date"
  8.         Cancel = True
  9.         Me.DischargeDateAFP.Undo
  10.     End If
  11. End Sub
  12.  
I'm worried that using the BeforeUpdate event isn't actually the event that I want to use since I have a Submit button that I want to be the only way a user can update a row.

Any ideas?
Sep 26 '08 #12

NeoPa
Expert Mod 15k+
P: 31,602
You have asked your question in another member's thread. This is called thread hijacking and is not allowed on this site. There are various reasons for this, the principal of which is that it typically diverts the flow of the thread away from their needs and into yours. Another is that it will tend to mean that your new question is not seen as such, and may get overlooked by many of our experts (You appreciate that they're not looking for answered questions).
Please post your questions in their own threads in future (See How to Ask a Question).

Administrator.

PS. As an experienced member I'm surprised you need reminding of this.

However, as a member who I'm sure I've discovered offering help around the place, you get leeway to make a mistake or two. Post your question(s) in a separate thread and post a link in here for any interested parties to follow you across. That way you can be sure that Linq will at least be aware of your questions.
Sep 30 '08 #13

beacon
100+
P: 579
Hi NeoPa,

I apologize for this. Actually, I made mention of it in the post earlier, but when no one indicated that it was a problem I continued asking questions.

I thought that maybe the questions I was asking would continue to help the other user since it was his question that prompted me to attempt what I did and stumble onto the issue.

Anyway, it won't happen again.

Thank you...
Sep 30 '08 #14

NeoPa
Expert Mod 15k+
P: 31,602
Well, it seems like you were sensitive to the issue at least. No big deal, but we need to protect the newer members as some of them can feel intimidated if those with higher post counts get busy in their threads.

Clarification posts are ok, but best to err on the side of posting a new thread if unsure.
Sep 30 '08 #15

beacon
100+
P: 579
Ok...sounds good NeoPa. Thank you for the gentle scolding. I'll keep my ears clean in the future.

-- beacon :)
Sep 30 '08 #16

Post your reply

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