Stop Double Bookings

[Noelf21]

i want to stop the user from being able to book somebody using the same facility on the same day and time but i am not sure how to go about doing this. would it be a query, or could i apply a macro to check this?

 

[spoole]

How are they booking it now. Is it through a form?

And how do you record the booking?

 

[Noelf21]

yea its done through a form and the information is stored in a table

 

[spoole]

Ok on the form is it a text box that they fill in i.e

1st text box is room number
2nd text box is start time
3rd text box finish time etc?

If it is we can use the "On Update" event to test to see if the values in the text boxes are already in the table.

 

[Noelf21]

yea its is Booking ID, Booking Time, Booking Date, Facility ID, Facility Name, Facility Cost, Member ID, Member Name, Discount ID, Discount Amount and total cost

 

[spoole]

Is there not a booking duration, or is it booked at a standard one hour block?

 

[Noelf21]

Standard one hour block

 

[speakers_86]

If the booking time is a drop down, you can change the source of the drop down to exclude times already used for the selected date.

 

[spoole]

Ok In that case we use the BeforeUpdate Event.

This is used to validate data on the form before its entered into the table.

You may need to add a cloumn to your existing table that is a concatonation of the Booking Time, Booking Date, Facility ID.

Then on your form a hidden text box that concatonates the above feilds on the form as they are entered and then use that value to test against the table.

I.e If time entered is 9am and day is today and Facility ID is 7 then the hidden concatonated field on the form would read,,,,, 9AM-2/12/09-7.

So the beforeupdate event on the hidden textbox would be used to check the table. Here is a little more on the event type.

http://www.blueclaw-db.com/access_event_programming/beforeupdate.htm



Or as speakers 86 says

 

[Noelf21]

Cheers the help is greatly appreciated mate

 

[Noelf21]

One more quick question how do i make the concatonates field

 

[spoole]

if you mean on your form then

Create a text box on the form. In the properties of the text box under "defaultValue" type

=[Booking Time] & [Booking Date] & [Facility ID ] this will fill the text box with those values concatonated.

If you mean in the table just open the table in design view and add a column called concat. In this feild store the concatonated value above.

Then your before update will use the value in the concat text box to check to see if the same value already exists in the concat field of the table.

 

[Noelf21]

So would that be a statement along the lines of

IF Text28 = Booking time Then error message