Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Posts
    64

    Unanswered: Validation Rule Based On Another Field In Table

    I'm trying to set a validation rule for a field in the table. I want to set the validation rule depending on another field in the same table.

    The field I want to set a Validation Rule is CreditCardType.
    The field that this depends on is PayByTypeID.

    If PayByTypeID is CreditCard Then
    I want to prompt the user to fill the CreditCardType field if they forget to fill it in.

    PayByTypeID is is number field. It has a relationship with PayByTypes table which has;
    PayByTypeID1 = Credit Card
    PayByTypeID2 = Check

    I don't want to set the validation for the table but set it for the field instead.

    Thanks very much for taking the time help.

  2. #2
    Join Date
    Jan 2004
    Posts
    64

    Red face

    Still looking for a solution. Help is appreciated.

    Thanks.

  3. #3
    Join Date
    Nov 2003
    Posts
    1,487
    In the BeforeUpdate Event of your Form enter something like this:
    Code:
    On Error Resume next
    If Me.PayByTypeID = CreditCard And IsNull(Me.CreditCardNumber) Then
       MsgBox"Credit Card Number Required:@@" & "You have forgotten to enter the Credit Card Number. " & _
                 "Please do so now.", vbExclamation, "Card Number Required"
       Cancel = True
       Me.CreditCardNumber.SetFocus
    End If
    Hope this helps

  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Re: Validation Rule Based On Another Field In Table

    Originally posted by bsarman
    I'm trying to set a validation rule for a field in the table. I want to set the validation rule depending on another field in the same table.
    The field I want to set a Validation Rule is CreditCardType.
    The field that this depends on is PayByTypeID.
    If PayByTypeID is CreditCard Then
    I want to prompt the user to fill the CreditCardType field if they forget to fill it in.

    PayByTypeID is is number field. It has a relationship with PayByTypes table which has;
    PayByTypeID1 = Credit Card
    PayByTypeID2 = Check

    I don't want to set the validation for the table but set it for the field instead.

    Thanks very much for taking the time help.
    if isnull(CreditCardType) then
       CreditCardType.ValidationRule = "Please enter your CardType"
    endif

  5. #5
    Join Date
    Jan 2004
    Posts
    64
    Cyberlynx: It's not working. Is it because
    PayByTypeID PayByType
    1 Credit Card
    ?

    Can't figure this out although it seems it should be so simple.

    Hammbakka: The solution you offered is not what I was really asking but I thank you both for taking the time to help.

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740

    maybe this is not what you want to hear!

    i don't use validation rules or bound forms.

    on an unbound form i have as many clock cycles as i want to check the users' input in as much detail and with as many messages as i like against as many rules as i can imagine.

    so: migrate to unbound forms????????

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Jan 2004
    Posts
    64
    I'm a not very experienced with Access. I do appreciate your reply however, I don't quite follow.

    I figured out all the other fields to check the users entries and prompt them to fill in a field, eg:
    On Field Level in table (not in table or form properties)
    Validation Rule: [MarketingSourceCodeID] Is Not Null
    Validation Text: Please complete Marketing Source Code.

    However, I can't figure out how to do this on an instance when the PaymentTypeID (From PaymentTypes table) is 1 (which equals Credit Card) then I want the user to complete the Credit Card Type, Credit Card Number and Credit Card Expiration Date in OrdersTable. Hope this makes sense.

    Thanks very much and I apologize if this is a redundant way to ask this question.

  8. #8
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Form

    If this is in the contecxt of a form, I think cyberlinx was correct, although substitute the word CreditCard for 1 (Representing Credit card in your payment types table?)

    >-
    On Error Resume next
    If Me.PayByTypeID = 1 And IsNull(Me.CreditCardNumber) Then
    MsgBox"Credit Card Number Required:@@" & "You have forgotten to enter the Credit Card Number. " & _
    "Please do so now.", vbExclamation, "Card Number Required"
    Cancel = True
    Me.CreditCardNumber.SetFocus
    End If
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  9. #9
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Thumbs down Users

    Am I right in saying your users will be entering data directly into your tables? Surely not?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  10. #10
    Join Date
    Jan 2004
    Posts
    64
    Thanks for all the help. I was putting Me. instead of the tablename. That's why it wasn't working.
    This doesn't work = Me.PayByTypeID
    This works = Orders.PayByTypeID

    I think this is because Orders form is a subform within another form?!

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    On Error Resume Next
    If Orders.PayByTypeID = 1 And IsNull(Me.CreditCardNumber) Then
    MsgBox "Credit Card Number Required:@@" & "You have forgotten to enter the Credit Card Number. " & _
    "Please do so now.", vbExclamation, "Card Number Required"
    Cancel = True
    Me.CreditCardNumber.SetFocus
    End If
    End Sub

    I'm grateful for all the help everyone offered.

Posting Permissions

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