Create Duplicate records

[Laertes]

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

[Phil Stanton]

Why not have sub routine before update of the TreatmentDate like

SubTreatmentDat e_BeforeUpdate( Cancel As Integer

Dim MyDb as database
Dim MySet as recordset
Dim SQLStg as string

Set MyDb = CurrentDb
SQLStg = "SELECT TreatmentDate, Count(Treatment Date) AS
CountOfTreatmen tDate "
SQLStg = SQLStg & "FROM MyTable "
SQLStg = SQLStg & "GROUP BY TreatmentDate "
SQLStg = SQLStg & "HAVING TreatmentDate = " & TreatmentDateal exander &
";"

Set MySet = MyDb.OpenRecord Set(SQLStg)
If MySet!CountOfTr eatmentDate >= 2 then
Msgbox "You already have 2 patients for this date",vbCritica l
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******@btope nworld.com> wrote in message
news:b2******** *************** **@posting.goog le.com...

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

[Laertes]

Thnx a lot! It took me a while but I finally managed to figure out how
to modify your code to my requirements!!!
Laertes