Restricting data entry based on the contents of another field

[usr33t]

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

 

[ScottGem]

First your structure should be like this:

tblVisit
VisitID (PK Autonumber)
PatientID (FK)
StartDate
Enddate

tblTreatments
TreatmentID (PK Autonumber)
VisitID (FK)
TreatmentDate
TreatmentTypeID (FK)

In this way, you can setup a Visit with a date range and then list the treatments by date. You could then create a Main form bound to tblVisit and a subform bound to tblTreatments.

You could then create a Custom function to set the default for TreatmentDate

Public Function StartTreatment(lngVisit as Long) As Date

Dim dteStart as Date
Dim dteEnd As Date

dteStart = DLookup("[StartDate]","tblVisits","[VisitID] = " & lngVisit)
dteEnd = DLookup("[EndDate]","tblVisits","[VisitID] = " & lngVisit)

If Dcount("[treatmentID]","tblTreatments","[VisitID] = " & lngVisit) = 0 Then
StartTreatment = dteStart
Else
StartTreatment = DMax("[TreatmentDate]","tblTreaments","[VisitID] = " & lngVisit) + 1
End If

If StartTreatment > dteEnd Then
MsgBox "Visit has ended", VbOKOnly
StartTreatment = StartTreament - 1
End If

End Function

Finally you would create a multi field unique index on VisitID and TreatmentDate to ensure only one entry per day per visit.

 

[Uncle Gizmo]

Hello,

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

Table 1:..............................................................

I must compliment you on the way you asked this question, very well done exactly what is required from question.

Other posters could learn from this as an example of a good question.

 

[usr33t]

Hi Guys,

Thanks for both your comments. Scott, I will give your elegant suggestion a go (i'm a bit a beginner to Acess in some respects) and let you know how I get on.

Uncle Gizmo, thanks for your comments regarding my question. That's very kind.

With best wishes to both of you.

Russell

 

[usr33t]

Hello Scott,

Thanks again for your help. I have examined your code and I think I can see how Access uses it - I have never played with Modules etc. to any great extent.

Presumably one would now create a new Module and enter this code? Once entered, how would I get the code to "run" as it were. I have tried fiddling around with the Default Value property of the TreatmentDate field in the tblTreatments table but to be honest I don't really know what I should be doing!

If you can help me further I would be extremely grateful.

Many thanks and best wishes
Russell

 

[ScottGem]

Yes since its a public function you would put into a Global module. I would then set the Default value of the control on the form (NOT in the table) to:

=StartTreatment([VisitID])

That should be what you need.

 

[usr33t]

Thanks again Scott,

Definitely getting somewhere now I think. Just one more thing. On your original message, in which you listed the table structure, you have "FK" in brackets after PatientID in the tblVisit table and also after VisitID and TreatmentTypeID in the tblTreatments table. I understand this means "Foreign Key". What does this mean, and also what does it involve in terms of clicks and button presses when setting up the tables (haha, told you I was a beginner!)?

Sorry to keep troubling you.

Best wishes
Russell

 

[ScottGem]

No trouble. A Foreign Key is a field in a child or related table that contains the value of the key field in the parent table. You want to identify what patient a Visit records is for. You include the PatientID as a FK in the Visits table. You can then join the Visits table to the Patients table to get other info about the patient. Save thing with the relation between Visits and treatment. In the function I gave you, you are passing the VisitID to the function so the function knows which visit to get the Start and End dates from.