Restricting Data Entry Based On The Contents Of Another Field

Hello,

I have two tables which contain the following fields (simplified);

Table 1:
ID
Startdate
Enddate

Table 2:
ID
Unitdate
Treatment

The tables are related (one-to-many) through the ID field.

In operation, the user first enters the Startdate and an Enddate for a patients' hospital stay in Table 1. Following this, the user enters the Startdate again in the Unitdate field in Table 2 and selects a Treatment from a drop-down list. After this, the user enters the following in the Unitdate field and selects a treatment. This is repeated in consecutive order until the Enddate is reached.

The end result looks like this;

Table 1:
ID Startdate Enddate
123456 01/01/2005 04/01/2005


Table 2:
ID Unitdate Treatment
123456 01/01/2005 Ventilation
123456 02/01/2005 Ventilation
123456 03/01/2005 Haemofiltration
123456 04/01/2005 Ventilation

Does anyone know if there is any way to restrict the Unitdate field in Table 2 to only allow dates to be entered that are between the Startdate and Enddate fields in Table 1? Ideally, I would like the Unitdate to populate automatically with the dates, starting with the Startdate value and ending with the Enddate value and filling in all the intermediate dates. Also I would like to make sure that there is no way of having duplicate dates in the Unitdate field.

If anyone can help, I would be extremely grateful.

Best wishes
Russell