MS Access 365, data validation

Question

Good afternoon,

Again, I am a complete novice at this!!

I have another question regarding the holiday let database I am trying to build.

I have one table that stores the booking information and a form for entering data into it. The table fields are:

What I'm trying to achieve is: when a new booking is entered via the booking form the arrival date required is checked against existing bookings. If the date falls on the dates of another booking for the same holiday cottage an error message is given and the booking record can not be saved.

For example: If holiday cottage 1 is booked with an arrival date of 01/01/2022 for a period of 3 nights another booking for holiday cottage 1 can not be entered for 01/01/2022, 02/01/2022 and 03/01/2022. The next available date that could be entered would be 04/01/2022. I do not need it to suggest other properties if they are available.

With that in mind I have been using the 3 known fields: Property(Bookingtble_FHLtblUI). Arrival date and number of nights to validate new records.

My first attempt was to use the validation rule on the field properties for Arrival date to ensure that dates entered could not be in the past. This was: >=Date(). This worked.

I then tried to expand this so that dates added were checked against existing bookings (or so I thought) by using >=Date() And [Arrival_date]>=[Arrival_date]+[Number_of_nights]. That didn't work, got an "Invalid SQL syntax - cannot use multiple columns in a column-level CHECK constraint." error message.

So I tried [Arrival_date]>=[Arrival_date]+[Number_of_nights] in the table properties validation rule. That give me: "One or more values are prohibited by the validation rule '[Arrival_date]>=[Arrival_date]+[Number_of_nights]' set for 'Booking_tbl'. Enter a value that the expression for this field can accept" error.

This happened for dates that had no conflict with other bookings.

Am I in the right ball park? or should I be tackling this a completely different way? I did think about having a "leaving date" instead of "number of nights" and setting both the arrival and leaving dates to indexed no duplicates - this became non workable very quickly because we can have guests leaving and arriving on the same day.

I know that this is probably asked a thousand times, but how do I do it?

All and any help, criticism and guidance would be received with great gratitude.

Answer 1

The code I posted in my original reply is that for the WithinDateRange function.  You should paste it into any standard module in the database, and be sure to save the module under a name which differs from that of the function or any other object in the database.  In my case the module is named basDateStuff.

In my case the function is called in the RowSource query of a combo box to restrict the combo box's list to those rooms where the booked date ranges do not intersect with the date range defined by the start and end dates entered into the booking form, i.e. those rooms which are available for the whole of the desired data range. In the query the Nz() function is used to return a date far into the future if no end date is entered.  Consequently the booking is open ended , and will only be allowed for rooms which have no bookings on or after the desired start date.

If you want to use it to validate a range entered into a form then the form's BeforeUpdate event procedure would be an appropriate place.  To do this you should first create a query which calls the function, in the same way that the query I posted in my original reply does, referencing the controls in your form as parameters.  To detect where there is an intersection of the ranges, and the booking is therefore invalid, you'd call the Dlookup function to determine whether a row is returned by the query where there would be an intersection of the ranges.  The query would consequently be like this:

PARAMETERS Forms!YourFormName!Booking_tbl_FHtblUI LONG,

Forms!YourFormName!ArrivalDate DATETIME;

SELECT *

FROM Booking_tbl

WHERE Booking_tbl_FHtblUI = Forms!YourFormName!Booking_tbl_FHtblUI

     AND WITHINDATERANGE(Forms!YourFormName!ArrivalDate,

     Forms!YourFormName!ArrivalDate+Forms!YourForm!Number_Of_Nights,

     Arrival_Date, Arrival_Date+Number_Of_Nights);

The code to validate the dates entered in the form would be like this:

    Const MESSAGE_TEXT = "One or more of the dates selected have already been booked."

    If Not IsNull(DLookup("Booking_tbl_UI","YourQueryName")) Then

         MsgBox MESSAGE_TEXT, vbExclamation, "Invalid Operation"

         Cancel = True

    End If

You'll appreciate that I'm unable to test the above without access to your file, so cannot rule out the possibility that some debugging might be required.

Answer 2

Based on experience, I don't recommend using input masks or validation rules. They typically cause more problems than they solve and truly annoy end-users.

Instead use the after update event of the controls as well as the before update event of the form to perform your validation and cancel saving when necessary.

I'd also recommend reading up on data normalization and reviewing your design. You should have a Clients table, a Bookings table, a Properties table, ...

Answer 3

PS: I forgot to mention that intersecting ranges are prevented in the table definition by applying the following CHECK CONSTRAINT to the RoomOccupations table. This uses the same logic as the function I posted in my last reply:

CurrentProject.Connection.Execute "ALTER TABLE RoomOccupations ADD CONSTRAINT DoubleBooked CHECK((SELECT RO1.RoomNumber FROM RoomOccupations AS RO1 INNER JOIN RoomOccupations As RO2 ON RO1.RoomNumber = RO2.RoomNumber AND RO1.RoomReservationID <> RO2.RoomReservationID AND RO1.EntryDate <= RO2.DepartureDate AND RO1.DepartureDate >=RO2.EntryDate)IS NULL)"

Answer 4

Good Morning Daniel,

Thanks for this.

I have separated out the tables as far has I can, see below:

When it came to having a separate table for the guests I got stuck, I couldn't think of a way of uniquely identifying each guest with the information that we collect, that is only "first name" and "surname". Going forward I appreciate that we will have to do something about this.

What kind of expression or code would I have to use for the After and Before Update events? Are there any examples that you could point me to?

Thanks in advance

Answer 5

What I'm trying to achieve is: when a new booking is entered via the booking form the arrival date required is checked against existing bookings. If the date falls on the dates of another booking for the same holiday cottage an error message is given and the booking record can not be saved.

You might like to take a look at Reservations.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

This little demo file illustrates how to prevent double-booking by detecting intersecting date ranges.  It uses a simple room reservation database as its example, but the methodology applies to any similar booking system.  The following function is the key to it:

Public Function WithinDateRange(dtmParamRangeStart As Date, _

                       dtmParamRangeEnd As Date, _

                       dtmDataRangeStart As Date, _

                       dtmDataRangeEnd As Date) As Boolean

' Accepts: date at which parameter range starts

'          date at which parameter range ends

'          date at which data range starts

'          date at which data range ends

' Returns: True if data range intersects with parameter range

'          False if whole of data range outside parameter range

   WithinDateRange = _

       dtmDataRangeStart <= dtmParamRangeEnd And _

       dtmDataRangeEnd >= dtmParamRangeStart

End Function

The following is the model for the database.  Note that Calendar and Calendar_1 are two instances of the same table, not separate tables.