Prevent Duplicate Entries

[Hysteria86]

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?

 

[RuralGuy]

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

 

[Hysteria86]

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

 

[KeithG]

Set all three fields as Primary Keys.

 

[Rickster57]

Dups

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.

 

[Hysteria86]

Set all three fields as Primary Keys.

Solved it, cheers.

 

[Rickster57]

dups

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.

 

[KeithG]

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

 

[gemma-the-husky]

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.

 

[lightray]

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