Filtering, Counting and Denying

[DubboPete]

Sounds like a strange subject name, but here's what I want to do.

I have a table called BOOKINGS. In it are all the daily car bookings
- past, present and future.

Each booking is identified by DEPARTMENT (1=DA, 2=Finance, 3=Exec,
etc), and BOOKINGDATE.

I want to be able to deny any department - other than 1-DA - the
opportunity to book out one of our vehicles if the total external
department count of bookings exceeds two on any given day.

Not sure how to go about this, but I think I need to filter out the
daily bookings by date, then count all records that are not department
1, and then if the new booking is for another external department
_and_ the count of external department bookings will exceed two, deny
the opportunity to book the vehicle, as clinical support is more
important than finance wanting to go to the bank...

Hope this makes sense (it does to me... and any ideas anyone?

DubboPete

 

[Graham Mandeno]

Hi Pete

I suggest you also add a BookingID field to your table so that you can
eliminate the current booking from your query. You can then add some code
like this to your booking form's BeforeUpdate event procedure:

Dim strFilter as string
If Me.DEPARTMENT <> 1 then
strFilter = "DEPARTMENT<>1 and BookingID<>" & Me.BookingID _
& " and BOOKINGDATE=" _
& Format(Me.BOOKINGDATE, "\#mm\/dd\/yyyy\#)
If DCount("*", "BookingsTable", strFilter) >= 2 then
MsgBox "Sorry - no can do"
Cancel = True
End If
End If
End Sub

 

[DubboPete]

Graham,

That worked a treat, and I have BookingID already, however it wouldn't get
used in this instance as the record never gets created....

Thanks for your help!

Pete

Hi Pete

I suggest you also add a BookingID field to your table so that you can
eliminate the current booking from your query. You can then add some code
like this to your booking form's BeforeUpdate event procedure:

Dim strFilter as string
If Me.DEPARTMENT <> 1 then
strFilter = "DEPARTMENT<>1 and BookingID<>" & Me.BookingID _
& " and BOOKINGDATE=" _
& Format(Me.BOOKINGDATE, "\#mm\/dd\/yyyy\#)
If DCount("*", "BookingsTable", strFilter) >= 2 then
MsgBox "Sorry - no can do"
Cancel = True
End If
End If
End Sub
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Sounds like a strange subject name, but here's what I want to do.

I have a table called BOOKINGS. In it are all the daily car bookings
- past, present and future.

Each booking is identified by DEPARTMENT (1=DA, 2=Finance, 3=Exec,
etc), and BOOKINGDATE.

I want to be able to deny any department - other than 1-DA - the
opportunity to book out one of our vehicles if the total external
department count of bookings exceeds two on any given day.

Not sure how to go about this, but I think I need to filter out the
daily bookings by date, then count all records that are not department
1, and then if the new booking is for another external department
_and_ the count of external department bookings will exceed two, deny
the opportunity to book the vehicle, as clinical support is more
important than finance wanting to go to the bank...

Hope this makes sense (it does to me... and any ideas anyone?

DubboPete