Conditional Validation Rule in MS Access

I have a table in MS-Access - let's call it tComputers. Two of the fields in that table are titled Status - with the options of Activ开发者_高级运维e, Storage, Deactivated - and DeactivationDate.

If I wanted to make DeactivationDate mandatory if and only if the value of Status is Deactivated, how can I do that?

If I wanted to make DeactivationDate mandatory if and only if the value of status is Deactivated how can I do that?

Conversely, if a Deactivated record later changes Status ... say becomes Active ... should the DeactivationDate be discarded?

I think you can accomplish this with a table level Validation Rule. With tComputers in Design View, open the property sheet and use this as the Validation Rule property:

IsNull([DeactivationDate])=IIf([Status]="Deactivated",False,True)

Figure out what message you want the users to see when that validation rule is violated and put it in as the Validation Text property. It's unlikely the default message would mean anything to them.

Although it's possible, I doubt know how useful this is. The users should be editing data via a form, and that gives you the opportunity to enforce your validation requirements before the database engine even attempts to save the data ... use the Form_BeforeUpdate event as @mwolfe02 described.

Edit: Here is an outline for Form_BeforeUpdate. It assumes you have a combo named cboStatus bound to the Status field, and a text box named txtRetireDate bound to the RetireDate field.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim strMsg As String
    Select Case Me.cboStatus
    Case "Deactivated"
        If IsNull(Me.txtRetireDate) Then
            strMsg = "RetireDate is required for Status = Deactivated"
            MsgBox strMsg
            Me.txtRetireDate.SetFocus
            Cancel = True
        End If
    Case "Active", "Storage"
        '* what should happen here? *'
    Case Else
        '* what should happen here? *'
    End Select
End Sub

If you want to enforce this at the table level I believe you are out of luck. You might be able to do something with Data Macros if you are using Access 2010. EDIT: I stand corrected. Though I personally don't ever use this functionality (preferring to handle the validation at the form level where more complex validation is practical) it is most definitely possible: Validation Rules

If your users will only be updating the data through a bound form, you can perform the validation in the Form_BeforeUpdate event. Then if Status = 'Deactivated' and DeactivationDate is Null, you would set Cancel = True which will prevent the changes from being saved. Obviously you'd want to show a message box or indicate in some other way why the form could not be saved.

If you are entering the data with a form, you could use VBA to validate the form entries. On the button press to save the data you can check to see if the value of status is Deactivated, and if it is, you can require DeactivationDate to have a value. Otherwise, the data won't be saved.

Doing what you want in the table design window, I'm not sure how that could be done.