field validation custom err msgbox (1 Viewer)

Zydeceltico said:

Hi All -

I think this is probably a relatively simple one -- but I can't figure it out yet.

I want to use the following code to provide custom error messages - particularly the one about "this field must be a number"....


This code looks like what I want but I am not sure if it would go on the Load or the BeforeUpdate event of the form:

Code:Copy to clipboard

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Select Case DataErr
        Case 2113
            MsgBox "Only numbers are acceptable in this box."
            Response = acDataErrContinue
        Case 2237
            MsgBox "You can only choose from the dropdown box."
            Response = acDataErrContinue
        Case 3314
            MsgBox "This field is required."
            Response = acDataErrContinue
        Case Else
            Response = acDataErrDisplay
    End Select
    ActiveControl.Undo
End Sub

Thoughts?

Thanks!

Tim

Click to expand...

Micron said:

Not sure I get the question. It looks like something you want to do? Or you want to use that but are asking if it can go in form load or open events? As written, it can go in neither of those because it is pertinent only to an event that belongs to the form, and only if it has the focus when a run time error occurs.

There are a gazillion ways to trap things like wrong or no data in a field. Some of them would be related to validation that automatically fails because of how your table fields are typed (e.g. you try to put text in a number field) and the db will balk. In such cases, the form error event can be used to trap system errors and present your own message. However, an "error" about a field doesn't necessarily propagate to the form event.

You can also put validation messages at the table field level; e.g. if a numerical value exceeds a limit, you get your custom message for that field as soon as you attempt to update it. You need to first understand if what you want to capture will propagate to a form level error and be applicable to a particular field. For example, in Case 2113, do you care which field caused the error, because it's not identified. What if an error number could be caused by more than one type of validation?

Aside from table level, a lot of validation can be done by way of passing a control to one or more of a set of custom functions. So to test if a control length <= 8 you could have one function to accept both the control and its max length and return a boolean, like

If Not LengthPass(Me.ctlName,8) Then

where the function looks like

Public Function
LengthPass(ctl As Control, intLength As Integer) As Boolean
If Len(ctl) > intLength Then LengthPass = False
End Function

Lots of ways to validate control data. Someone else no doubt has better advice than mine.


EDIT - saw your post after I wrote my novel. Geez, that's all you wanted? It was right there all along
Form_Error(DataErr As Integer, Response As Integer) :banghead:

Click to expand...