Make a field mandatory when another one changes

[CW]

On our Orders form, there are controls for Order Status (a combo) and Booked
Date.
All orders start off as Pending (by default) and when the booking comes in
we use the combo to change that to Booked. At that moment the user should
complete the Booked Date field, but sometimes it is overlooked.
I would like to code it so that as soon as the Order Status changes to
Booked, the Booked Date becomes a mandatory field (with the approriate
message).
Your advice, please?
Many thanks
CW

 

[Scott Lichtenberg]

CW

You should put code in the BeforeUpdate event of the form to check the
status and determine whether to allow or disallow a blank booked date field.

If Me!OrderStatus <> "Pending" Then
If IsNull(Me!BookedDate) Then
Cancel = True 'Cancels update of the record
MsgBox "Booked date required", vbExclamation
End If
End If

 

[Klatuu]

There are a couple of ways to approach this. Assuming the Booked Date will
always be the date the combo is changed from Pending to Booked, you can use
the After Update event of the combo to enter the value for you:

Me.txtBookedDate = Date

If, on the other hand, the data entry could be done at a later date, use the
form's Before Update event to warn you if the date hasn't been entered:


With Me
If .cboStatus = "Booked" And IsNull(.txtBookedDate) Then
MsgBox "Booking Date Is Required", vbExclamation
Cancel = True
.txtBookedDate.SetFocus
End If
End With