Scheduling Database

[dhofman]

I have a production database where certain items are being utilitzed throughout the day. Individuals are able to schedule (through the form onto Microsoft Outlook) the 'appointments' / 'securitization' of the items for a certain time period (user input). I am now trying to figure out if someone can click a checkbox (ASAP button) where the item will be reserved at the next possible time or times. For example:

Bob reserves the item from 8-10
Joe reserves the item from 12-2
(leaving open 10-12 and 2+)

Mary wants to use the item for 4 hrs...whenever it is free...rather than view the schedule, adjust her entries...I would like Mary to click the checkbox and the item would be reserved at the next available times (10-12 and 2-4)...Does this make sense?

I have pasted my VB code below for someone to view...

Private Sub addjob_Click()
On Error GoTo AddJob_Err
' Save record first to be sure required fields are filled.
DoCmd.RunCommand acCmdSaveRecord
' Exit the procedure if appointment has been added to Outlook.
If Me!AddedtoOutlook = True Then
MsgBox "This appointment already added to the Production Schedule"
Exit Sub
' Add a new appointment.
Else
Dim outobj As Outlook.Application
Dim outappt As Outlook.AppointmentItem
Set outobj = CreateObject("outlook.application&quot
Set outappt = outobj.CreateItem(olAppointmentItem)
With outappt
'Start time is next available time
.Start = Me!JobDate & " " & Me!JobTime
.Duration = Me!JobLength
.Subject = Me!Job
If Not IsNull(Me!JobNotes) Then .Body = Me!JobNotes
If Not IsNull(Me!JobLocation) Then .Location = _
Me!JobLocation
If Me!JobReminder Then
.ReminderMinutesBeforeStart = Me!ReminderMinutes
.ReminderSet = True
End If
.Save
End With
End If
' Release the Outlook object variable.
Set outobj = Nothing
' Set the AddedToOutlook flag, save the record, display a message.
Me!AddedtoOutlook = True
DoCmd.RunCommand acCmdSaveRecord
MsgBox "Job Added!"
DoCmd.SendObject , , , "david@energytechlabs.com", , , "New Print Request", "Hello, there is a new print request!", No
Exit Sub
AddJob_Err:
MsgBox "Error " & Err.Number & vbCrLf & Err.Description

Exit Sub
End Sub


Thanks for everyone's help in advance...

 

[scking]

I've handled this type of problem successfully in the past as buckets. A bucket being the smallest unit of measurement for scheduling. I used an array to represent the day with 8 * 2 or 16 buckets if you want to restrict the scheduler to a single workday with 1/2 hour increments. I built functions to populate the buckets based on the approved and confirmed schedule and then built functions to determine how many buckets were requested and searched the array to determine if there were that many contiguous buckets. The function could return a from/to date/time set or another value for when they did not exist.

Below is just a quickly typed concept. You will have to ensure all the types match, complete the function, and build a PopulateBucket subroutine if you want to use it.

Type TimeBucket
StartTime As Date
Available
End Type
Dim Buckets() As TimeBucket

Type SchedPart
From As Date
To As Date
NotFound As Boolean
End Type

AddToSchedule(SchedDate As Date, SchedPeriods As Integer) As SchedPart

Dim i As Integer
Dim intAdjPeriods As Integer
Dim STime As Date
Dim ETime As Date

For i = 0 To UBounds(Buckets)
If Buckets.Available Then
If IsNull(STime) Then
STime = Buckets.StartTime
End If
intAdjPeriods = intAdjPeriods + 1
Else
intAdjPeriods = 0
STime = Null
End If
If intAdjPeriods = SchedPeriods Then
ETime = Buckets.StartTime + BucketSize
AddToSchedule.From = STime
AddToSchedule.To = ETime
AddToSchedule.NotFound = False
End Function
End If
Next i
End If
Exit Function

----------------------
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------

 

[dhofman]

Thanks for the help. But im decent at programming, but your concept still eludes me...Can you possibly explain alittle further?