Conditionally required field

[Guest]

I have a form where DueDate and Status are not required, but if one is filled
in the other must also be filled in. Is there code I can add to each control
to make sure that if one of them has a value, the other becomes a required
field?

Thanks!
Mary

 

[Carl Rapson]

I have a form where DueDate and Status are not required, but if one is
filled
in the other must also be filled in. Is there code I can add to each
control
to make sure that if one of them has a value, the other becomes a required
field?

Thanks!
Mary

I'd recommend you put code in the BeforeInsert (and BeforeUpdate, if
necessary) events of the form to check for that. Something like:

If Not IsNull(DueDate) Then
If IsNull(Status) Then
MsgBox "Status is required!"
Status.SetFocus
Cancel = True
End If
End If
If Not IsNull(Status) Then
If IsNull(DueDate) Then
MsgBox "DueDate is required!"
DueDate.SetFocus
Cancel = True
End If
End If

Setting Cancel to True will abort the insert/update, so no record will be
added/updated.

Carl Rapson

 

[Guest]

Carl, thank you, that does keep the user from entering one without the other.
It's not displaying the message box though, just the generic Access error
message about a required field missing....any thoughts on why that's
happening.

Thanks! Mary

 

[Guest]

I put it in the Before Insert at first, when I put it in the Before Update,
it worked great, thanks!!!

 

[Guest]

I want to add another critieria in this statement to allow the DueDate field
to be null only if the Status equals "TBD".

Can you help?

Current code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not IsNull(DueDate) Then
If IsNull(Status) Then
MsgBox "If Due Date has been entered, Status is required!"
Status.SetFocus
Cancel = True
End If
End If
If Not IsNull(Status) Then
If IsNull(DueDate) Then
MsgBox "If Status has been enetered, DueDate is required!"
DueDate.SetFocus
Cancel = True
End If
End If

End Sub

Thank you! Mary

 

[Douglas J. Steele]

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not IsNull(DueDate) Then
If IsNull(Status) Then
MsgBox "If Due Date has been entered, Status is required!"
Status.SetFocus
Cancel = True
End If
Else
If Status <> "TBD" Then
MsgBox "Due Date is required for a Status of TBD"
DueDate.SetFocus
Cancel = True
End If
End If
If Not IsNull(Status) Then
If Status <> "TBD" Then
If IsNull(DueDate) Then
MsgBox "If Status has been entered, DueDate is required!"
DueDate.SetFocus
Cancel = True
End If
End If
End If

End Sub