Validation Rule error handling on a Form control- how to display meaningful messages

[XelaIrodavlas]

Hi all,

Please help, I'm having some trouble with a control on a form: The control asks for a value between 0 and 50, so naturally I entered the Validation rule <=50. I haven't used a lot of validation rules (for some reason) but that seems to work, so that's not really the problem.

If a user enters a value outside of the range they receive the message:

The value you entered does not meet the validation rule defined for the field or control.
To see the validation rule, switch to Design view or Layout view, click the appropriate field, and then, if the property sheet is not open, press F4. Then, click the Data tab in the property sheet.
Enter a value that meets the validation rule, or press ESC to undo your changes.

This doesn't mean a lot to the average user, so I was trying to capture the error and replace it with a more meaningful message.

I started with the following code, which I tried entering into the form's On Error event:

 Select Case Err.Number
 Case 2107
 MsgBox "Please enter a number between 0 and 50."
 End Select

But this doesn't seem to work, I added 'msgbox Err.number' to the top and it then came out with 0, suggesting this wasn't triggering as an error on the form.

Is there another approach I can use? Or can you just not capture validation errors in this way?

Any thoughts much appreciated

Thanks,

 

[XelaIrodavlas]

Just realised I'm probably being thick (not the first time, sorry) I can do this just using the before update event to check the value and cancel/undo/msgbox etc....

Consider my previous question an academic exercise

For anyone in my situation here's what i used in the end:

Private Sub Combo80_AfterUpdate()
If Me!Combo80 > 50 Or Me!Combo80 < 0 Then
MsgBox "Please enter a value between 0 and 50"
Me!Text82.SetFocus 'for some reason I need to move the focus away first before I can set the focus back... Any control would do.
Me!Combo80.SetFocus
End If
End Sub

Thanks all,

 

[gemma-the-husky]

you said beforeupdate and used the afterupdate
you probably ALSO need to check for NULL

 Private Sub Combo80_BeforeUpdate(cancel as integer)

 If Me!Combo80 > 50 Or Me!Combo80 < 0 Then
    MsgBox "Please enter a value between 0 and 50"
     cancel = true 'refuses to accept the entry
  
     'some version of me.undo will probably remove the faulty entry, 
     'although I tend to use sendkeys "{esc}"
    exit sub
End If
End Sub

 

[XelaIrodavlas]

Yes sorry you can probably tell I wrote that response pretty quickly, thought I'd get in before anyone wasted too much time writing a lengthy reply.

The code has changed a bit since then, but you get the picture. Thanks for the reply