Why not have sub routine before update of the TreatmentDate like
SubTreatmentDate_BeforeUpdate(Cancel As Integer
Dim MyDb as database
Dim MySet as recordset
Dim SQLStg as string
Set MyDb = CurrentDb
SQLStg = "SELECT TreatmentDate, Count(TreatmentDate) AS
CountOfTreatmentDate "
SQLStg = SQLStg & "FROM MyTable "
SQLStg = SQLStg & "GROUP BY TreatmentDate "
SQLStg = SQLStg & "HAVING TreatmentDate = " & TreatmentDatealexander &
";"
Set MySet = MyDb.OpenRecordSet(SQLStg)
If MySet!CountOfTreatmentDate >= 2 then
Msgbox "You already have 2 patients for this date",vbCritical
cancel = true
end if
MySet.Close
Set MySet = nothing
end sub
This will hive the number of times that date has been entered
Phil
"Laertes" <bi******@btopenworld.com> wrote in message
news:b2*************************@posting.google.co m...
Hi,
I would like to create a table that will allow only two records per
date entered. So you should be able to input as many dates as you
like, but you can have each date only twice! It's a database for
patients receiving a specific treatment (We cannot have more than 2
patients/day). I want this restriction so as to ensure we don't
overbook the rooms.
At the moment I have to different tables , in each one of which the
date field is a "no duplicates" one. I then merge the two tables using
a union query. This though is not good enough as you are not allowed
to update the records in a union query.
Any better ideas?
thnx
Laertes
