Field must be completed if another field is checked

[Critt]

I have a table which contains several fields - one is a check box called
"Completed" and another is a Date field called "Date Completed". How do i
get it that if I check the "Completed" box is doesn't allow you to come off
the record until you have entered the "Date Completed" field. Also you can't
enter a "Date Completed" if you haven't checked the "Completed" box

 

[Brendan Reynolds]

I have a table which contains several fields - one is a check box called
"Completed" and another is a Date field called "Date Completed". How do i
get it that if I check the "Completed" box is doesn't allow you to come
off
the record until you have entered the "Date Completed" field. Also you
can't
enter a "Date Completed" if you haven't checked the "Completed" box

Well my advice would be to get rid of the Completed field altogether. If a
record is not to be considered completed unless a date has been entered,
then the presence or absence of a date indicates whether the record is
completed or not, and the Completed field is redundant. You can easily
selected completed or uncompleted records with queries such as ...

SELECT * FROM SomeTable WHERE DateCompleted IS NULL

.... and ....

SELECT * FROM SomeTable WHERE DateCompleted IS NOT NULL

With that said, you can do the kind of thing you describe with table-level
validation rules such as "Completed IS NULL OR DateCompleted IS NOT NULL".
If you decide to do this, make sure that you are creating a table-level
validation rule, not a field validation rule - look in the properties of the
table in design view, not the properties of an individual field in the
table. Field-level validation rules can't refer to other fields like this.

Again though, my advice is not to do this at all in this instance, all of
the information you require is contained in the presence or absence of the
date, the "Completed" field is redundant and serves no useful purpose.

 

[Klatuu]

Brendon is correct. Two fields that mean the same thing is redundancy and
should be avoided.

My solution, which would ensure the correct date is entered and make it easy
on the user would be to hide the DateCompleted control (make the visible
property No) and replace the checkbox with a command button. In the Click
event of the command button:

Me.DateCompleted = Date()

 

[Critt]

Thanks I will have ago

Brendon is correct. Two fields that mean the same thing is redundancy and
should be avoided.

My solution, which would ensure the correct date is entered and make it easy
on the user would be to hide the DateCompleted control (make the visible
property No) and replace the checkbox with a command button. In the Click
event of the command button:

Me.DateCompleted = Date()

 

[Critt]

Thanks I will have a go

Well my advice would be to get rid of the Completed field altogether. If a
record is not to be considered completed unless a date has been entered,
then the presence or absence of a date indicates whether the record is
completed or not, and the Completed field is redundant. You can easily
selected completed or uncompleted records with queries such as ...

SELECT * FROM SomeTable WHERE DateCompleted IS NULL

... and ....

SELECT * FROM SomeTable WHERE DateCompleted IS NOT NULL

With that said, you can do the kind of thing you describe with table-level
validation rules such as "Completed IS NULL OR DateCompleted IS NOT NULL".
If you decide to do this, make sure that you are creating a table-level
validation rule, not a field validation rule - look in the properties of the
table in design view, not the properties of an individual field in the
table. Field-level validation rules can't refer to other fields like this.

Again though, my advice is not to do this at all in this instance, all of
the information you require is contained in the presence or absence of the
date, the "Completed" field is redundant and serves no useful purpose.