Complex booking form using VBA code

[Wildster]

Hi,

I have a form in Access 2000 running Windows XP with the following fields: -

Booking_ID
Date
Person_Name
Booking_Type
8-9am (y/n checkbox)
9-10am (y/n checkbox)
10-11am (y/n checkbox)
11-12am (y/n checkbox)

I'm trying to write some VBA code which for any given date only allows a max of 3 bookings each for the two timeslots 8-9am and 9-10am, and a max of 5 bookings each for the timeslots 10-11am and 11-12am, but only when the Booking_Type is set as 'holiday', if the Booking_Type is anything else then there can be unlimited bookings for each of the individual timeslots.

Lets say that on 15/10/08 there have been 3 holiday bookings for the time slot 8-9pm and only 1 holiday booking for 9-10am and someone tries to make a booking for 2 holiday slots (8-9am & 9-10am), I would want a message appearing which states that the 8-9am slot was fully booked up (as already 3 bookings had been made for that time slot on that date).

Basically I'm creating a holiday booking database where there is a holiday threshold where only 3 holidays can be booked from 8-10am and only 5 bookings can be made from 10-12am each day.

Any help would be very much appreciated,

Regards,

Dean

[sierra7]

Hi Dean

First, don't use fieldname "Date" in your table as this is a reserved word. Use Bdate (for Booking Date) or something instead.

You will need another table to hold one record for each 'slot' booked. Before adding an extra record to book a new 'slot' on a particular day , you will need to count how many already exist and evaluate whether another is allowable.

I suggest you define SlotType as = 1 for 8-9am and SlotType = 2 for 9-10am.

The structure of the new table table: Name = tblSlots

 
SlotID            Autonumber     'Unique, for internal indexing
SlotType Integer             'Holds 1 or 2
Sdate         Date                 'Date of time slot
Booking_ID    Long Integer     'needed for deleting!
 

On the Before_Update event of the checkbox for [8-9am] you need some code like;

 
Private Sub Ctl8_9am_AfterUpdate()
'first check a date has been entered
If IsNull(Me.Bdate) Then
    MsgBox "You must enter a date before booking the time", vbCritical, "No Date!"
    Me.Ctl8_9am = False
    Exit Sub
End If
 
'Application.SetOption "Confirm Action Queries", False
If Me.Booking_Type Like "H*" Then 'only check if Holiday
    If Me.Ctl8_9am = True Then
        'Check current situation
        If DCount("[SlotID]", "tblSlots", "[SlotType]=1 and [Sdate]=#" & Format(Me.Bdate, "mm-dd-yyyy") & "#") >= 3 Then
            MsgBox "There are already 3 Holidays booked for this time on this date", vbCritical, "Fully Booked"
            Me.Ctl8_9am = False
            Exit Sub
        Else
            'Confirm booking by adding new record
            DoCmd.RunSQL "INSERT INTO tblSlots ( SlotType, Sdate, Booking_ID )" & _
                " Values( 1,#" & Format(Me.Bdate, "mm-dd-yyyy") & "#, " & Me.Booking_ID & ");"
        End If
 
    Else
        'Removing a booking
        DoCmd.RunSQL "DELETE * FROM tblSlots WHERE Booking_ID =" & Me.Booking_ID & " and [SlotType] =1 ;"
    End If
End If
End Sub
 

I have left warnings on while you are testing but you can set them off once you have it all working.

I don't know if I have captured exactly what you want but it must be something along these lines. You will have to repeat this code for the 9-10am slot, not forgetting to change the Count and SlotType.

S7

[Wildster]

Hi S7,

Thanks for your quick response, the majority of it makes sense and it looks as though it covers everything needed. However when I click on the checkbox which contains the code is brings back the error message: -

MS Access can't append all the records in the append query....and it didn't add 1 record to the table due to key violations

The code it is getting stuck on is as follows: -

DoCmd.RunSQL "INSERT INTO tblSlots ( SlotType, Sdate, Booking_ID )" & _
                " Values( 1,#" & Format(Me.Bdate, "mm-dd-yyyy") & "#, " & Me.Booking_ID & ");" 

Just to confirm, the main tables being used look like this: -

tblBooking
-Booking ID
-Bdate
-PersonID (links into an Person Table containing info on the Person)
-Booking Type (i.e. holiday, sick, AWOL etc)

tblSlots
-SlotID
-SlotType
-SDate
-BookingID

And the form in question contains all data in the tblBooking table along with checkboxes for the possible slot times i.e. 8-9am etc.

Quick question: What is the use of SDate in tbleSlots when there is a BDate in tblBooking, I would have thought that having two dates is duplicating data?

Thanks,

Dean

[sierra7]

Hi Dean

Last things first!; You need a date in tblSlots so that you know how may Type 1 there are for a given date. I called it [Sdate] whereas it's copied from [Bdate] so could be called [Bdate]. It's just a habit; I try and name all my dates differently so I know where they come from and I don't get mixed up (on a biggger project).

Just reading your post you have three different spellings for BookingID, Booking ID and Booking_ID. Choose one and stick with it!

As a general rule I abhor spaces in field names; it stops this sort of problem. Also, I didn't want to winge at the time but I don't like fieldnames starting with Numbers. e.g [8-9am] Out of interest I continued to follow your schema and to see if Access would allow it and I note that when the form is built Access prefixes these contols with 'Ctl' e.g [Ctl8-9am]

This does not necessarily account for your error message which implies that you have an index on one of the fields and are trying to add a duplicate value. This may be, or it could just be that Access 'has got it's knickers in a twist' because of an erroneous field name.

My test-bed seems to works althought I have not tried to break it.
I tried to use the Before_Update event for the test but got too many error messages about cancelling and the After_Update did not seem to mind the check box being re-set if the numbers where exceeded.

S7