Help creating schedule

[sean]

Hi all,

I want to create a run schedule for our companies production lines. I
have a unbound main form with a combo box from which I select a
production line and 1 text box to hold the day's scheduled start time.
In a subform (set to continuous), from a combo box I can pick jobs/
tasks to do. Each job/task has time fields telling me how long to
complete the job (one in hours and one in minutes).

So for each record in the subform, I want to record each job's
scheduled start and end times. Obviously the first record is start
time + minutes to run job will give me finish time, but how do I
continue to add jobs and take the end time from the previous record
for the start of the next job and so on?

Also, what if I had 6 jobs scheduled and went back into the list and
changed a record (scheduled job), can I have it adjust all of these
times in all the other records?

Straight forward in an Excel spreadsheet but I want to do it in
access.

I have a table (tblProductionSched) with only 4 fields, psID
(autoNumber), psCardID(links to production card ID), and start and end
time fields. Another table (tblProductionCards) holds the job details
and run time data.

Any suggestions and help appreciated.

 

[Tom Wickerath]

Hi Sean,

You might try logic similar to the odometer example shown in this KB article:

Referring to a Field in the Previous Record or Next Record
http://support.microsoft.com/?id=210504

However, I rather doubt that an autonumber ID field is going to work in this
example. You'll likely need to assign your own incrementing ID sequence
number. If you later need to insert a record, you'll need to increment all
remaining sequence values first, starting with the last record, to make room
for the inserted record.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[sean]

Hi Tom,

Thanks for the suggestions. Tried using DLookup. Works on the
autonumber ID field of course until I delete a record, then add
another. As autonumbering skips the previously deleted ID the time
calculation restarts as it cant find [ID]-1.

I quess can use a manual incrementing cell where the user can type the
order manually but I really want to automate the process.

I didn't think [start time] + [time to run "minutes"] = [end time]
then using that end time for the next start time of the next record
and so on would be that difficult.

Any further ideas very welcome.

Cheers, Sean

 

[Tom Wickerath]

Hi Sean,

I have an idea that may work for you, but I need to write it up either
tomorrow evening or sometime on Saturday. I have a very early training class
that I need to attend tomorrow morning, so I can't spend time writing up my
idea right now. Basically, it would involve you manually assigning a
text-based number for each record, something like this:

1 Record 1
2 Record 2
3 Record 3

and if you later needed to insert two records in-between records 1 and 2,
you would simply need to give a "number", something like this:

1 Record 1
2 Record 2
3 Record 3
1.5 Inserted new Record
1.5.1 Inserted new Record (or 1.6, or 1.7, etc.--just something less than
2).

This would spare you the pain of having to renumber all remaining records,
starting from the last record. I can help you implement a "paragraph
numbering" type of sort algorithm. Would that type of an idea be workable to
you?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[sean]

Hi Tom,

Sounds like a plan! Thanks for your help.

Cheers, Sean

 

[Tom Wickerath]

Hi Sean,

It occured to me that my suggestion of using a text-based number for each
record is turning a simple task into a more complex task. I think all you
really need to do is to add a new field to your table, Datatype: Number /
Field size: Single, and start populating this field with the appropriate
numbers to sort your operations. If you use a single, and you start with
numbering like 1, 2, 3, 4, etc., then you will later be able to insert
records with numbers like 1.5, 1.55, 1.6, etc. Alternatively, you could
number each record with multiples of 5 or 10 (for example: 5, 10, 15, 20, 25
or 10, 20, 30, 40, 50), and still have plenty of room to insert additional
records, without having to renumber existing records, if/when the need
arises. Just include this new field in the query that serves as the
recordsource for any form or report, and set an ascending sort.

The text-based method that implements sorting on paragraph style numbers,
such as 1.1.2.1, 1.2.2.2, etc., would still require manual entry of a value
for each record. In case you are curious, here is a procedure for sorting
such paragraph style numbering (but I really don't think that you need to get
this complex for what you are doing):

' Posted by 'prebez'
'
http://www.utteraccess.com/forums/showthreaded.php?Cat=&Number=1387965&page=&view=&sb=5&o=&vc=1
' Substring value currently limited to 9999 maximum.
'
' Slightly modified by Tom Wickerath (added error handling)
' Note: You can increase the "4" indicated below to the maximum number
' of digits that you want to allow. For example, use 6 to allow 999999
maximum.

Option Compare Database
Option Explicit

Public Function ChapterKey(strChapter As String) As String
On Error GoTo ProcError
'On Error Resume Next

Dim Subchapter As Variant
Dim intCounter As Integer

Subchapter = Split(strChapter, ".")

For intCounter = 0 To UBound(Subchapter)
ChapterKey = ChapterKey & String(4 - Len(Subchapter(intCounter)), _
"0") & Subchapter(intCounter)
Next intCounter

ExitProc:
Exit Function
ProcError:
Select Case Err.Number
Case 5 'Invalid procedure call or argument
ChapterKey = "_Invalid substring"
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in ChapterKey Function..."
End Select
Resume ExitProc
End Function



Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________