Resource Scheduling

[swarfmaker]

I'm developing a database (after a 3 year layoff, so I'm a bit rusty) to
book training resources out. I've got 3 tables, tblBooking, tblResources,
tblResourceType and have built the necessary form. If I only have the one
resource I have code that will check if the resource is free between the
requested start and end dates. This code was from Allen Browne many years
ago. I am stumped as to how I can modify this code to cope with multiple
resources of different types, e.g. I may have 3 Digital Projectors, 5
laptops, 2 cameras, etc.
As I said, I'm a bit rusty, its amazing how much you forget when you no
longer do this stuff every day.
The code I need to modify is below.

TIA
Iain, in Pudsey, UK

********Code Start***********

Private Sub EndDate_LostFocus()
'dimension variables used
Dim sdate As Date, edate As Date
Dim MySQL As String, X As Date
Dim db As Database, rst As DAO.Recordset
Dim ctlText As Control

'Me!ctlStartDate and Me!ctlEndDate are the fields from your FORM
sdate = Me![StartDate]
edate = Me![EndDate]

'this creates the query, tblBookings is your TABLE name, id is the idnumber
'of the field your searching for
'Me!cboItem is the field you choose the item from on your FORM, this assumes
'that your bookings table
'has an item name and an id for that item, also that the combo box shows the
'item name but is bound
'to the item id field.....let me know if that confused you
MySQL = "SELECT * FROM tblBooking WHERE [Cancelled]Is Null "
Set db = CurrentDb()
'open recordset based on query
Set rst = db.OpenRecordset(MySQL, dbOpenDynaset)

'this code loop will walk through the recordset for each time the item is
booked
With rst
rst.MoveFirst
Do While Not rst.EOF
'!startdate and !enddate are the field names from your TABLE
' variable x stores the current date being compared
For X = ![StartDate] To ![EndDate]
If X >= sdate And X <= edate Then
'item is booked so do something, in this case display message box and
exit
'note: put msgbox statement all on one line
MsgBox "Date Conflict, Booking for dates " & sdate & " to " & edate
& " conflicts with previous booking scheduled between " & !StartDate & " and
" & !EndDate
Me.Undo 'Cancels entry

Set ctlText = Forms![frmBookings]!StartDate 'Returns focus to Start Date
ctlText.SetFocus
Exit Do
End If
Next X
.MoveNext
Loop
End With
End Sub

*********Code End************

 

[Rob Oldfield]

You don't need to be using loops... either the one looping through the
recordset and or the one looping from the start date of each record to the
end date. That's inefficient code.

I don't have the time to set up your entire situation (partly because I
don't understand it all)... but try this... (...and this is ignoring your
question about multiple resource types for the moment... start small and
build up...)

Set up a table called Bookings with three fields: BookingID (autonumber,
pk), DateStart, and DateEnd. Add some random records on to that. These
represent bookings already made (and, yes, you could add a Cancelled boolean
field later and check it's false)

Next, a form called BookingDates. Add two text boxes StartDate and EndDate
which are going to be proposed bookings.

Next, a query:

SELECT Bookings.BookingID, Bookings.DateStart, Bookings.DateEnd
FROM Bookings
WHERE (((Bookings.DateStart)<=CDate([forms]![bookingdates]![EndDate])) AND
((Bookings.DateEnd)>=CDate([forms]![bookingdates]![StartDate])));

Call that OverlapTest.

Go back to the form and add a listbox called lstOverlap... this will show
any overlapping bookings. Set the rowsource of that to OverlapTest, and the
ColumnCount to 3. And a button where the click code just says...

me.lstOverlap.requery

Save everything.

Open the form (you'll get an error on the way in because there are no dates
to play with yet, click OK and ignore it) and enter a couple of dates... and
click the button to see any overlaps... if there are no records listed it
means that the dates that you've entered don't overlap.

Does that work for you so far?

I'm developing a database (after a 3 year layoff, so I'm a bit rusty) to
book training resources out. I've got 3 tables, tblBooking, tblResources,
tblResourceType and have built the necessary form. If I only have the one
resource I have code that will check if the resource is free between the
requested start and end dates. This code was from Allen Browne many years
ago. I am stumped as to how I can modify this code to cope with multiple
resources of different types, e.g. I may have 3 Digital Projectors, 5
laptops, 2 cameras, etc.
As I said, I'm a bit rusty, its amazing how much you forget when you no
longer do this stuff every day.
The code I need to modify is below.

