By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,704 Members | 1,861 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,704 IT Pros & Developers. It's quick & easy.

Double Booking on Microsoft Access.

P: 1
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
Dec 6 '07 #1
Share this Question
Share on Google+
3 Replies


P: 5
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
Dec 6 '07 #2

100+
P: 365
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("DentistID","tblAppointments","DentistID = [forms]![frmAddAppointment]![DentistID] AND AppointmentDate = [forms]![frmAddApoointments]![AppointmentDate] AND AppointmentTime = [forms]![frmAddApoointments]![AppointmentTime]")

if IsNull(DentistCheck) 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
Dec 6 '07 #3

P: 1
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
Dec 2 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.