Dlookup / Dcount ?

[Guest]

Hi all,

I have a form with the following fields:-

StartTime = Format dd/mm/yyyy hh:nn:ss
ExpFinishTime = Format dd/mm/yyyy hh:nn:ss
Dentist = Number

StartTime is bound to my table, ExpFinishTime is calculated in the forms
underlying query.

Before the record updates I am trying to check if anyone already has an
appointment scheduled in during that particular time for that particular
dentist. So if the record being added has a starttime between those two times
for that dentist, throw up a message box.

Thanks for any help.

Taff

 

[Marshall Barton]

I have a form with the following fields:-

StartTime = Format dd/mm/yyyy hh:nn:ss
ExpFinishTime = Format dd/mm/yyyy hh:nn:ss
Dentist = Number

StartTime is bound to my table, ExpFinishTime is calculated in the forms
underlying query.

Before the record updates I am trying to check if anyone already has an
appointment scheduled in during that particular time for that particular
dentist. So if the record being added has a starttime between those two times
for that dentist, throw up a message box.

I think this is what you asked for:

Sub Form_BeforeUpdate(. . .
If DCount("*", "yourtable", _
"StartTime Between Forms!yourform.StartTime " _
& "And Forms!yourform.ExpFinishTime") > 0 Then
MsgBox "Conflict"
Cancel = True 'prevent saving record
End If
End Sub

Note that the above does not take existing finish times
into account. I.e. what if an existing appointment starts
before the new appointment, but ends after the new
appointement starts? Since the finish time is not stored in
the table and you never said how you calculate the finish
time, I can't figure this situation into the above.