Mandatory field based on other field's value

[jaffar2000]

Hi,
I have a field named status (pick-list type) and a field named apply_date (date type).
How do I make the apply_date field to be mandatory when the status field selected value is 'X' for example.
Also presenting a message to the user if he did not set the would be very helpful.

Thanks,
Jan.

 

[KenHigg]

In the before update event check the status, if it meets the requirement, check the apply date, if it is null warn the user and cancel the event.

Will this work?

 

[jaffar2000]

No.
I even tried to make it more simple but not working for me.

Private Sub status_AfterUpdate()
If Me.status.Value = "X" Then
MsgBox "you must supply the apply date"
End If
End Sub

What did I do wrong here?
Suggestions?

 

[KenHigg]

For starters it looks like you are using the wrong event.

 

[jaffar2000]

Why the wrong event, don't have I have to check the status after it was updated?
Can you point me to the correct solution?

 

[KenHigg]

I think you are close. So if you try to check after the update then you'll after to write code to go back into the table and correct something. So you need to do the check before the table gets updated. I think the pc you are missing is the 'cancel event' if the test fails:

Private Sub status_BeforeUpdate()
If Me.status.Value = "X" Then
MsgBox "you must supply the apply date"
Docmd.CancelEvent
End If
End Sub

???

Edit: I usually put this in the form before update event

 

[jaffar2000]

not working.
I wrote the code in the BeforeUpdate.
I get nothing.

 

[KenHigg]

If you didn't even get a msg box the your test is flawed. What kind of control is status and what kind of data type is it?

 

[jaffar2000]

Status is a combo box (drop down list) field with text data type.

 

[KenHigg]

I assume it's bound to the status field and does not have a default value?

 

[jaffar2000]

Those are 2 separated fields with no connection between them and both do not have a default value.

 

[KenHigg]

Hang on, let me put something together...

Edit: look at this

 

[jaffar2000]

Had help from another place and found the problem:
My file is located on a shared folder so I needed to set the location as trusted:
"Code does not run in 2007/2010 unless your database resides in a folder that has been declared a “trusted” location."