Table Fields: conditional values based on another field in same ta

I have a table which includes the following fields:
Course (text)
Attended (Yes/No)
Exempt (Yes/No)
Exempt Reason (3 values in value list: Already Done, Extensive Experience,
Relevance).
(There are more fields, such as ID etc, in the table but these are the
relevant ones)

I have a form which contains a subform where the user selects the reason for
providing an exemption from doing a course. The recordsource for the subform
contains a query based on the table (really basic). The subform is a
continuous form that presents all of the courses that a participant has
registered against their profile that they must attend unless they have been
exempted (for any one of the above reasons). Next to the topic (still on the
subform) is a locked check box that indicates if the person has attended the
course. Next to the Attended check box there is a check box for indicating
the Exemption, and a textbox for the user to select the Exemption Reason.

What I want to be able to do is - restrict the ability to exempt a person
from a course that they have recently attended (the rule for the "Already
Done" value is where the course was completed prior to the release of the
database), that is they cannot check the Exempted check box in the subform,
nor can they select an exemption reason on the subform for courses that will
be attended during the life of the database.

Is there a way to do this? I'm not specifically looking for code, I'm fairly
confident I can work it out if pointed in the right direction.

So far I have tried playing around with
Validation rules on underlying table - of course this doesn't work.
Locking/Unlocking/displaying the controls on the subform by changing
properties using VB (If [Attended] = True then [Exempt].Locked = True;
[Exempt Reason].Locked = True - type of code - I realise the syntax isnt
correct, I've just abbreviated for the purposes of asking this question).
This is not successful on a continuous form. I only want to disable the
controls for those records where Attended = Null or False, not the entire
subform.

The users need to be able to see also if a course has been attended so I
can't exclude these from the list presented to the user, and just show what
is available for exempting.

Can you impose a condition on a value in a table field based on the value of
another field in the same table?
If not what do you suggest would be the best way to approach this?
Do I need to rethink the continuous form and present the data in a datasheet
type view?

Any help, suggestions etc would be gratefully appreciated
Jo

Sorry, I have posted this in the wrong forum and don't know how to move it

"Jodi P" wrote:

I have a table which includes the following fields:
Course (text)
Attended (Yes/No)
Exempt (Yes/No)
Exempt Reason (3 values in value list: Already Done, Extensive Experience,
Relevance).
(There are more fields, such as ID etc, in the table but these are the
relevant ones)

I have a form which contains a subform where the user selects the reason for
providing an exemption from doing a course. The recordsource for the subform
contains a query based on the table (really basic). The subform is a
continuous form that presents all of the courses that a participant has
registered against their profile that they must attend unless they have been
exempted (for any one of the above reasons). Next to the topic (still on the
subform) is a locked check box that indicates if the person has attended the
course. Next to the Attended check box there is a check box for indicating
the Exemption, and a textbox for the user to select the Exemption Reason.

What I want to be able to do is - restrict the ability to exempt a person
from a course that they have recently attended (the rule for the "Already
Done" value is where the course was completed prior to the release of the
database), that is they cannot check the Exempted check box in the subform,
nor can they select an exemption reason on the subform for courses that will
be attended during the life of the database.

Is there a way to do this? I'm not specifically looking for code, I'm fairly
confident I can work it out if pointed in the right direction.

So far I have tried playing around with
Validation rules on underlying table - of course this doesn't work.
Locking/Unlocking/displaying the controls on the subform by changing
properties using VB (If [Attended] = True then [Exempt].Locked = True;
[Exempt Reason].Locked = True - type of code - I realise the syntax isnt
correct, I've just abbreviated for the purposes of asking this question).
This is not successful on a continuous form. I only want to disable the
controls for those records where Attended = Null or False, not the entire
subform.

The users need to be able to see also if a course has been attended so I
can't exclude these from the list presented to the user, and just show what
is available for exempting.

Can you impose a condition on a value in a table field based on the value of
another field in the same table?
If not what do you suggest would be the best way to approach this?
Do I need to rethink the continuous form and present the data in a datasheet
type view?

Any help, suggestions etc would be gratefully appreciated
Jo