Hi

I'm not sure if this is the correct place to post but wondering if anyone can help. We are currently customising an ERP system for use in workplace and are struggling to get this code to work correctly.

Basically we are trying to combine all hours in the sub assemblies into one set of hours for each operation. Although the code below works and there are no errors, we can not get it to take into account the quantity per parent for each sub assembly before the hours are added together (we need to times the hours by the quantity per parent first.)

The column name for the quantity per parent is jmaquantityperparent. I hope that someone can help.

If controls("chkJmpProductionComplete").Value = -1 Then

Call App.MessageBox("You cannot remove the operations on this job as it is marked as completed",vbExclamation,"Cannot proceed")

Else

Dim JobID, JobQty
JobID = Trim(controls("txtJmpJobID").Value)
JobQty = CDbl(controls("txtJmpProductionQuantity").Value)

Set rsGetExistingTimecards = CreateObject("ADODB.Recordset")
rsGetExistingTimecards.Open "Select * from joboperations where jmoJobID In (Select lmlJobID From TimecardLines) And jmoJobID = "+App.AddQuotes(JobID)+" ", Connection, adOpenStatic, adLockBatchOptimistic, adCmdText

If rsGetExistingTimecards.EOF = False Then

Call App.MessageBox("There are timecards that already exist for this Job so the Operations cannot be deleted!",vbExclamation,"Cannot remove JobOperations")

Else

Set rsGetExistingSubcontractPOs = CreateObject("ADODB.Recordset")
rsGetExistingSubcontractPOs.Open "Select * from joboperations where jmoJobID In (Select pmlJobID From PurchaseOrderLines WHERE pmlJobType = 2 ) And jmoJobID = "+App.AddQuotes(JobID)+" ", Connection, adOpenStatic, adLockBatchOptimistic, adCmdText

If rsGetExistingSubcontractPOs.EOF = False Then
Call App.MessageBox("There are PO's that already exist for this Job so the Operations cannot be deleted!",vbExclamation,"Cannot remove JobOperations")
Else
Set rsGetJobData = CreateObject("ADODB.Recordset")
rsGetJobData.Open "Select jmoJobID, jmoJobAssemblyID,row * 10 As jmoJobOperationID,jmoWorkCenterID, jmoProcessID, jmoProductionStandard, jmoStandardFactor,jmoProcessShortDescription ,jmoQuantityPerAssembly , " _
+" jmoOperationType, jmoMachineType, jmoSetupRate,jmoProductionRate, jmoOverheadRate, jmoInspectionType, jmoOperationQuantity FROM " _
+"(Select jmoJobID, 0 As jmoJobAssemblyID, jmoWorkCenterID, Row_Number() OVER(Order By jmoJobID Asc) As row, jmoProcessID, SetupAndProdStandard As jmoProductionStandard, jmoStandardFactor, " _
+" jmoProcessShortDescription,jmoQuantityPerAssembly, jmoOperationType,jmoMachineType, jmosetupRate, jmoProductionRate, jmoOverheadRate,jmoInspectionType, jmoOperationQuantity " _
+" FROM (Select jmojobid, jmoworkcenterid, jmoprocessid ,sum(jmosetupHours)+sum(jmoProductionStandard) As SetupAndProdStandard,'HP' as jmoStandardFactor, max(jmoJobOperationid) as JobOp, " _
+" Max(jmoProcessShortDescription) as jmoProcessShortDescription, max(jmoQuantityPerAssembly) as jmoQuantityPerAssembly, max(jmoOperationType) as jmoOperationType, " _
+" max(jmoMachineType) as jmoMachineType, max(jmoSetupRate) as jmoSetupRate, max(jmoProductionRate) as jmoProductionRate, Max(jmoOverheadRate) as jmoOverheadRate, max(jmoInspectionType) as jmoInspectionType, " _
+" sum(jmoOperationQuantity) as jmoOperationQuantity " _
+" from joboperations where jmojobid = "+App.AddQuotes(JobID)+" GROUP BY jmojobid, jmoworkcenterid, jmoprocessid) as DataTOReplace ) as JobOperationData Order by jmoJobOperationID", Connection, adOpenStatic, adLockBatchOptimistic, adCmdText

connection.Execute "DELETE jobOperations WHERE jmoJobID = "+App.AddQuotes(JobID)+" "
Dim JobAssemblyID, JobOperationID, StdFactor, ProductionStandard, WorkCenterID, ProcessID, ProcessShortDescription, QtyPerAssembly, OpType, MachineType, SetupRate, ProdRate, OverheadRate, InspectionType, OperationQty, Firm

If rsGetJobData.EOF = False Then
rsGetJobData.MoveFirst
Do While rsGetJobData.EOF = False

