Question Re: Field Completion Contingent Upon Other Field's Completion

[Debbie]

Hello,

I've been teaching myself MS Access and have done okay so far, but I've run
into a situation I can't seem to resolve. I have a table in which I want to
make one field's completion contingent upon another field's completion.
Here is an example of what I'm trying to accomplish:

Supervisor's Initials Date Form Was Completed
MFF 11/23/04

I would like to have completion of the date field contingent upon completion
of the supervisor's initials field first. As I have many such paired fields
as this (this is a list of forms that are submitted at various times
throughout a process), making the initials field a "Required" entry doesn't
solve the problem, as the user cannot save the record upon entering his/her
initials and date for a specific form. The supervisors need to be able to
return to update the record as the forms are completed and submitted.

I also tried data validation rules but didn't have much success with that
either, as these entries are conditional as described above.

If anyone has any suggestions as to how to accomplish this, I would very
much appreciate your input.

Thanks,

Debbie

 

[Allen Browne]

To insist that the date has a value if the Supervisor's Initials does:

1. Open the table in design view.

2. Open the Properties box.

3. Beside the Validation Rule, enter:
([Supervisor's Initials] Is Null) OR ([Date Form Was Completed] Is Not Null)

Note that this is the Validation Rule for the table, not that of a field (in
the lower pane of table design).

 

[Debbie]

Dear Allen:

Thank you so much for your prompt response. I really appreciate your help.
I knew about validation rules for tables vs. fields, and I knew I would need
Is Null and Is Not Null, but I just couldn't seem to get the expression set
up right. Thanks to your help, my database is now working flawlessly.

Best regards,

Debbie

P.S. I tried to send this directly to you only, but it was returned.

To insist that the date has a value if the Supervisor's Initials does:

1. Open the table in design view.

2. Open the Properties box.

3. Beside the Validation Rule, enter:
([Supervisor's Initials] Is Null) OR ([Date Form Was Completed] Is Not Null)

Note that this is the Validation Rule for the table, not that of a field (in
the lower pane of table design).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello,

I've been teaching myself MS Access and have done okay so far, but I've run
into a situation I can't seem to resolve. I have a table in which I

want

to
make one field's completion contingent upon another field's completion.
Here is an example of what I'm trying to accomplish:

Supervisor's Initials Date Form Was Completed
MFF 11/23/04

I would like to have completion of the date field contingent upon completion
of the supervisor's initials field first. As I have many such paired fields
as this (this is a list of forms that are submitted at various times
throughout a process), making the initials field a "Required" entry doesn't
solve the problem, as the user cannot save the record upon entering his/her
initials and date for a specific form. The supervisors need to be able to
return to update the record as the forms are completed and submitted.

I also tried data validation rules but didn't have much success with that
either, as these entries are conditional as described above.

If anyone has any suggestions as to how to accomplish this, I would very
much appreciate your input.

Thanks,

Debbie

 

[Tim Ferguson]

Supervisor's Initials Date Form Was Completed
MFF 11/23/04

I would like to have completion of the date field contingent upon
completion of the supervisor's initials field first. As I have many
such paired fields as this (this is a list of forms that are submitted
at various times throughout a process),

The key phrase for me here is the one about "many such paired fields" --
this sounds like an opportunity for Getting The Design Right. I wonder if
you would not be better off having a table for Approvals, with a FK into
the original Forms table an ApprovalLevel field both making a compound PK,
and the Supervisor and CompletionDate each set to Required.

Especially if you choose suitable values for ApprovalLevel, then it becomes
easy to list the current state of any particular form, how many forms have
passed AdvancedSecondLevel and so on.

Just a thought, since this the TablesDbDesign group...


Tim F