Data Validation Between Two Tables

Hi there!

I wonder if someone can help me please? I have an Access 2000 database used to record the daily treatments received by patients in our Intensive Care Unit. The database consists of 2 tables;

Table 1 has these fields; [First Name], [Surname], [Hospital Number], [ICU Admission Number] (Primary Key), [Date of admission], [Date of discharge].

Table 2 has these fields; [Date], [ICU Admission number], and various treatment fields requiring a true or false response.

The tables are linked through the [ICU Admission Number] field.

I enter data in the first instance through Table 1 and then click on the '+' in the far left hand column to access Table 2 for each record (Patient), whereupon I enter the daily treatments - each record in this table accounting for 1 day on the unit (i.e. there must be no duplicate values).

When I open Table 2 I need three things to happen to try and avoid mistakes being made.

(1) The first record in the [Date] field must only be equal to the [Date of admission] in Table 1. It must not be possible to enter anything else

(2) In subsequent records in Table 2 the date must increase by one day until it equals the [Date of discharge] in Table 1.

(3) Once this value has been reached it must not be possible to enter subsequent dates or duplicate dates.


If anybody can help me with this query I would be delighted to hear from you. I have tried fiddling around with Validation rules but have had no luck.

If you can help then that would be fantastic.

Best regards
Usr33t