Conditional If

  • Thread starter Thread starter Beverly Darvill
  • Start date Start date
B

Beverly Darvill

I have a field in a query that says Open or Closed. If the field is Closed
then I need to force the user to fill in another field with a date before
they leave the form, otherwise it can be a null value.

I tried Iif [Status]=CLOSED, NOT NULL but it asks for a date even if the
Status field is Open

In programming I would have used an If Then Else statement but I am not sure
how to phrase it in Access.

Thanks
 
You cannot use the validation rule of the control on the form, because the
user may never visit that control.

You could use the validation rule on the table itself, or you could use the
BeforeUpdate event procedure of the Form.

1. Open the table in design view.
2. Open the Properties sheet.
3. Beside the Validation Rule in the Properties sheet, enter:
([Status] Is Null) OR ([Status] <> 'Closed') OR ([DateClosed] Is Not
Null)

Note: Don't use the Validation Rule in the lower pane of table design:
that's the rule for a field, not for the table.

The rule can be satisified 3 ways:
- If Status is blank, it's happy.
- If Status is something other than Closed, it's happy.
- If neither of the above apply, the only way it can be satisfied is if the
DateClosed field has a value.
 
Hello Beverly,

You can add some code ad the unload procedure of your form like this.

Private Sub Form_Unload(Cancel As Integer)

If IsNull(Me.TEST) Then
MsgBox ("Message missing ....... "), vbCritical
Cancel = -1
End If

where me.TEST is a textbox where you wanted the date be filled in.

grtz


End Sub
 
Back
Top