TylerDerden
asked on
MS Access Text Box Custom Error Message
I have a text box called Txt_Date_Opened.
Rather than have the standard error message that if a use does not enter the proper date I would like to prompt with a more helpful message such as, "Please enter a valid date in the format mm/dd/yyyy".
I've placed the following code in both the On Enter and On Exit but it doesn't work.
Rather than have the standard error message that if a use does not enter the proper date I would like to prompt with a more helpful message such as, "Please enter a valid date in the format mm/dd/yyyy".
I've placed the following code in both the On Enter and On Exit but it doesn't work.
On Error GoTo Dt_Error
Exit Sub
Dt_Error:
MsbBox "Please enter the date in the format mm/dd/yyyy", , "Attention"
Exit Sub
>On Enter and On Exit
On enter fires before the user enters a value, and On Exit is not cancellable via VBA code if the user enters a value that fails validation.
BeforeUpdate fires after a value is entered, before focus moves on to the next control, and is cancellable via VBA code.
On enter fires before the user enters a value, and On Exit is not cancellable via VBA code if the user enters a value that fails validation.
BeforeUpdate fires after a value is entered, before focus moves on to the next control, and is cancellable via VBA code.
ASKER
Thnx. I think we r close. I made one slight change to the if statement. The code works if I leave the field blank but if I put in a bad value such as the text gd I still get the standard MS Access error.
Private Sub Txt_Date_Opened_BeforeUpdate(Cancel As Integer)
If Not IsDate(Nz(Me.Txt_Date_Opened, "foo")) Then
MsgBox "Please enter a valid date in the format mm/dd/yyyy"
Cancel = True
DoCmd.CancelEvent 'May need this line too
End If
End Sub
You'll want to step-through this code and see how it is executing. Try this, then run, type sb in the combo box.
You should be able to mouse over Me.txt_date_opened (make sure there's no typos) and make sure it contains the value sb.
Private Sub Txt_Date_Opened_BeforeUpda te(Cancel As Integer)
STOP '<--- ADD THIS
If Not IsDate(Nz(Me.Txt_Date_Open ed, "foo")) Then
MsgBox "Please enter a valid date in the format mm/dd/yyyy"
Cancel = True
DoCmd.CancelEvent 'May need this line too
End If
End Sub
You should be able to mouse over Me.txt_date_opened (make sure there's no typos) and make sure it contains the value sb.
Private Sub Txt_Date_Opened_BeforeUpda
STOP '<--- ADD THIS
If Not IsDate(Nz(Me.Txt_Date_Open
MsgBox "Please enter a valid date in the format mm/dd/yyyy"
Cancel = True
DoCmd.CancelEvent 'May need this line too
End If
End Sub
ASKER
I added the line of code. The default value is todays date.
If I delete the default value the subroutine works fine.
I step into the line of code.
The value of null passes to the code and I get the prompt.
However, if I add in text such as sb or a number then I do not step into the code at all. Rather the MS Access default error message populates, "The value you entered isn't valid for this field."
Can I turn off MS Access error handling then turn it back on. Kind of like turning off setwarnings?
If I delete the default value the subroutine works fine.
I step into the line of code.
The value of null passes to the code and I get the prompt.
However, if I add in text such as sb or a number then I do not step into the code at all. Rather the MS Access default error message populates, "The value you entered isn't valid for this field."
Can I turn off MS Access error handling then turn it back on. Kind of like turning off setwarnings?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks that works great!
Final code is attached.
Final code is attached.
Private Sub Txt_Date_Opened_BeforeUpdate(Cancel As Integer)
If Not IsDate(Nz(Me.Txt_Date_Opened, "foo")) Then
MsgBox "Please enter a valid date in the format mm/dd/yyyy"
Cancel = True
DoCmd.CancelEvent
Else: Me.Txt_Dt_Opened_Bound = Me.Txt_Date_Opened
End If
End Sub
Thanks for the grade. Good luck with your project. -Jim
If Not IsDate(Nz(Me.Txt_Date_Open
msgbox "Please enter a valid date in the format mm/dd/yyyy"
Cancel = True
Docmd.CancelEvent 'May need this line too
Else
'Good to go.
End If