Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Validation Rule On Form

Discussion in 'Information Technology' started by charles.kendricks@charter.net, Jul 28, 2006.

  1. charles.kendricks@charter.net

    Guest

    I have a client form that depends on a client table. One of the fields

    in both objects is a pull down which lists the different programs to
    which the client can be assigned to (one of which is "Discharged", and
    another field is a possible discharge date. I want the form to inforce

    the rule by which if the program fields contains "Discharged" then the
    DischargeDate field MUST contain a valid discharge date, otherwise the
    DischargeDate field should be blank. I have tried applying the
    validation rule:

    ([Program] = "Discharged" And [DischargeDate] Is Not Null) Or
    ([Program] <> "Discharged" And IsNull([DischargeDate]))


    This rule seem to work when applied to the Client table validation
    rule, however when I'm using the form I don't get my expected
    validation text which is "Invalid Discharge Date". I want the form to
    display the validation text and place the user back into the same
    record so he/she can correct the error.
     
  2. Loading...


  3. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    See whether Not IsNull([DischargeDate]) works any better.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    <charles.kendricks@charter.net> wrote in message
    news:1153580078.825783.54710@m73g2000cwd.googlegroups.com...
    >I have a client form that depends on a client table. One of the fields
    >
    > in both objects is a pull down which lists the different programs to
    > which the client can be assigned to (one of which is "Discharged", and
    > another field is a possible discharge date. I want the form to inforce
    >
    > the rule by which if the program fields contains "Discharged" then the
    > DischargeDate field MUST contain a valid discharge date, otherwise the
    > DischargeDate field should be blank. I have tried applying the
    > validation rule:
    >
    > ([Program] = "Discharged" And [DischargeDate] Is Not Null) Or
    > ([Program] <> "Discharged" And IsNull([DischargeDate]))
    >
    >
    > This rule seem to work when applied to the Client table validation
    > rule, however when I'm using the form I don't get my expected
    > validation text which is "Invalid Discharge Date". I want the form to
    > display the validation text and place the user back into the same
    > record so he/she can correct the error.
    >
     
  4. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    Guest

    An alternative approach, rather than setting the control's ValidationRule
    property, would be to use the Program control's BeforeUpdate event procedure,
    which supports a Cancel argument, e.g.

    Const ConMESSAGE = "Invalid Discharge Date."

    If Me.Program = "Discharged" Then
    ' inform user and set return value of
    ' Cancel argument to True if no discharge date
    If IsNull(Me.DischargeDate) Then
    MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
    Cancel = True
    End If
    Else
    ' set DischargeDate control to Null
    Me.DischargeDate = Null
    End If

    As regards the DischargedDate control you can simply set the Program
    control's value to 'Discharged' if the user enters a date, or to Null if the
    user specifically clears an existing date value in the control to Null. This
    would be done in the DischargeDate control's AfterUpdate event procedure:

    If Not IsNull(Me.DischargeDate) Then
    Me.Program = "Discharged"
    Else
    Me.Program = Null
    End If

    Alternatively this could be handled at form level rather than at each
    control level with code in the form's BeforeUpdate event procedure:

    Const ConMESSAGE = "Invalid Discharge Date."

    ' inform user and set return value of
    ' Cancel argument to True if value of
    ' program contol not compatible with
    ' value of DischargeDate control
    If (Me.Program = "Discharged") = (IsNull(Me.DischargeDate)) Then
    MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
    Cancel = True
    End If

    The latter approach assumes that the program column does not allow Nulls.
    Otherwise the code would need to be extended to cater for this.

    Ken Sheridan
    Stafford, England

    "charles.kendricks@charter.net" wrote:

    > I have a client form that depends on a client table. One of the fields
    >
    > in both objects is a pull down which lists the different programs to
    > which the client can be assigned to (one of which is "Discharged", and
    > another field is a possible discharge date. I want the form to inforce
    >
    > the rule by which if the program fields contains "Discharged" then the
    > DischargeDate field MUST contain a valid discharge date, otherwise the
    > DischargeDate field should be blank. I have tried applying the
    > validation rule:
    >
    > ([Program] = "Discharged" And [DischargeDate] Is Not Null) Or
    > ([Program] <> "Discharged" And IsNull([DischargeDate]))
    >
    >
    > This rule seem to work when applied to the Client table validation
    > rule, however when I'm using the form I don't get my expected
    > validation text which is "Invalid Discharge Date". I want the form to
    > display the validation text and place the user back into the same
    > record so he/she can correct the error.
    >
    >
     

Share This Page