Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1

    Unanswered: Validation Rule Property

    Greeting all,

    I want to put in some validation rules without using a "save button" (for a combination of reasons, not least of which is sloth). I have been experimenting with table level and form level validation using the "validationrule" property, and it seems to work pretty well.

    1. Is there some way to do an "is numeric" test on a numeric field in the validation rule property to force one's own error message box (as opposed to the system one)?
    2. What are the advantages and disadvantages of using the table verses form level "validationrule" property. Seems to me I would always want to use the table level one (or field one vs. control one) because it saves me putting the rules in all my different forms.

    Thoughts.

    John Smith
    currently:
    Guangxi University
    Nanning China

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I simply prefer having my validation code in front of me when I'm in the code editor, rather than having to refer to the Properties sheet in Form Design View or worse, in Table Design View.

    As for having custom warnings instead of the dreadful Access default warnings, I do something like this:
    Code:
    Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Dim Message As String
      If DataErr = 2113 Then
      Message = "You Have Entered Invalid Data In The " & Me.ActiveControl.Name & " Field!"
      Response = MsgBox(Message, vbExclamation, "Invalid Data Entered")
      Response = acDataErrContinue
     End If
    End Sub
    2113 is the error code invoked when incorrect data type is entered into a field. The errant field name is then part of the warning message. The above code can be easily be modified to cover specific textboxes and assign each an individual message, if desired, by simply adding more If...Then constructs within the current one.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1

    Validation Property Questions

    Hi Missingling,

    Learn something new every day; never knew how to capture specific system errors. I appreciate your first comment, and admit succumbing to heavy duty sloth (its for a simple application used by some teachers to capture their students' marks and absences). I have done full code error checking in the past, but have to set everything off a button, etc, etc.

    My experience with masks is that they can be clunky, especially if someone makes an error. I only recently started using the valuationproperty, and it seems to work well for simple, field-level checking. Still don't know why anyone would use it at the form (control) level (as oppose to the table-field level).

    Thanks for the information / education!

    Regards

    John S

    Regards


    John S

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I don't really believe that Input Masks are evil, as some say, but a good point to remember when using them is that the user may not always arrive at the control by tabbing to it from the previous control, but may click on the control. If a mask is being used and the user doesn't click to insert the cursor at the very beginning of the text box, he/she may start to enter data without realizing that they're not at the beginning. When they leave the control they'll get an error message because the data wasn't entered as the input mask dictated. They'll then have to go back and re-enter the info, which wastes time. The way to avoid this is to use something like this:
    Code:
    Private Sub YourControlName_Click()
        YourControlName.SelStart = 0
    End Sub
    Even tabbing to the control can be problematic if in the Options for the database the default behavior for "Entering Field" is set to anything other than "Go to start of field." The answer to this is to set the cursor to the beginning of the field when the field is tabbed into:
    Code:
    Private Sub YourControlName_GotFocus()
        YourControlName.SelStart = 0
    End Sub
    Common sense would dictate that the second snippet of code should work for both cases, since click into the field should mean the textbox has focus, but for some reason this doesn't work for all versions.

    Input Masks also make correction of data clunky, in my opinion, so I almost never use them.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •