MonkeyPie
asked on
Access 2010 - validation of unbound text box
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.)
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.)
ASKER
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?
So, any further ideas?
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(Ca ncel 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
Then apply this code:
Private Sub txtEndDate_AfterUpdate()
With Me!txtEndDate
.Value = Format(.Value, "Short Date")
End With
End Sub
Private Sub txtEndDate_BeforeUpdate(Ca
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
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.
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.
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
The trick is to remove such format. Then text is expected, and every input matches this.
/gustav
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes! That is the solution - thank you.
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
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
Set the property of textbox, ValidationText, to you custom message.
/gustav