I've set up a booking system and am looking to stop the system recording double bookings.

I've 3 fields, RoomID, Date and Time that need to be searched, however I can't just simply set the properties to No Duplicates (as this would mean a booking cannot be made on the same day at 9:00 and 12:00 for example).

How can I do this?

Look at Pat Hartman's response in this thread.
http://www.access-programmers.co.uk/forums/showthread.php?t=116461

I've tried a multiple field primary key and that doesn't seem to work. I can still double book a room without the system batting an eyelid.

I can't set any of the fields to No Duplicates (as a room can be booked more than once, more than once on a particular date etc).

Really not sure how to approach this, if anyone would be good enough to give me an example I'd really appreciate it :)

Set all three fields as Primary Keys.

You could use a compound dlookup statement where the variable "response" is set equal to the result of the lookup. If the repsonse value is greater than 1 (assuming the update has occurred on your form) then it is a dup and then do an undo.

Set all three fields as Primary Keys.

Solved it, cheers. :)

If this table had a primary key say called bookedid set as autonumber and the other 3 fields also set as primary keys can someone confirm that this would still work?

Without the autonumber field set as primary key it works.

No that would not work because you could have a room booked twice because the autonumber would make it unique

How long do you book rooms for - It seems to me that you need a datebookedfrom AND datebookedto, Hotels presumably book rooms for several days at a time. Meeting rooms may be booked twice in the same day. So really you need to find a logic that prevents you making a booking during the period that a room etc is currently booked for.

I agree that in such circumstance you can't manage the rentals by using a no duplicates key - you will probably need to use before update events when you enter the booking details.

The multi field primary key however, roomNo, date/timebooked will still be necessary to provide a chronological order to the bookings.

Solved it, cheers. :)
Did you end up using the Undo command? What was your code solution if you did? Having similar issues, but not duplicates. lightray:(