Automatic date problem

[usr33t]

Dear all,

I have been provided with the following code to automatically enter the date in a field (TreatmentDate), based on two dates (StartDate and EndDate) entered in another table.

This is an example of how the result should look;

Table 1:
PatientID: 123456
StartDate: 01/01/2005
EndDate: 04/01/2005

Table 2:
Patient ID TreatmentDate
123456 01/01/2005
123456 02/01/2005
123456 03/01/2005
123456 04/01/2005

Table 1 and 2 are related through the PatientID field in a one-to-many relationship.

With the current code, I can achieve this to some extent, however, in Table 2 I get the following result in the TreatmentDate field (i.e. two instances of each date in the range);

Table 2:
Patient ID TreatmentDate
123456 01/01/2005
123456 01/01/2005
123456 02/01/2005
123456 02/01/2005
123456 03/01/2005
123465 03/01/2005
123456 04/01/2005
123456 04/01/2005

If anyone could examine the following code and suggest alterations that may fix this problem I would be extremely grateful.

Many thanks and best wishes.
Russell
(usr33t)

Public Function StartTreatment(lngVisit As Long) As Date

Dim dteStart As Date
Dim dteEnd As Date

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

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

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

End Function

 

[Bat17]

the code looks OK can you post the code that you are using to call it and populate the table

Peter

 

[usr33t]

Thanks Peter,

Table 2 is set up as a sub-form within a main form based around Table 1. To call the procedure I have entered the following in the Default Value property for the TreatmentDate field in Table 2:

=StartTreatment([VisitID])

Something that may help further in explaining the problem is if you have a look at thread headed "Restricting data entry based on the contents of another field" in the "Tables" forum which was my original request for help with automating the date entry in Table 2.

Many thanks
Russell

 

[Bat17]

The code seems OK so it looks as if the form is updating the Default Value before it finishes saving the record which would give this duplicate effect.
Can't see how to get around it at the moment but maybe Scott will have some ideas.
Another possibility would be to use code to write the dates to table2 then refesh the subform to show the new data but You would need a button then to run the code. You could lock the dates on the subform then to stop anyone fiddeling with them.

Peter