Gets Zero values while approving work orders (1 Viewer)

[Khalid_Afridi]

Hi Every one!

I get zero values while approving work orders? I don’t know where is the bug?
The code is based on some queries which put back the total value of the issued items on the subform to a field on the main form.
The field on the main form gets zero values some times. (not the calculated value on the subform)

Here is the code:

Private Sub cmdApprove_Click()
On Error GoTo Err_Handler
    If IsNull(Me.ActCompletionDate) Then
    MsgBox "Please Enter Actual Completion Date", vbCritical
    Me.ActCompletionDate.SetFocus
    Exit Sub
End If
If IsNull(Me.WorkOrderApp_SF1.Form!txtAppvdByDesig) Then
    MsgBox "Please Select Approval Designation", vbCritical
    Exit Sub
End If
 
If IsNull(Me.WorkOrderApp_SF1.Form!Text680) Then
    MsgBox "Please Select quantity verifier", vbCritical
    Exit Sub
End If
 
 
If Me.cmdApprove.Caption = "Approved" Then
    MsgBox "Work Order has all ready been approved"
    Exit Sub
Else
    If Me.cmdApprove.Caption = "Click to Approve" Then
        Dim db As DAO.Database
        Set db = CurrentDb
        Dim pkid As Long
        Dim strSQL, csofno, Msg, title, responce As String
        Dim rstid As DAO.Recordset
 
        Msg = "Do you want to Approve the Work Request?"
        title = "Work Request Approval"
        responce = MsgBox(Msg, vbYesNo + vbQuestion, title)
 
            If responce = vbYes Then
            Me.WorkOrder_SF.Enabled = False
            Me.WorkOrderApp_SF1.Enabled = False
            Me.cmdApprove.Caption = "Approved"
            Me.LblStatus.Visible = True
            Me.cmd_edit.Visible = False
 
[COLOR=red]           If Me.WorkOrder_SF.Form!txtTotWOVal.Value - Me.WorkOrder_SF.Form!txtAccuTotal.Value > 0 Then[/COLOR]
[COLOR=red]              Me!SOFValue = Me!WorkOrder_SF.Form!txtNetVal.Value[/COLOR]
[COLOR=red]           End If[/COLOR]
[COLOR=red]           DoCmd.RunCommand (acCmdSaveRecord)[/COLOR]
 
        'chk if parrial issued is allowed
           If Me.chkPartPmt = -1 And Me.chkLastPayment.Value = 0 And Me.WorkOrder_SF.Form!txtTotWOVal.Value - Me.WorkOrder_SF.Form!txtAccuTotal.Value Then
 
                If Me!TxtIncrmnt = 0 Then
                    Me!TxtIncrmnt = 1
                End If
                 Me!SOFNo = "WO-" & Me!WONo & "-" & Me!TxtIncrmnt
                 Me!SOFValue = Me.WorkOrder_SF.Form!txtTotWOVal.Value - Me.WorkOrder_SF.Form!txtAccuTotal.Value
                 DoCmd.RunCommand (acCmdSaveRecord)
                'add the new record with the current form values
 
                DoCmd.SetWarnings False
                DoCmd.OpenQuery "AppendWO_Q"
 
                 csofno = "WO-" & Me!WONo & "-" & Me!TxtIncrmnt + 1
                 strSQL = "SELECT tblSOF.SOFID FROM tblSOF"
                 strSQL = strSQL + " WHERE ((tblSOF.ContractNo = '" & Me.ContractNo & "') And (tblSOF.SOFNo = '" & csofno & "'))"
 
                 Set rstid = db.OpenRecordset(strSQL, dbOpenForwardOnly)
 
                pkid = rstid!SOFID
                strSQL = "INSERT INTO WOApproval_T ( F_SOFID, WORID, WORIDDate, WOREndID1, WOREndID2, WOREndDate1, WOREndDate2, WORAuthorizedBy, WORAuthorizedDate, AuthUpdatedBy, AuthUpdatedDate )"
                strSQL = strSQL + " SELECT " & pkid & ", WOApproval_T.WORID, WOApproval_T.WORIDDate, WOApproval_T.WOREndID1, WOApproval_T.WOREndID2, WOApproval_T.WOREndDate1, WOApproval_T.WOREndDate2, WOApproval_T.WORAuthorizedBy, WOApproval_T.WORAuthorizedDate, WOApproval_T.AuthUpdatedBy, WOApproval_T.AuthUpdatedDate"
                strSQL = strSQL + " FROM WOApproval_T WHERE (((WOApproval_T.F_SOFID)=" & Me!SOFID & "))"
                db.Execute (strSQL)
 
 
                strSQL = "INSERT INTO WORef_T ( F_SOFID, LetterOfIntntRef, LetterOfIntntDate, PreCommncRptRef, PreCommncRptDate, AppAmt, OrgCode, OraclePN, F_AccStringID, UpdatedBy, UpdatedDate )"
                strSQL = strSQL + " SELECT " & pkid & ", WORef_T.LetterOfIntntRef, WORef_T.LetterOfIntntDate, WORef_T.PreCommncRptRef, WORef_T.PreCommncRptDate, WORef_T.AppAmt, WORef_T.OrgCode, WORef_T.OraclePN, WORef_T.F_AccStringID, WORef_T.UpdatedBy, WORef_T.UpdatedDate"
                strSQL = strSQL + " FROM WORef_T WHERE (((WORef_T.F_SOFID)=" & Me!SOFID & "))"
                db.Execute (strSQL)
 
                strSQL = "INSERT INTO tblQty ( SOFID, ItemID, Qty, TotQtyCompleted, PreQtyCompleted )"
                strSQL = strSQL + " SELECT " & pkid & ", tblQty.ItemID, tblQty.Qty, tblQty.Qty, tblQty.TotQtyCompleted"
                strSQL = strSQL + " FROM tblQty WHERE (((tblQty.SOFID)=" & Me!SOFID & "))"
                db.Execute (strSQL)
 
                rstid.Close
 
            DoCmd.SetWarnings True
                pkid = Me!SOFID
                strSQL = "SELECT Sum(([tblQty].[TotQtyCompleted]-[tblQty].[PreQtyCompleted])*[tblItem].[UnitRate]) AS tot"
                strSQL = strSQL + " FROM tblItem INNER JOIN tblQty ON tblItem.ItemID = tblQty.ItemID GROUP BY tblQty.SOFID HAVING (((tblQty.SOFID)=" & pkid & "))"
                Set rstid = db.OpenRecordset(strSQL, dbOpenForwardOnly)
                If Not rstid.BOF And Not rstid.EOF Then
                     Me!SOFValue = rstid!tot
                End If
                rstid.Close
                Set rstid = Nothing
                Me!Status = "Approved"
                Me!StatusDate = Now()
            Else
                Me!Status = "Approved"
                Me!StatusDate = Now()
                Me.cmdCancelWO.Enabled = False
                Me.ActCompletionDate.Locked = True
 
            End If
Exit_Err:
  Exit Sub
Err_Handler:
  MsgBox Err.DESCRIPTION
  Resume Exit_Err
     End If
   End If
End If
End Sub

Any idea please.