I need help with Editing dates

[Guest]

I have a table called "tblMedication" with the following situation: For every
patient there has to be 2 types of medications per treatment date ex:

PatientID DateofTreatment MedicationType
1 1/1/2006 Med1
1 1/1/2006 Med2

For every date there should be 2 records only. I am trying to perform the
following action:
Editing existing dates in the "DateofTreatment" field:
Upon editing the old date to new one, I want to check if the new date exist
or not. If it does, then, I will undo the change i.e.(set the date back to
the original date) and generate a message saying that the date can not be
changed because it will creat duplicate records. If the new date does not
exist in the table, then accept the new date for both recordsi.e.(Med1and
Med2). any ideas
Thanks
Al

 

[Allen Browne]

You can do this without any code, by adding a new unique index on the
combination of DateOfTreatment + PatientID.

Steps:

1. Open your table in design view.

2. Open the Indexes dialog (View menu.)

3. Enter a new name for the index the first field.

4. In the lower pane of the dialog, set Unique to Yes.

5. On the next pane of the dialog, leave the Index Name blank, but choose
the 2nd field. The dialog now looks something like this:
DateOfTreatmentPatientID DateOfTreatment
PatientID

 

[Allen Browne]

Hmm. For any combination of PatientID and DateOfTreatment, you want to
ensure there are always exactly 2 records?

Can't imagine how to get the engine to do that for you, so you will need to
write code to handle all inserts, edits, and deletes to ensure this business
rule is applied.

Inserts:
Use the BeforeUpdate event of the form to check that the record does not
already exist. Use the AfterInsert event of the form to create the matching
record (by executing an Append query statement.)

Edits:
Use the BeforeUpdate event of the form to check that the record does not
already exist. Use the AfterUpdate event of the form to execute an Update
query to change the matching record.

Deletes:
Use the AfterDelConfirm event of the form to execute a Delete query to
delete the matching entry as well.

 

[Allen Browne]

Hmm. For any combination of PatientID and DateOfTreatment, you want to
ensure there are always exactly 2 records?

Can't imagine how to get the engine to do that for you, so you will need to
write code to handle all inserts, edits, and deletes to ensure this business
rule is applied.

Inserts:
Use the BeforeUpdate event of the form to check that the record does not
already exist. Use the AfterInsert event of the form to create the matching
record (by executing an Append query statement.)

Edits:
Use the BeforeUpdate event of the form to check that the record does not
already exist. Use the AfterUpdate event of the form to execute an Update
query to change the matching record.

Deletes:
Use the AfterDelConfirm event of the form to execute a Delete query to
delete the matching entry as well.