Validation Rule On Form

[charles.kendricks]

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.

 

[Douglas J. Steele]

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

 

[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