Hi Everyone,
I have got some way through a db for my daughter, I'm not very good with Access. I have a bookings table called tblBookings, which is like a hotel room bookings system. The three relevant fields are the customer code [CusCode] the arrival date [Arrive] and the departure date [Depart]. CusCode is a short text field and Arrive and Depart are Date/Time fields, although time is not to be considered. The only related table is tblCustomers only to reference the property address.
I want to prevent overlapping bookings being entered and to start with, I have a data entry form to enter the above fields and a 'Save Record' button. I wanted to use BeforeUpdate to check if the departure date (Depart) of the new proposed booking overlaps a previously confirmed booking already in the table. I've spent ages building a query that finds the previously accepted booking's depart date so that I can later go on the see if the new proposed booking's depart date falls within the previously saved one. Even later I can construct a similar query that finds the same information about any following accepted bookings and then see if the new booking's dates overlap past or future dates.
Then I found out that BeforeUpdate can't use a query and I can't find a BeforeUpdate parameter for a macro.
The sql that I constructed to find the most recent accepted booking in the past is this;
Code:
SELECT TOP 1 tblBookings.CusCode, tblBookings.Depart
FROM tblBookings
WHERE tblBookings.Depart <= forms!frmBookingsEntryForm.Depart AND tblBookings.CusCode = forms!frmBookingsEntryForm!Cuscode
ORDER BY tblBookings.Depart DESC;
Can anyone point me forwards to construct a macro that I can refer to in the form's BeforeUpdate property please?