Double Booking on Microsoft Access.

[Keyboyx]

I am creating a Dentist Booking form, for my A level project.

I have the following Tables:

Appointments
Appointment ID
Dentist ID
Patient ID
Appointment Date
Appointment Time
Appointment Type

Dentists
Dentist ID
Dentist Surname

Nurses
Nurse ID

Patients
Patients ID
Forename
Surname
Address line 1
Address line 2
Postcode
Phone number

Basically my problem is that in my data input forms, that i have created I need it to bring up an error message when a dentist is about to be double booked.

e.g.

If dentist 1 has an appointment on the 22/10/08 at 10:00 then that dentist can not be booked on that date and time again. but Dentist 2 and 3 can be.

I have tried to work this out for many hours and havnt really got close.

The system also has the following relationships

The avialble table is from my attempt to do this..it is irrelevant really it just contains the list of available times.

Thank you alot in advance to anyone who can help me, im sorry if there is repeated problems like this and threads all over the place. but i dont really have time to be searching for them and adapting them to my system.

if you feel the need to contact me :jwkey@hotmail. com

[Armenante]

Lol i asked the same question in this thread

http://www.thescripts.com/forum/thread745118.html

tried to convert the VBA from another thread but it is harder than it looks so sorry i can not help you, but i have exactly the same problem. So anyone that can help this guy out i would be greatful because it would help me out too.

Tom

[Dan2kx]

if this information is selected from a form then you could use a dcount as such:
(dcount(what to count, where from, when a=b) (just in case you didnt know)

dim DentistCheck as integer

DentistCheck = DCount("Dentist ID","tblAppoint ments","Dentist ID = [forms]![frmAddAppointme nt]![DentistID] AND AppointmentDate = [forms]![frmAddApoointme nts]![AppointmentDate] AND AppointmentTime = [forms]![frmAddApoointme nts]![AppointmentTime]")

if IsNull(DentistC heck) or dentistcheck = 0 then
Exit sub (or add record or whatever)
else
Msgbox "this dentist already has a booking"
end if

That basically searches your appointment table and if it returns any records it will count them and then if there are any it will avoid the double book

Just a little tip for ya... take the spaces outta ya field names cos it does cause problems when coding, and prefix ur objects as not to confuse access...

Dan

[noelf21]

just wondering would this also work for mine?

I have the following table booking, member, discount and facility

Booking table consists of Booking ID, Booking Time, Booking Date, Member ID, Facility ID, Discount ID