JobID = Trim(rsGetJobData.Fields("jmoJobID").Value)
JobAssemblyID = CDbl(rsGetJobData.Fields("jmoJobAssemblyID").Value )
JobOperationID = CDbl(rsGetJobData.Fields("jmoJobOperationID").Valu e)
StdFactor= Trim(rsGetJobData.Fields("jmoStandardFactor").Valu e)
ProductionStandard = CDbl(rsGetJobData.Fields("jmoProductionStandard"). Value)
WorkCenterID = Trim(rsGetJobData.Fields("jmoWorkCenterID").Value)
ProcessID = Trim(rsGetJobData.Fields("jmoProcessID").Value)
ProcessShortDescription = Trim(rsGetJobData.Fields("jmoProcessShortDescripti on").Value)
QtyPerAssembly = CDbl(rsGetJobData.Fields("jmoQuantityPerAssembly") .Value)
OpType = CDbl(rsGetJobData.Fields("jmoOperationType").Value )
MachineType = CDbl(rsGetJobData.Fields("jmoMachineType").Value)
SetupRate = CDbl(rsGetJobData.Fields("jmoSetupRate").Value)
ProdRate = CDbl(rsGetJobData.Fields("jmoProductionRate").Valu e)
OverheadRate = CDbl(rsGetJobData.Fields("jmoOverheadRate").Value)
InspectionType = Trim(rsGetJobData.Fields("jmoInspectionType").Valu e)
OperationQty = CDbl(rsGetJobData.Fields("jmoOperationQuantity").V alue)
Firm = -1
QtyPerAssembly = 1
' If JobQty <> 0 Then
' QtyPerAssembly = Round(OperationQty/JobQty,5)
' Else
' QtyPerAssembly = QtyPerAssembly
'
' End If

' Connection.Execute "INSERT INTO JobOperations (jmoJobID,jmoJobAssemblyID, jmoJobOperationID, jmoStandardFactor, jmoProductionStandard, jmoWorkCenterID, jmoProcessID, " _
' +" jmoProcessShortDescription, jmoQuantityPerAssembly, jmoOperationType, jmoMachineType, jmoSetupRate, jmoProductionRate, jmoOverheadRate, jmoInspectionType,jmoOperationQuantity,jmoFirm) " _
' +" VALUES ("+App.AddQuotes(JobID)+","+App.AddQuotes(JobAssem blyID)+","+App.AddQuotes(JobOperationID)+","+App.A ddQuotes(StdFactor)+","+App.AddQuotes(ProductionSt andard)+","+App.AddQuotes(WorkCenterID)+","+App.Ad dQuotes(ProcessID)+", " _
' +" "+App.AddQuotes(ProcessShortDescription)+","+App.A ddQuotes(QtyPerAssembly)+" ,"+App.AddQuotes(OpType)+","+App.AddQuotes(Machine Type)+","+App.AddQuotes(SetupRate)+","+App.AddQuot es(ProdRate)+","+App.AddQuotes(OverheadRate)+","+A pp.AddQuotes(InspectionType)+", "+App.AddQuotes(OperationQty)+",-1) "

Connection.Execute "INSERT INTO JobOperations (jmoJobID,jmoJobAssemblyID, jmoJobOperationID, jmoStandardFactor, jmoProductionStandard, jmoWorkCenterID, jmoProcessID, " _
+" jmoProcessShortDescription, jmoQuantityPerAssembly, jmoOperationType, jmoMachineType, jmoSetupRate, jmoProductionRate, jmoOverheadRate, jmoInspectionType,jmoOperationQuantity,jmoFirm) " _
+" VALUES ("+App.AddQuotes(JobID)+","+App.AddQuotes(JobAssem blyID)+","+App.AddQuotes(JobOperationID)+","+App.A ddQuotes(StdFactor)+","+App.AddQuotes(ProductionSt andard)+","+App.AddQuotes(WorkCenterID)+","+App.Ad dQuotes(ProcessID)+", " _
+" "+App.AddQuotes(ProcessShortDescription)+","+App.A ddQuotes(QtyPerAssembly)+" ,"+App.AddQuotes(OpType)+","+App.AddQuotes(Machine Type)+","+App.AddQuotes(SetupRate)+","+App.AddQuot es(ProdRate)+","+App.AddQuotes(OverheadRate)+","+A pp.AddQuotes(InspectionType)+", "+App.AddQuotes(JobQty)+",-1) "



rsGetJobData.MoveNext
Loop
End If

Call App.MessageBox("The Job Operations have now been replaced",vbInformation,"Job Operations Updated")
controls("M1DataControl").RefreshTree

End If

End If


''''' End stopping the user from deleting the operations
End If