what if scenario 2

[Pammy46]

I have 2 colums in an access table one containing a status of a case CLOSED or PENDING. The other column is the date CLOSED - how can I ensure that once CLOSED option is selected by the user that the Date closed must be filled?? as the users are forgetting to put closed date in and data is lost for analysis

 

[lupins46]

You cannot control this at table level.
You must use a form for data entry.

In the Form_Beforeupdate event procedure test the field:

Private Sub Form_BeforeUpdate(Cancel As Integer)
if me.txtStatus <>"Closed" then exit sub
if isnull(me.txtDateClosed) then
msgbox "You must enter the closed date"
Cancel = true
else
snd if


End Sub

 

[dhookom]

There are some possibilities for setting table level validation. Try an expression in the table Validation Rule of:[blue]
IIf([status]="Closed",Not IsNull([DateClosed]),-1)[/blue]

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]