validation rule based on another table's field

[Guest]

I want to set up my form, so that, if a person answers yes to a field on
another table, they cannot enter information into a separate table. Is this
possible? I tried "building" a validation rule using iif and
[table.fieldname]=YES, but that didn't work. Maybe I set up the code
incorrectly.

 

[Tim Ferguson]

I want to set up my form, so that, if a person answers yes to a field
on another table, they cannot enter information into a separate table.
Is this possible? I tried "building" a validation rule using iif and
[table.fieldname]=YES, but that didn't work. Maybe I set up the code
incorrectly.

You can create very complex CHECK constraints using ADO and DDL; but often
these requests actually relate to a design problem. Can you post more
details of what you are actually trying to do?


Tim F

 

[Guest]

I am a creating a database to keep track of therapies patients received. I
have each therapy in a separate table (for field name simplification, since
the same information is collected on different therapies). When physicians
fill out the form, I want to make sure that if they answer "Yes" for the
field "Was therapy A given?" (in table A), then the field "Was therapy B
given?" automatically goes to "No" and no details can be entered into table
B.

 

[Tim Ferguson]

I am a creating a database to keep track of therapies patients
received. I have each therapy in a separate table (for field name
simplification, since the same information is collected on different
therapies). When physicians fill out the form, I want to make sure
that if they answer "Yes" for the field "Was therapy A given?" (in
table A), then the field "Was therapy B given?" automatically goes to
"No" and no details can be entered into table B.

Really bad idea: check out normalisation methods or read any dozen or so
posts in this newsgroup. May I suggest another approach:

Patients(*RecordNumber, FName, LName, Address, etc etc)
/* you've probably already got this!!
*/

Therapists(*TxNum, Name, Specialty, DollarsPerHour, etc etc)
/* to be honest, I'd probably include all medical professionals
in this, e.g. specialists, GPs, Home nurses etc etc
*/

Referrals(*PtRecordNum, *TherapistNum, DateOfRef, ReferredByWhom, etc)
/* the PK is made up of two fields so that the same patient can only
have one active referral to the same therapist at a time.
*/

Sessions(*PtRecordNum, *TherapistNum, DateOfSession, DoneByWhom, etc)
/* the combination (PtRecordNum, TherapistNum) is a FK into the
referrals table, so that you can't have sessions that don't
belong to a specific referral
*/


This seems to cover all the things that you mentioned above, although my
guess is that it's only a small subset of what you really need to know.
Isn't it generally easier to build this into your normal medical records/
clinical information system?

All the best


Tim F

 

[Guest]

I just had to clean up a SAS database that was NOT normalized, so I
understand the headaches that are involved without normalization, but I am
the only one that will be dealing with the database design, queries, etc., so
I set it up in a way that makes sense to me, and allows me easily add on as
needed. There isn't any duplicate data in the tables; Specific data is only
being captured once.

I just need to know if the following scenario is possible: A Field X's
options are listed in a drop-down box. If Field Y has a certain value, then
it limits the options available in the drop down box for Field X. Basically a
If-then scenario for validations - but can it be done easily?

 

[Tim Ferguson]

If Field Y has a certain value, then
it limits the options available in the drop down box for Field X.
Basically a If-then scenario for validations - but can it be done
easily?

Use the AfterUpdate event of one listbox (or combo etc.) to adjust the
RowSource of the other listbox. Look up google for "cascading combo boxes"

HTH

Tim F

 

[Guest]

Thank you so much for all of your help. I will try this on Monday.