First - a big thanks for help on my last problem.



Ive created a room booking database.  The problem is that I dont know how 

to check to make sure the user isnt booking the same room on the same day 

for the same time.

i.e. room 401 time 10.00 to 10.30 on 12/12/01

Please help me to display an error message if the user tries to repeat 

the above booking.  There will be multiple bookings per day per room.

Thanks

Dear Stephan,



<< Ive created a room booking database.  The problem is that I dont know 

how to check to make sure the user isnt booking the same room on the same 

day for the same time, i.e. room 401 time 10.00 to 10.30 on 12/12/01 >>



This is not too difficult to logically think it out if you take it one 

step at a time.  It just involves a lot of IF statements.



First, the user enters a date on the form.  The "On Update" event for DATE 

would then count the number of records that have that same date.  If zero, 

the user is home free.  If not zero, then the next IF statement comes.



Second, the user would enter the room number.  Why not the time?  Because 

if the user picks an empty room, the time is a moot point, but if the user 

enters a time first, then the program needs to know what room.  If the 

room is empty all day, then the user is home free.  If not, then the next 

IF statement comes.



Third and fourth, the user enters the start time and the end time needed.  

Now you loop through all the meetings for that date and room (roughly):



For i = 1 to {Number of meetings for that date and room counted}



     Check the front end.  If PROPOSED START TIME >= MEETING START TIME(i)

     and PROPOSED START TIME < MEETING END TIME(i) THEN you have a problem.

     Inform user that a meeting is in progress during that start time and

     to pick a new start time, room, or date.



     Check the tail end.  IF PROPOSED END TIME > MEETING START TIME(i) and

     PROPOSED END TIME <= MEETING END TIME(i) THEN you have a problem.

     Inform the user that a meeting is already scheduled to start in that

     room there during your proposed meeting!  Suggest to shorten your

     meeting duration to end before the other meeting starts, or change

     room and/or date.



Next i



Lastly, add a check to make sure that PROPOSED START TIME >= BUILDING OPEN 

TIME and PROPOSED END TIME <= BUILDING CLOSING TIME.  Those two numbers 

can be constants or an array of 5, 6, or 7 (depending if you have 

different daily building hours or not).



That's rough, but I hope you get the idea.



Greg