Required field based on value of another field

G

Grace

Hello, how can I have a field required based on the value
of another field? For example, I have a field called
[CallStatus]. If the [CallStatus] is "Closed", then I
would like the "Closed by" field to be required. How
difficult is this to do?

Thank you,
Grace
 
D

Dennis Schmidt

Hi Grace,

My name is Dennis Schmidt. Thank you for using the Microsoft Newsgroups.

You could create a function that you called on the BeforeUpdate event of
the form which evaluated your control and would not allow an update when
necessary. Something like the following would be a good starting point:

If Me!CallStatus = "Closed" and Me!ClosedBy = "" then
DoCmd.CancelEvent
MsgBox "ClosedBy requires an entry"
Me!ClosedBy.SetFocus
End If

I hope this helps! If you have additional questions on this topic, please
reply to this posting.

Need quick answers to questions like these? The Microsoft Knowledge Base
provides a wealth of information that you can use to troubleshoot a problem
or answer a question! It's located at
http://support.microsoft.com/support/c.asp?M=F>.

This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use. © 2001 Microsoft Corporation. All rights
reserved.

Regards,
Dennis Schmidt
Microsoft Support
 
P

Pavel Romashkin

A field in a table can be either Required or not, so you can't alternate this.
What you should do instead is insert validation code into the
BeforeUpdate event of the form:

If Strcomp(Me.CallStatus, "Closed", vbTextCompare) = 0 And
IsNull(Me.ClosedBy) Then
MsgBox "You must enter the Closed By information."
Cancel = True
End If

Cheers,
Pavel
 
G

Gerald Stanley

Assuming that the fields [CallStatus] and [Closed By] are
controls on a form, try something along the following

Private Sub Form_BeforeUpdate(Cancel As Integer)
If [CallStatus] = "Closed" And IsNull([Closed By]) Then
MsgBox "[Closed By] Is Manadatory", vbOkOnly
[Closed By].SetFocus
Cancel = True
End If
End Sub

Hope That Helps
Gerald Stanley MCSD
 
Top