Required field based on value of another field

  • Thread starter Thread starter Grace
  • Start date Start date
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
 
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
 
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
 
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
 
Back
Top