Limit the number of entries to a table with a specific value (preventing overbooking in airline)

[Sassaglas12]

Hi, I am currently working on a small airline management application.
I have a table containing the various technical information about the planes, including their capacity. There is a separate table which contains the flights (time, date, plane used, destination...) and another one containing the bookings (passenger info, what flight are they registered for)

I want to limit the number of entries to the "Bookings" table, so that it is impossible to add more passengers to a flight, than the capacity of the plane used on that flight. ie. I want to prevent overbooking.

Is there a way to do this without using VBA? If not could you suggest how to do it with VBA?

 

[bob fitz]

You will need some validation code in the forms before update event.
Can you show us your table relationships, or post a copy of the db.

 

[arnelgp]

Hi, I am currently working on a small airline management application.

was the db done in-house or by a consultant.
you should consider also to contact the programmer and ask him your request.

 

[Sassaglas12]

You will need some validation code in the forms before update event.
Can you show us your table relationships, or post a copy of the db.

Sure

 

[Sassaglas12]

was the db done in-house or by a consultant.
you should consider also to contact the programmer and ask him your request.

No, it is a case study type project done by myself, nothing corporate. There is no real airline behind it

 

[ebs17]

Is there a way to do this without using VBA?

Yes, of course.
The ultimate way is a validation rule in the table (entered in the design view of the table or via code, analogous
Number of bookings per flight is less than or equal to the capacity of the aircraft

Validation Rules for Access Tables

Declarative data validation in Access with Validation Rules and Check Constraints.

codekabinett.com

 

[GPGeorge]

I don't recall without looking it up. Is it even possible to apply a validation rule to a field in one table based on a value in a field in a different table?

 

[bob fitz]

Asuming

Sure

Assuming that SiegesNom holds the number of seats available before any bookings, you could:
Change the Record Source of frmBooking to

SELECT tblBooking.BookingID, tblBooking.PassengerID, tblBooking.BaggageNum, tblBooking.FlightID, tblAvion.SiegesNom, tblAvion.SiegesNom FROM tblAvion INNER JOIN (tblFlight INNER JOIN tblBooking ON tblFlight.FlightID = tblBooking.FlightID) ON tblAvion.AvionID = tblFlight.AvionID;

You could then use DCount() function in the forms Current event to return the number of seats already booked and then set the forms Allow Additions property to false when the DCount number is = to SiegesNom.

 

[The_Doc_Man]

I don't recall without looking it up. Is it even possible to apply a validation rule to a field in one table based on a value in a field in a different table?

I recalled (and found) an Allen Browne article that includes cross-field validation. But the easiest and clearest way to do this WOULD involve some VBA code in the booking form's BeforeUpdate event.

Microsoft Access tips: Validation Rules

How and when to use Validation Rules on your fields and tables, in Microsoft Access databases.

allenbrowne.com

 

[arnelgp]

try to add some flight numbers and go to booking form.
also see the new query i made.

 

[GPGeorge]

I recalled (and found) an Allen Browne article that includes cross-field validation. But the easiest and clearest way to do this WOULD involve some VBA code in the booking table's BeforeUpdate event.

Microsoft Access tips: Validation Rules

How and when to use Validation Rules on your fields and tables, in Microsoft Access databases.

allenbrowne.com

Limitations​

You cannot use a validation rule where:

• You want to call user-defined functions, or VBA functions beyond the ones in JET such as IIf() and Date().
• The user should be able to bypass the rule.
• The expression is too complex.
• The expression involves data in other records or other tables. (Well, not easily, anyway.)

So there may be a complicated way to do it, according to Allen Browne. I'd rather be half right than half fast.

 

[ebs17]

I also mentioned a source. Philipp Stiefel (@sonic8) is also very trustworthy.

What always surprises and irritates me is that some VBA coding is preferred to real database work (rules at table level and therefore comprehensive).
You will soon see that unique indexes are no longer used to prevent duplicates or that the use of the set referential integrity is going out of fashion - you can check everything using VBA...

I also find the question about not using VBA with the answer to VBA not very precise, rather answered in the opposite way.

 

[Pat Hartman]

To begin with, you need to add a column to the flight table to contain the maximum number of passengers for this particular flight. Then, in the BeforeInsert event of the Booking table subform, you count the existing passengers and cancel the BeforeInsert event if the flight is also full.

If DCount("*", "tblBooking", "FlightID = " & Me.FlightID) >= Me.Parent!SeatCount) then
    Msgbox "This flight is full.  Please try a different one.",vbOKOnly
    Cancel = True
    Me.Undo
    Exit Sub
End If

In the real world, airlines overbook. So, you might want to think about that concept. That would require additional columns in the Booking table to handle the overbooking.

 

[isladogs]

I have an example app that you can adapt for this purpose:

Limit the Number of Records

This is a simple application demonstrating how the number of records in a subform can be limited to a specified value

www.isladogs.co.uk

Related video:

 

[arnelgp]

see again the form frmBooking.

 

[CJ_London]

I’ve not looked at the requirement in detail but if I was developing this for a client I would be checking a number of other things:

• How are cancellations to be treated
• What if some seats are out of commission for repairs
• What if there are additional crew requiring passenger seats
• What about special needs requiring extra legroom for example
• Are there different classes of seats (first/business/economy/etc)
• Have you considered booking seats rather than just a number

 

[Sassaglas12]

see again the form frmBooking.View attachment 113540

I am very thankful for your aid as well as to all the other contributors! I've fallen terribly ill shortly after writing this post, so please excuse me for the delayed response. Showing the total booked number straight away in the form does seem like a good solution for my use case (it is assumed that the employee of the company will compare the capacity and the nb of reservations).