TIA
Iain, in Pudsey, UK

********Code Start***********

Private Sub EndDate_LostFocus()
'dimension variables used
Dim sdate As Date, edate As Date
Dim MySQL As String, X As Date
Dim db As Database, rst As DAO.Recordset
Dim ctlText As Control

'Me!ctlStartDate and Me!ctlEndDate are the fields from your FORM
sdate = Me![StartDate]
edate = Me![EndDate]

'this creates the query, tblBookings is your TABLE name, id is the idnumber
'of the field your searching for
'Me!cboItem is the field you choose the item from on your FORM, this assumes
'that your bookings table
'has an item name and an id for that item, also that the combo box shows the
'item name but is bound
'to the item id field.....let me know if that confused you
MySQL = "SELECT * FROM tblBooking WHERE [Cancelled]Is Null "
Set db = CurrentDb()
'open recordset based on query
Set rst = db.OpenRecordset(MySQL, dbOpenDynaset)

'this code loop will walk through the recordset for each time the item is
booked
With rst
rst.MoveFirst
Do While Not rst.EOF
'!startdate and !enddate are the field names from your TABLE
' variable x stores the current date being compared
For X = ![StartDate] To ![EndDate]
If X >= sdate And X <= edate Then
'item is booked so do something, in this case display message box and
exit
'note: put msgbox statement all on one line
MsgBox "Date Conflict, Booking for dates " & sdate & " to " & edate
& " conflicts with previous booking scheduled between " & !StartDate & " and
" & !EndDate
Me.Undo 'Cancels entry

Set ctlText = Forms![frmBookings]!StartDate 'Returns focus to Start Date
ctlText.SetFocus
Exit Do
End If
Next X
.MoveNext
Loop
End With
End Sub

*********Code End************

 

[PC Datasheet]

I developed a scheduling calendar module that will do this very handily for
you. You can schedule your resources directly on the calendar and print out
the calendar. If you are interested in my integrating the scheduling module
into your database, contact me at my email address below and I will send you
a screen shot of the scheduling calendar module.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

I'm developing a database (after a 3 year layoff, so I'm a bit rusty) to
book training resources out. I've got 3 tables, tblBooking, tblResources,
tblResourceType and have built the necessary form. If I only have the one
resource I have code that will check if the resource is free between the
requested start and end dates. This code was from Allen Browne many years
ago. I am stumped as to how I can modify this code to cope with multiple
resources of different types, e.g. I may have 3 Digital Projectors, 5
laptops, 2 cameras, etc.
As I said, I'm a bit rusty, its amazing how much you forget when you no
longer do this stuff every day.
The code I need to modify is below.

TIA
Iain, in Pudsey, UK

********Code Start***********

Private Sub EndDate_LostFocus()
'dimension variables used
Dim sdate As Date, edate As Date
Dim MySQL As String, X As Date
Dim db As Database, rst As DAO.Recordset
Dim ctlText As Control

'Me!ctlStartDate and Me!ctlEndDate are the fields from your FORM
sdate = Me![StartDate]
edate = Me![EndDate]

'this creates the query, tblBookings is your TABLE name, id is the idnumber
'of the field your searching for
'Me!cboItem is the field you choose the item from on your FORM, this assumes
'that your bookings table
'has an item name and an id for that item, also that the combo box shows the
'item name but is bound
'to the item id field.....let me know if that confused you
MySQL = "SELECT * FROM tblBooking WHERE [Cancelled]Is Null "
Set db = CurrentDb()
'open recordset based on query
Set rst = db.OpenRecordset(MySQL, dbOpenDynaset)

'this code loop will walk through the recordset for each time the item is
booked
With rst
rst.MoveFirst
Do While Not rst.EOF
'!startdate and !enddate are the field names from your TABLE
' variable x stores the current date being compared
For X = ![StartDate] To ![EndDate]
If X >= sdate And X <= edate Then
'item is booked so do something, in this case display message box and
exit
'note: put msgbox statement all on one line
MsgBox "Date Conflict, Booking for dates " & sdate & " to " & edate
& " conflicts with previous booking scheduled between " & !StartDate & " and
" & !EndDate
Me.Undo 'Cancels entry

Set ctlText = Forms![frmBookings]!StartDate 'Returns focus to Start Date
ctlText.SetFocus
Exit Do
End If
Next X
.MoveNext
Loop
End With
End Sub

