?
Solved

Access 2010 - validation of unbound text box

Posted on 2013-05-19
10
Medium Priority
?
2,829 Views
Last Modified: 2013-05-20
I have an unbound form, with an unbound text box, with format short date.

If the user enters an invalid date Access gives 'wrong type' error messsage.  I want to do my own validation and give my own 'nicer' message.

I tried to validate in the text box lost focus event, but this is too late - Access error message came first.  I then tried in the text box before update event, but again too late.

How can I catch the error myself?

(Of course, all is OK if the user uses the date picker, but not if they enter directly into the text box.)
0
Comment
Question by:MonkeyPie
[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
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 39180298
Use the textbox's ValidationRule property.
Set the property of textbox, ValidationText, to you custom message.

/gustav
0
 

Author Comment

by:MonkeyPie
ID: 39180511
Thank Gustav but no good.  I added isDate([txtEndDate]) to text box property validation rule, and "Not a valid date." to validation text.  But still, I get the "The value you entered isn't valid for this field." error generated by access checking the text box format of short date.

So, any further ideas?
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 39180705
Yes, you may need to remove formatting and normal validation from the textbox.
Then apply this code:

Private Sub txtEndDate_AfterUpdate()

    With Me!txtEndDate
        .Value = Format(.Value, "Short Date")
    End With

End Sub

Private Sub txtEndDate_BeforeUpdate(Cancel As Integer)

    Dim strValue    As String
   
    strValue = Nz(Me!txtEndDate.Value)
   
    If strValue <> "" Then
        Cancel = Not IsDate(strValue)
    End If
   
    If Cancel Then
        MsgBox "Please provide a date.", vbExclamation + vbOKOnly, "End Date"
    End If
   
End Sub

/gustav
0
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.

 
LVL 48

Expert Comment

by:Dale Fye
ID: 39180716
I prefer to use some other event associated with the form to do my validation in VBA.

What else do you have going on on that form?  Is there a "save" button or some other button that will cause a particular action (filter, report, ...).  If so, you could put your "validation rule" in VBA code in the click event of that button and if the "validation" fails, exit the sub.

Another place that I generally put my validation rules for bound fields (would work for unbound as well) is in the Form_BeforeUpdate event.  With the BeforeUpdate event, you would simply set the Cancel argument to True if the "validation" fails.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 39180935
The issue is that Access - before anything else - validates the input against the data type that matches a defined Format of the textbox. If this fails, it pops the  'wrong type' error messsage.

The trick is to remove such format. Then text is expected, and every input matches this.

/gustav
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 39182496
Since there is a built-in Date Picker in Access 2010, you could just let the user select a date from the little pop-up calendar; that assures that the date will be valid.  The selected format will be applied when the user tabs away from the control.
0
 

Author Comment

by:MonkeyPie
ID: 39182760
Thank you everyone for worthwhile suggestions, however the field must have format SHORT DATE to provide the date picker, and the user must be able to type in manually their date - this is a client specification.  

When user types an invalid date we get the Access error message.

The client has drawn my attention to this 'bug' and wants it fixed.

So far, LOST FOCUS, BEFORE UPDATE, FORM SAVE BUTTON events are all too late.

So, back to the drawing board?

Some background: the form is just a little popup with START DATE and END DATE fields that are kept as registry keys as default date range for all reports.  User can change these defaults via this form.
0
 
LVL 48

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 39182958
I think someone may have already mentioned this, but use the Form_Error Event.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
   
    If DataErr = 2113 Then
        MsgBox "Invalid date! " & DataErr
        Response = acDataErrContinue
    Else
        Response = acDataErrDisplay
    End If

End Sub
0
 

Author Closing Comment

by:MonkeyPie
ID: 39183195
Yes!  That is the solution - thank you.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 39183472
Yes, that is useful here.

Just remember that the error will pop for any invalid input, so for a "normal" form you would have to check for which control is active to customise the error message.

/gustav
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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 …
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

801 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