Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: Form field data validation

    Is it possible to create a validation rule that will force the user to enter a value between 15 and 500, and the value must be in mulitples of 15 or 30?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Not sure if you can as a rule within the table (ie within JET) / table design

    But it can be done by placing some validation code in the forms on change event / before update events.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    This will work. Replace ParamerField with the actual name of the control you're validating.

    Private Sub ParamerField_BeforeUpdate(Cancel As Integer)
    If Not ((Me.ParamerField.Value >= 15 And Me.ParamerField.Value <= 500) And ((Me.ParamerField.Value) Mod 15 = 0)) Then
    MsgBox "Value Must Fall Between 15-500 and Be Evenly Divisible by 15", vbOKOnly, "Field Value Incorrect !"
    End If
    End Sub

    You may want to change the wording on the message box, but this is the general idea.
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Missingling

    Like it

    but I think I should done @ the Table level

    in the General tab Validation Rule

    The only reason is When you create a New form the rule is always there.
    one less thing to remember
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I agree, myle, but I also agree with healdem's above comment. I'm not sure you can do it at the table level. Spent a bunch of time trying to, but could never get that dog to hunt! Could get one or the other of the two criteria to function, but not both. Error message kept insinuating that the validation was too complex! But I'd love to have someone prove me wrong!
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    poor cat

    in the Validation rule just type

    >15 And <500

    in Validation text Type you Message

    But if you want To do a Field name check you have to do that @ the Table Properties Area.

    eg [PaymentDate] > [InvoiceDate] would be put in the Table Properties

    Validation text = "Please check Payment Date"

    Msaccess will start have a fix if there are spaces in the feildname @ this level
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    myle,

    You didn't catch all the validation requirements!

    value between 15 and 500, AND the value must be in mulitples of 15 or 30
    that's the problem at the table level.

    Doing the between 15 and 500 works fine at the table level, it's trying to do that AND the
    must be in multiple of 15 or 30
    that's the problem at the table level. Since anything that's a multiple of 30 is automatically a multiple of 15, I did simplify the thing a little bit.

    The cat thank's you for your concern!
    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
  •