Custom error message box 1

[Syerston]

Would anyone know how to produce my own error message box in Access 2000, if the user enters a date in the form that violates the validation rule for the text box. No matter what I do it keeps bringing up the default message box (not very user friendly). The validation rule states that a future date cannot be input.

Thanks in Anticipation John

 

[jebry]

Hi John!

On the Format tab of the property sheet for the text box is a property called Validation Text. Whatever you put in that property will be displayed to the user if the validation rule is violated.

hth Jeff Bridgham
bridgham@purdue.edu

 

[Syerston]

Thanks for replying Jeff.

I was looking for more of a VBA method of doing this in case I need to build a little more functionality into it. For example, to clear the text box when the user ok's the message box.

Any ideas John

 

[jebry]

Hi again!

If you want to do the error message in code then the validation will need to be done in code also. If you have a validation rule set in the table or the form then Access will check that first and reply with the error message before any code is run. Given that, here is the code you would need:

Private Sub YourTextBox_BeforeUpdate(Cancel As Integer)

If IsNull(YourTextBox.Text) = False Then
If IsDate(YourTextBox.Text) = True Then
If CDate(YourTextBox.Text) > Date() Then
Call MsgBox("You must enter today's date or an earlier date&quot
Cancel = -1
End If
Else
Call MsgBox("You must use a valid date format&quot
Cancel = -1
End If
Else
Call MsgBox("You must enter a date&quot
Cancel = -1
End If

End Sub

Of course, you can use whatever messages you want in the boxes. The Cancel = -1 will return the focus to the box with out clearing the box. If you want to clear the box then add YourTextBox.Value = "" and remove the last else statement. Note that this may not work if the text box is a bound control and the field does not allow zero length strings.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Syerston]

Jeff

As usual you save the day

Many thanks
John