If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Other > Writing code to combine hours. Please help

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Feb 2013
Posts: 1
Question Writing code to combine hours. Please help

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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On