Date validation on form

[accessnewbie]

Hi, all. I have a very simple form. It has a drop down menu, 2 date fields, and a button.



The user must select an item from the drop down menu. If the user provides a begin date, they must provide an end date, and vice versa. Then they click the button to open a report. I have VBA to confirm that all the necessary information is entered.

Basically, if they only select an item from the drop down menu and click the button, the report shows all time data. If they select an item from the drop down menu and provide a begin and end date, the report shows the data between that time frame.

My problem comes when an invalid date is entered. For example, if I try putting 2/29/2010 in my date field, I get a very ugly "The value you entered isn't valid for this field." error. My users will not understand this. I'd like to work in a date check and display a more helpful error message if the date is invalid. Is there an easy way to do this without having to change the text boxes from date to text? I'd like my users to still have the date picker option.

Thank you for any and all assistance. You're all awesome!
Marisa

[RuralGuy]

Hi Marisa,
That error can be caught in the OnError Event of the form where you can subsitute your message instead and tell Access to *not* display the internal message.

[accessnewbie]

Hi, RuralGuy. Thank you for your reply! I believe I'm half way there. In the "On Error" event of my form, I added the following logic:

If IsDate(dateBegin) Then
If IsDate(dateEnd) Then
MsgBox "An error has been encountered. Please contact Marisa.", vbOKOnly, "Error Encountered"
Else
MsgBox "An invalid date has been entered as the End Date. Please enter a valid date.", vbOKOnly, "Error Encountered"
End If
Else
MsgBox "An invalid date has been entered as the Begin Date. Please enter a valid date.", vbOKOnly, "Error Encountered"
End If

When I enter an invalid date, my helpful message is displayed. I click OK and then the ugly Access message pops up. Do you have any insight as to where I'm going wrong?

Thank you for all your help,
Marisa

[RuralGuy]

Sorry, I was gone all day. Try:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
   Dim sMsg As String
   If IsDate(dateBegin) Then
      If IsDate(dateEnd) Then
         sMsg = "Error No:    " & lngErrNumber & vbCrLf & _
                "Description: " & strErrDescription & vbCrLf & _
                "An error has been encountered. Please contact Marisa."
         MsgBox sMsg, vbOKOnly, "Error Encountered"
         Response = acDataErrContinue
      Else
         MsgBox "An invalid date has been entered as the End Date. Please enter a valid date.", vbOKOnly, "Error Encountered"
         Response = acDataErrContinue
      End If
   Else
      MsgBox "An invalid date has been entered as the Begin Date. Please enter a valid date.", vbOKOnly, "Error Encountered"
      Response = acDataErrContinue
   End If
End Sub

You could also examine the incoming DataErr value and make decisions if you wanted to.

[accessnewbie]

Hi again. This code worked great! I just noticed one strange thing during testing but I doubt it will ever happen.

I chose my college from the drop-down menu. I put a date of 1/1/2011 in the begin date. I put a date of 2/29/2011 in the end date. I get the pretty date error for the end date. I change it to 2/28/2011 and when I click the button, the report runs fine. However, one time I did the same thing except after I changed the end date to 2/28/2011, I returned to the begin date and changed it to 11/31/2010. I got the ugly Access error. I stepped through the code and the value of my dateBegin field is still the 1/1/2011. It's like it didn't realize the date was changed.

I've been toying around with it for a while now (probably too long) and can live happily with it as is, but if you know of a quick solution, I would be interested. I tried putting the date check other places (such as After Update on the text boxes) and a "Refresh" in the OnError event (which didn't do what I thought it would).

You've been so very helpful and I appreciate it so much!
Marisa

[RuralGuy]

I put all of my validation code in the BeforeUpdate event of a control and Cancel the event if there is a problem which returns the focus to the control. You can throw in a Me.ControlName.Undo if you want to start over.

[RuralGuy]

I should also say I don't have any validation done at the table level.