*********Code End************

 

[swarfmaker]

You don't need to be using loops... either the one looping through the
recordset and or the one looping from the start date of each record to the
end date. That's inefficient code.

I don't have the time to set up your entire situation (partly because I
don't understand it all)... but try this... (...and this is ignoring your
question about multiple resource types for the moment... start small and
build up...)

Set up a table called Bookings with three fields: BookingID (autonumber,
pk), DateStart, and DateEnd. Add some random records on to that. These
represent bookings already made (and, yes, you could add a Cancelled
boolean
field later and check it's false)

Next, a form called BookingDates. Add two text boxes StartDate and
EndDate
which are going to be proposed bookings.

Next, a query:

SELECT Bookings.BookingID, Bookings.DateStart, Bookings.DateEnd
FROM Bookings
WHERE (((Bookings.DateStart)<=CDate([forms]![bookingdates]![EndDate])) AND
((Bookings.DateEnd)>=CDate([forms]![bookingdates]![StartDate])));

Call that OverlapTest.

Go back to the form and add a listbox called lstOverlap... this will show
any overlapping bookings. Set the rowsource of that to OverlapTest, and
the
ColumnCount to 3. And a button where the click code just says...

me.lstOverlap.requery

Save everything.

Open the form (you'll get an error on the way in because there are no
dates
to play with yet, click OK and ignore it) and enter a couple of dates...
and
click the button to see any overlaps... if there are no records listed it
means that the dates that you've entered don't overlap.

Does that work for you so far?

Many Thanks Rob, that bit works. I can expand this to cover selected types
of resources, but how do I deal with the situation where I have multiple
items of one type of resource. i.e. I might have 6 Dell laptops in the
Computer Category, 3 Digital Projectors in the Projector Category, 5
Portable TVs in the TV Category, etc. Obviosly I would have a table for
Categories and another for equipment. Does this make sense?
Regards,
Iain, in Pudsey, UK

 

[Rob Oldfield]

You don't need to be using loops... either the one looping through the
recordset and or the one looping from the start date of each record to the
end date. That's inefficient code.

I don't have the time to set up your entire situation (partly because I
don't understand it all)... but try this... (...and this is ignoring your
question about multiple resource types for the moment... start small and
build up...)

Set up a table called Bookings with three fields: BookingID (autonumber,
pk), DateStart, and DateEnd. Add some random records on to that. These
represent bookings already made (and, yes, you could add a Cancelled
boolean
field later and check it's false)

Next, a form called BookingDates. Add two text boxes StartDate and
EndDate
which are going to be proposed bookings.

Next, a query:

SELECT Bookings.BookingID, Bookings.DateStart, Bookings.DateEnd
FROM Bookings
WHERE (((Bookings.DateStart)<=CDate([forms]![bookingdates]![EndDate])) AND
((Bookings.DateEnd)>=CDate([forms]![bookingdates]![StartDate])));

Call that OverlapTest.

Go back to the form and add a listbox called lstOverlap... this will show
any overlapping bookings. Set the rowsource of that to OverlapTest, and
the
ColumnCount to 3. And a button where the click code just says...

me.lstOverlap.requery

Save everything.

Open the form (you'll get an error on the way in because there are no
dates
to play with yet, click OK and ignore it) and enter a couple of dates...
and
click the button to see any overlaps... if there are no records listed it
means that the dates that you've entered don't overlap.

Does that work for you so far?

Many Thanks Rob, that bit works. I can expand this to cover selected types
of resources, but how do I deal with the situation where I have multiple
items of one type of resource. i.e. I might have 6 Dell laptops in the
Computer Category, 3 Digital Projectors in the Projector Category, 5
Portable TVs in the TV Category, etc. Obviosly I would have a table for
Categories and another for equipment. Does this make sense?
Regards,
Iain, in Pudsey, UK

Basically, you just need to replace the Bookings table in my example with a
query that just picks out the previous bookings... but only for items that
fall within the category that you're checking against. By referencing
something on the form that you're going to be running this code from you
should be able to pick out a category. You can then build the SQL string of
a query that *only* picks out bookings related to that category and check
how many records that returns. If the number of records returned is equal
to the number of items in that category then the booking isn't allowed.

I have a feeling I'm still missing something about your problem... if so
could you give an example of the type of situation you foresee happening?