Comparing Previous record to current etc.

[Kate]

Hello,
I have a main form which summarises product information.
I then have a subform which contains the operations required to
manufacture that part.
For example, op1 might be 2 hours of using the NC lathe, op2 might be 1
hour machining using the NC lathe, op3 might be rumbling for half an
hour etc.

I want to summarise this information on the main form, such that the
above example would look as follows:
NC Lathe 3 hours (=2 +1 because the first 2 ops are both done on teh
same machine)
Rumbling 0.5 hours

I had decided to put a whole pile of invisible textboxes on teh main
form called machine(1), machine(2) etc, and make them visible when
filled.

i = Operation_Summary!Operation_Number

If i = 1 Then
Machine(1) = Operation_Summary!Operation
Time(1) = Operation_Summary!Programme_Time +
Operation_Summary!SetUp_Time + (Operation_Summary!Cycle_Time) * (Qty +
Scrap_Other_Qty)

ElseIf Machine(i - 1) = Operation_Summary!Operation Then
Time(i - 1) = Time(i - 1) + Operation_Summary!Programme_Time +
Operation_Summary!SetUp_Time + (Operation_Summary!Cycle_Time) * (Qty +
Scrap_Other_Qty)

Else
Machine(i) = Operation_Summary!Operation
Time(i) = Operation_Summary!SetUp_Time +
(Operation_Summary!Cycle_Time) * (Qty + Scrap_Other_Qty)
End If

But I can't get this to work. What am I doing wrong?

 

[James A. Fortune]

Hello,
I have a main form which summarises product information.
I then have a subform which contains the operations required to
manufacture that part.
For example, op1 might be 2 hours of using the NC lathe, op2 might be 1
hour machining using the NC lathe, op3 might be rumbling for half an
hour etc.

I want to summarise this information on the main form, such that the
above example would look as follows:
NC Lathe 3 hours (=2 +1 because the first 2 ops are both done on teh
same machine)
Rumbling 0.5 hours

I had decided to put a whole pile of invisible textboxes on teh main
form called machine(1), machine(2) etc, and make them visible when
filled.

i = Operation_Summary!Operation_Number

If i = 1 Then
Machine(1) = Operation_Summary!Operation
Time(1) = Operation_Summary!Programme_Time +
Operation_Summary!SetUp_Time + (Operation_Summary!Cycle_Time) * (Qty +
Scrap_Other_Qty)

ElseIf Machine(i - 1) = Operation_Summary!Operation Then
Time(i - 1) = Time(i - 1) + Operation_Summary!Programme_Time +
Operation_Summary!SetUp_Time + (Operation_Summary!Cycle_Time) * (Qty +
Scrap_Other_Qty)

Else
Machine(i) = Operation_Summary!Operation
Time(i) = Operation_Summary!SetUp_Time +
(Operation_Summary!Cycle_Time) * (Qty + Scrap_Other_Qty)
End If

But I can't get this to work. What am I doing wrong?

Hello Kate,

I have done a database that assigns work to NC Machines. Here is the
table structure I used:

tblNCMachines
MachineID Auto
Manufacturer Text
X Int
Y Int
Z Int
Type Text
RotaryTable Y/N
WeightCapacity Text
Axis Int
SortOrder Long

Note: MachineID 17 with Manufacturer 'Operation Pool' is used to
indicate unassigned operations.

tblOperations
OperationID Auto
Operation Text
Performed Dbl
OperationHours Dbl
Complete Y/N
JobID Long (FK)
OperatorID Long (FK)
DataRecdDate Date/Time
MatlRecdDate Date/Time
DueDate Date/Time
CompDate Date/Time
Remarks Text
EncryptedRemarks Y/N

tblAssignments
AssignmentID Auto
OperationID Long (FK)
MachineID Long (FK)
MachinePriorityIndex Long

tblNCMachEmployees
EmployeeID Auto
LastName Text
FirstName Text
Ext Text

tblDetail
DetailID Auto
OperationID Long (FK)
DetailNumber Text
DetailDescription Text

tblJobData
JobID Long (PK)
Tool Text
Type Text
CCode Text
UnitNumber Text

Through a form, the user can assign pool operations to different
machines, change the operation order for a given machine, or move an
operation from one machine to another followed by a recalculation of
hours allocated to those machines. The X, Y and Z values are displayed
within the listbox for each machine so that the user doesn't assign a
job that is too large for a given machine. That database is not
actually being used much since the NC manager likes doing things by hand
. Since several operations can have the same JobID, the JobID
identifies a product. Each operation can have several (blueprint)
details. Let me know if this is too confusing. If so, I'll try to help
you with the way you are doing it.

James A. Fortune
(e-mail address removed)

 

[Kate]

I must admit to being a little confused! Also, I don't much fancy the
idea of completely restructuring the database since everything else
works great.

I won't want to change the order of operations - there wont be many, so
the user can just re-enter them if they wanted to. I dont think i need
an extra job ID, since the machine name gives this. It would be great
if you could suggest something more along the lines of the way I am
doing it.

Cheers
Kate

 

[James A. Fortune]

I must admit to being a little confused! Also, I don't much fancy the
idea of completely restructuring the database since everything else
works great.

I won't want to change the order of operations - there wont be many, so
the user can just re-enter them if they wanted to. I dont think i need
an extra job ID, since the machine name gives this. It would be great
if you could suggest something more along the lines of the way I am
doing it.

Cheers
Kate

O.K. Lets start with Operation_Summary!Operation. To refer to the
field on the subform try Operation_Summary.Form!Operation. Repeat this
syntax for any subform references.

Post back with your results.

James A. Fortune
(e-mail address removed)

 

[Kate]

Thanks for looking into this. I actually managed to get it to do what
i wanted last night. So I will post this in case anyone else is
wondering how to do it. In the end, this is what I did:

Costing_Master form had the form on it called Operation_Summary. I
then created another subform on the Costing_Master from which had the
same fields as the Operation_Summary form, but the record source of
that form selected only hte records from Operation_Summary where Show =
true. (I created a field on the Operation_Summary form called "show",
and a totaltime field which added the time from the previous record if
the machine type was the same).

Thanks again
Kate

 

[James A. Fortune]

Thanks for looking into this. I actually managed to get it to do what
i wanted last night. So I will post this in case anyone else is
wondering how to do it. In the end, this is what I did:

Costing_Master form had the form on it called Operation_Summary. I
then created another subform on the Costing_Master from which had the
same fields as the Operation_Summary form, but the record source of
that form selected only hte records from Operation_Summary where Show =
true. (I created a field on the Operation_Summary form called "show",
and a totaltime field which added the time from the previous record if
the machine type was the same).

Thanks again
Kate

Kate,

That should not have been necessary since the subform has a
RecordSetClone that can be used to obtain summary information. I'd say
use whatever is easier for you to understand and to refresh. Using the
RecordsetClone probably requires a location on the main form to hold the
information so a separate subform is not much overhead. I'm glad you
found a way to get your summary info. My application refreshes the
totals on the main form whenever an operation gets assigned to a machine
or gets moved to another machine. The remaining hours for a job get
updated whenever hours are applied against it.

James A. Fortune
(e-mail address removed)