Limiting Bookings

[GreenshootProgrammer]

I have Trainee, Staff, Course, and Booking tables and forms. Everything is working fine but I want to limit the amount of bookings per course to 50, how would I go about doing this? Thanks.

 

[pbaldy]

You can use a recordset or DCount() to check the table at the appropriate point. Perhaps that's when they choose the course, or when they try to add, etc.

 

[GreenshootProgrammer]

I have a hidden field with following control source:
=DCount("CourseID","tblBooking","CourseID = [cboCourseID]")

Each course is counted, how do I restrict it to 50 now?

 

[pbaldy]

Along the lines of:

If DCount(...) >= 50 Then
  MsgBox "This course is full"
End If

Probably with code to abort the selection or update, depending on where in the process you intend to check.

 

[GreenshootProgrammer]

I'm using the following code:

Private Sub cboCourseID_AfterUpdate()
If DCount("CourseID", "tblBooking", "CourseID = [cboCourseID]") > 5 Then
  MsgBox "This course is full"
End If
End Sub

I have three different bookings for the same course by three different people. So that should mean there are only two bookings left, but it is letting me make another four bookings of the same course with one of the trainee's. (I'm currently limiting it to 5 until I get it working).

 

[pbaldy]

If your form allows for multiple bookings, you'd probably want to check after both course and quantity were input, or behind a save button. Or maybe check the course when it's chosen in case it's already full, then both after quantity. This type of thing

If DCount("CourseID", "tblBooking", "CourseID = " & [cboCourseID]) + Quantity > 5 Then

Note I changed the DCount() slightly to match this format:

http://www.mvps.org/access/general/gen0018.htm

I've seen it work the way you have it, but I feel safer concatenating.

 

[GreenshootProgrammer]

I get an error when I try it that way. I was thinking of using code that checks the hidden column field to make sure it doesn't go over a certain number, would I use the following code:

Private Sub cboCourseID_AfterUpdate()
If [txtDCount] > 5 Then
   MsgBox "This course is full"
End If
End Sub

The DCount column displays the totals expected but I have to click on then away from the field for it to display the value and it doesn't update in real-time.

 

[pbaldy]

If yours worked, then this should:

If DCount("CourseID", "tblBooking", "CourseID = [cboCourseID]") + [QuantityField] > 5 Then

 

[GreenshootProgrammer]

I got the code that wasn't working working:

Private Sub cboCourseID_AfterUpdate()
If DCount("CourseID", "tblBooking", "CourseID = [cboCourseID]") >= 5 Then
  MsgBox "This course is full"
End If
End Sub

For some reason the equals sign made a big difference, thanks for all your help pbaldy, that's it working now

 

[pbaldy]

No problemo!