How to prevent making double bookings in Access?

Hey guys,

For my A2 coursework I'm doing an appointments system in Access, I have just one very small end to tie up before I can test and evaluate the damn thing.

Basically in my Appointments table (where I put the details of appointments), I'd like to prevent double bookings by ensuring you cannot enter the same time more than once under the same date.

I have a Time field which uses a lookup to list times in 15 minute increments, and a Date field. However, it's still entirely possible for the user to make another appointment which has the same date and time combination - which is meant to be a big problem of the OLD system!

Does anyone have a solution to this problem? I don't know if there's a validation rule I can use, or something like that.

Cheers for any help!

Reply 1

teachercol

Make the time and date a composite key.

Reply 2

Absinthe

If you mean a primary key, I've done that and I've enforced referential integrity, too. I can still make double bookings.

Reply 3

teachercol

No - I mean a composite key. Go into table design. Highlight BOTH fields and click on the key.

Access will now prevent you from entering any records that have the same values for BOTH fields.

Reply 4

Absinthe

yesyes I get you now - sorry!

It still doesn't prevent me from entering the same date/time combinations?
Is it affected by the fact that I used Lookup Wizard to create a drop-down list of different time slots, for the Time field?

Reply 5

teachercol

It should prevent them from being saved to the table.

Reply 6

irfan121

Yo cuz, is it possible for some1 to put screen shots up. This would really be benifitial. Thanks
Irfan121

Reply 7

Absinthe

Thanks for your help, it's working now!
I have no idea why the composite key wasn't working before.

Reply 8

Ahmed.A98

hey, is it possible for you to send me your database that you created or something that will help me stop doing double booking in my database. i am facing problems as it is veyr annoying trying to figure something out which i do not know and i have tried everything