Performing Form Validation with an Access 2003 ADP project

Question

I'm developing an Access 2003 Database that uses a MS SQLServer backend.

I'm trying to do Form Validation and am experiencing some problems.

1. ValidationRule for each field seems to be ignored
2. I can't figure out what event I should override to enforce validation without having the database do it. (I'm not against this, it's just unknown to me how I'd catch Error Messages, instead of displaying them to the user)

I've tried getting around number 2 by disallowing closing and enforcing the use of a "Close Button", but the user can side step it by pressing tab, or by pressing the "Next Record" button at the bottom.

Any suggestions would be greatly appreciated.

Answer 1

If you give us an example of one of your validation rules, it might help.

However, I've been under the habit of putting validation in Before Update (especially when I've got more complex validation algorithms)

Update: I did some experimentation and it seems that the validation rule for the field is ONLY checked when you make an update to the field, but not if you are making an update somewhere else on the form, and not when you save the record. So I think your solution is to move validation to Form_BeforeUpdate.

OR you could make the column in the table non-null - which would force the users do something with the field.

OR, if the field must be "Testing", then you could side step the whole thing by setting a default value and locking the field so the user can't change it.

Answer 2

If you are using the validation rules property, you can catch validation and duplicate key errors, amongst other things, in the Form Error event:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
   If DataErr=2107 Then
      MsgBox "Validation error! "  & ActiveControl.Name
   End If
End Sub

You can enforce the use of your close button with a variable defined at form level and set to false unless your button is clicked.

Option Compare Database
Option Explicit

Public AllowClose As Boolean

Private Sub Form_Load()
    AllowClose = False
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Cancel = Not AllowClose
End Sub
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click

    AllowClose = True
    DoCmd.Close

Exit_cmdClose_Click:
    Exit Sub

Err_cmdClose_Click:
    MsgBox Err.Description
    Resume Exit_cmdClose_Click

End Sub

From: http://www.tek-tips.com/faqs.cfm?fid=2071

Answer 3

You can hide the navigation buttons by setting the form's Navigation Buttons property to "No." You can also disallow tabbing to a new record by setting the form's Cycle property to "Current Record."

With any nontrival application interface in Access, I usually lock the form down pretty tight and supply my own nav buttons, close button, etc. as needed.