Requiring entry in a field based on value of another field

[bgotis]

In the form I'm working on, when the status of a project as entered in a field named "Status" is "Complete," I want to required the user to enter a completion date. Otherwise, the completion date field may be left blank.

I've tried writing this as a validation rule using the =Iif expression, but I think I'm off based, and I sure could use a pointer in the right direction!

Thanks,
Mary

 

[pdx_man]

Need to write some code.

We'll be looking at the On Exit event for the controls.

First one, Status:

Public Sub Status_OnExit()
if me.Status = "Complete" then
msgbox "We need to enter a completion date"
docmd.gotocontrol "CompDate"
endif

Next CompDate:

Public Sub CompDate_OnExit()
if me.Status = "Complete" and isnull(me.CompDate) then
msgbox "You must enter a completion date"
docmd.gotocontrol "CompDate"
endif


[This message has been edited by pdx_man (edited 02-07-2001).]

 

[bgotis]

Worked!!!

Thank you *very* much!

 

[Seasider]

Re Required Entry of Date

I had the exact same situation as you and I searched this forum for a solution but did not find one to suit my problem although I have found many other solutions here and am grateful for the help.

In my case, I had a checkbox on a form that a user can check if Data Access Guidelines have been approved. The field is DAG_Appvd. Next to it is a date field, DateDAG_Appvd, in which I want to force the user to enter a date when the checkbox is checked. Conversely, I don't want the user to be able to enter a date of approval if the checkbox is not checked.

Having both of these controls is not my idea. It is a requirement for a client. I would just use a date if the decision was mine.

I think you can substitue your own field/control names based on the following code I used. As you can see, the code was entered in the Exit event of each control.


Private Sub DAG_Appvd_Exit(Cancel As Integer)
'DAG_Approved is a checkbox user can check when Guidelines have been approved
'If the checkbox is checked, that is -1, they display a message to user that
' a date of approval must be entered in DateDAG_Appvd field
If Me.DAG_Appvd = -1 Then
MsgBox "You must now enter the date of approval", vbOKOnly, "DATE REQUIRED"
' move to the DateDAG_Appvd field to enter a date
DoCmd.GoToControl "DateDAG_Appvd"
Else
' if the checkbox in not checked, that is 0, then do not go to the DateDAG_appvd
'field and set focus to the next field in the tab order
If Me.DAG_Appvd = 0 Then
Me.DateDAG_Appvd = Null
Me.BudgetAmount.SetFocus
End If
End If
End Sub

Private Sub DateDAG_Appvd_Exit(Cancel As Integer)
'if the DAG_Appvd is checked and user entered a date in the date approved field
'then use a requery to immediately update both fields
If Me.DAG_Appvd = -1 And Not IsNull([DateDAG_Appvd]) Then
Me.DAG_Appvd.Requery
Me.DateDAG_Appvd.Requery
Else
'if DAG_appvd is checked but user does not enter an approval date
'then display a message and cancel the event, including resetting
'the DAG_Appvd field back to 0. Focus is set to the next field in the tab order
If Me.DAG_Appvd = -1 And IsNull([DateDAG_Appvd]) Then
MsgBox "You did not enter an approval date!"
Me.BudgetAmount.SetFocus
DoCmd.CancelEvent
Me.DAG_Appvd = 0
Else
'If user tries to enter a date of approval while DAG_Appvd is not checked
'then display a message and cancel the whole event and set focus back to
'the DAG_Appvd field and set DAG_Appvd to unchecked
If Me.DAG_Appvd = 0 And Not IsNull([DateDAG_Appvd]) Then
MsgBox "DAG Approved must be checked before you can enter a date", vbOKOnly, "Must Check DAG Approved"
Me.DateDAG_Appvd = Null
Me.DAG_Appvd.SetFocus
DoCmd.CancelEvent
End If
End If
End If
End Sub

Hope this helps. I notice there are numerous other threads where people are having similar problems.

Seasider