Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS Access Text Box Custom Error Message

Posted on 2007-12-06
8
Medium Priority
?
3,046 Views
Last Modified: 2013-11-28
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.


On Error GoTo Dt_Error
 
  Exit Sub
 
 Dt_Error:
 
  MsbBox "Please enter the date in the format mm/dd/yyyy", , "Attention"
  Exit Sub

Open in new window

0
Comment
Question by:TylerDerden
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 20420009
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
Else
   'Good to go.
End If
 
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 20420059
>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.

0
 

Author Comment

by:TylerDerden
ID: 20420329
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

Open in new window

0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 66

Expert Comment

by:Jim Horn
ID: 20420448
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_BeforeUpdate(Cancel As Integer)
STOP   '<--- ADD THIS
 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
0
 

Author Comment

by:TylerDerden
ID: 20420683
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?
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 1000 total points
ID: 20420705
In that case, your table.column data type is forcing the formatting before you can trap it with the form.control.BeforeUpdate event.

You'll likely need to make your textbox unbound, ie nothing in the ControlSource property.  This will allow any value to be entered.
Then, you can always have the textbox with the correct ControlSource hidden, and if validation is passed set Me.UserEnteredControl = Me.TheBoundCountrol

>Can I turn off MS Access error handling then turn it back on.
Not in this case.
0
 

Author Comment

by:TylerDerden
ID: 20422090
Thanks that works great!
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

Open in new window

0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 20423250
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question