Access error message help button

how did you set the data validation rule?

You can create your own error handlers in VBA routines.

Sub Button1_Click()
On Error GoTo Err_Button1_Click()

    'routine here

Exit_Button1_Click():
    Exit Sub

Err_Button1_Click():
    If Err.Number = 3316 Then
        MsgBox "You're not supposed to do that silly.", , "Oops!"
    Else
        MsgBox "I'm not sure what you did but it broke my app!........silly", , "Oops!"
    End If
    Resume Exit_Button1_Click()

End Sub

if you set the validation rule in the field of the table as Required Yes, you can do this as an alternate solution

Set the following Field property as follows
Required = No
Validation Rule = IS NOT NULL
Validation Text = "here you can place your own message like " The value in field <Name of field > is required. blah,,, blaah

My data validation rules are set either on form controls, for example:  Nz([Name],"")<>"" (the field must be completed and not zero length) or on a table, e.g. Trim$("" & [Surname] & [GivenNames] & [GenID])<>"" (at least one of the three fields must be specified).

I have sub-classed the Form object to standardise the handling of standard controls and navigation across all forms in my application; ideally, error-handling would be another thing I would standardise in VBA (using the validation text associated with the various validation rules on the forms and tables) but only if I can avoid exposing my users to Access help. I have coded a form_error routine in the class module as shown in the attached code snippet.

Private Sub m_ctlForm_Error(DataErr As Integer, Response As Integer)

   On Error GoTo Error_Handler

    Response = acDataErrContinue 'Default behaviour
    Select Case DataErr
        Case 2237 'Not in list
            'Ignore -- will have been handled in unique form on_error
        Case 3316, 7753
            Response = acDataErrDisplay 'allow access to display error message
        Case 3101, 3162 'Missing element in drop-down box
            myErrorReporter.ReportExpectedError "That " & m_strFormItemType & " is incomplete. Please complete all mandatory fields"
        Case 2107, 3314, 3315, 3317  'Invalid item or missing item in dropdown box
            Response = acDataErrDisplay 'allow access to display error message
        Case 2116 'Ignore
        Case 2501, 3021, 3059, 3709    'Action cancelled
        Case 3022 'Duplicate item
            myErrorReporter.ReportExpectedError "That " & m_strFormItemType & " already exists"
        Case 3200 'Can't delete an item in use
            myErrorReporter.ReportExpectedError "You cannot delete a record that is in use elsewhere"
        Case 2169 'Can't save when closing
            Response = acDataErrDisplay 'allow access to prompt for right course of action
        Case Else 'Unanticipated error
            myErrorReporter.ReportUnexpectedError DataErr, , "Form"
    End Select

Exit_procedure:

   On Error GoTo 0
   Exit Sub

Error_Handler:

    myErrorReporter.ReportUnexpectedError Err.Number
    Resume Exit_procedure
    Resume
End Sub

Select allOpen in new window

Thank you; I already find MZtools invaluable and it will make the task much easier if I have to move the error handling out of the subclassed form module into the individual form modules. However, if I could just find an answer about the Help file, I might be able to avoid doing so.

colevalleygirl,
error 3316 is <table level validation text>

is my post at http:#a33410136  did not help at all?

I'd think that you're best best would be to roll your own custom help by creating a set of custom errors.

Use the code at http://msdn.microsoft.com/en-us/library/aa200518%28office.10%29.aspx to list the existing error number.  This will show you the ones available for use as custom numbers.  Look up "vbObjectError" in the VBA help file for more on this.

(If it comes to it, your other option is to write a custom help file, distribute it with your app, and make sure that the topic ids are set properly.  That's a whole subject in itself, and I'm not going to get drawn in to that discussion on a work day, no matter how fascinating!)

capricorn1, thank you, but no -- I already had the table validation rules set up as you describe. My problem is understanding if or how a custom help file would interact with the help button that Access displays with some (not all) data validation errors.

I did try trapping the validation errors (3316, 7753, 3101, 3162,  2107, 3314, 3315, 3317) and then displaying the validation text associated with the active control in my own message box. However, when an error is trapped exiting the form, the active control may not be the one with the invalid data so that didn't work either.

At this point, my options look like:

1. Displaying a very vague error message, akin to (but a little more serious than): "Nought out of Ten. There is a problem with your data. Go back and try again." Easiest for me but I suspect my users won't be happy.
2. Coding specific field validation into the BeforeUpdate event of every validated control -- I can standardise this a little by sub-classing the mandatory controls and hooking the BeforeUpdate event -- and coding table validation into the BeforeUpdate event of every form. I'll need also to perform similar validatiion wherever I use DAO to update the data. This looks like the route you would all pursue (maybe without the sub-classing wrinkle).
   
3. Experimenting with custom help files (which will be a major learning curve that I'd hoped to put off until the next phase of development) to see if I can suppress the Access help which will thoroughly confuse my users.

Andrew_Webster: I'm not sure where your proposal for custom error codes would be better than option (2) above -- what am I missing?

Andrew_Webster: Number/skill level of users unknown. It's an application that I will be offering for free (or a donation) to other family history researchers (using Access runtime). I've already colour-coded the mandatory fields (based on a tag that I could also use to assign them to the mandatory field class if I choose to go that route) and set tooltips on optional fields to say that they're optional. All combo boxes work on lookup tables.

I think I will leave the Access help buttons in place until I've run the app past a group of Beta testers; and then determine what else I need to do.

Thanks to everyone for their input.