Need help on Date field Validation

[jayjayplane]

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

[missinglinq]

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:

Private Sub YourDate_BeforeUpdate(Cancel As Integer)
  If Me.YourDate > Date Then
   MsgBox "The Date Must Be Today's Date or an Earlier Date!"
   Cancel = True
  End If
End Sub

Where YourDate is replaced by the actual name of your textbox.

Welcome to Bytes!

Linq ;0)>

[beacon]

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:

Private Sub myTextField_Exit(Cancel as Integer)  '<--- this should be there waiting
     if myTextField.Value > Date then
          msgbox "You've entered a future date", vbExclamation
          myTextField.Value = " "
     end if
End Sub
 

[jayjayplane]

thanks everybody's reply, I feel so lucky I found this useful forum.

[missinglinq]

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)>

[jayjayplane]

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...

[beacon]

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:

Private Sub DischargeDateAFP_BeforeUpdate(Cancel As Integer)
    Dim myDate
 
    myDate = Me.DischargeDateAFP.Value
 
    If myDate > Date Then
        MsgBox "You've entered a future date", vbOKOnly, "Future Date"
        Me.Undo
    End If
End Sub
 

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.

[missinglinq]

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.

Private Sub DischargeDateAFP_BeforeUpdate(Cancel As Integer)
    Dim myDate
 
    myDate = Me.DischargeDateAFP.Value
 
    If myDate > Date Then
        MsgBox "You've entered a future date", vbOKOnly, "Future Date"
        Cancel = True        
        Me.DischargeDateAFP.Undo
     End If
End Sub

Linq ;0)>

[beacon]

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.

Private Sub DischargeDateAFP_BeforeUpdate(Cancel As Integer)
    Dim myDate
 
    myDate = Me.DischargeDateAFP.Value
 
    If myDate > Date Then
        MsgBox "You've entered a future date", vbOKOnly, "Future Date"
        Cancel = True        
        Me.DischargeDateAFP.Undo
     End If
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:

If myDate > Date Then
        MsgBox "You've entered a future date", vbOKOnly, "Future Date"
        Cancel = True
        Me.DischargeDateAFP.Value = Null
End If
 

[missinglinq]

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)>

[beacon]

Cool. Thanks again...

[beacon]

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:

Private Sub DischargeDateAFP_BeforeUpdate(Cancel As Integer)
    Dim myDate
 
    myDate = Me.DischargeDateAFP.Value
 
    If myDate > Date Then
        MsgBox "You've entered a future date", vbOKOnly + vbExclamation, "Future Date"
        Cancel = True
        Me.DischargeDateAFP.Undo
    End If
End Sub
 

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?

[NeoPa]

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.

[beacon]

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...

[NeoPa]

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.

[beacon]

Ok...sounds good NeoPa. Thank you for the gentle scolding. I'll keep my ears clean in the future.

-- beacon :)