I'm stuck some where, whenever i am generating a report form my front-end I'm getting an error "ODBC 3151 ERROR"
I was assuming that their might be a problem in query
but when I'm executing on sql it is giving me the output for reference I'm giving the u people the code for the same
Expand|Select|Wrap|Line Numbers
- Private Function FillTempTable(strFilterDesc As String) As Boolean
- Dim rstcontract As DAO.Recordset
- Dim rst As DAO.Recordset
- Dim rst1 As DAO.Recordset
- Dim rstInvoice As DAO.Recordset
- Dim strUpdate As String
- Dim strcontractno As String
- Dim strInvoiceDetail As String
- Dim strInvoiceID As String
- Dim strInvoiceNo As String
- Dim strInvoiceDate As String
- Dim strInvGeneratedDate As String
- Dim strInvoiceAmount As String
- Dim strInvoiceType As String
- Dim strSQl As String
- Dim strAllocationAmount As String
- Dim intCnt As Long
- Dim intTotalRecords As Integer
- Dim strRewriteContractNo As String
- Dim strRVRecovery As String
- Dim strRVTransfer As String
- Dim dblContractRewrite As Double
- Dim strContractStatus As Boolean
- Dim blnGroup1 As Boolean
- Dim TotalPVofRV As Double
- Dim TotalNewPVofRV As Double
- Dim TotalOldPVofRV As Double
- Dim dblBalanceRV As Double
- Dim strContractVolume As String
- Dim strNominalRV As String
- Dim strGRV As String
- Dim strInvDueFromDate As String
- Dim strInvDueToDate As String
- Dim strGeneratedFromDate As String
- Dim strGeneratedToDate As String
- Dim strSQLConditon As String
- Dim strCashFlowTypes As String
- Dim strContractFrequency As String
- Dim strPVofRV_BF As String
- Dim strPVOfRV As String
- Dim strRemainingRVAsonDate As String
- Dim strAccoundingDate As String
- Dim strinvoiceRecovery As String
- Dim strBillingPeriod As String
- Dim strCustomerName As String
- Dim strADCustomerName As String
- Dim strAccountManager As String
- On Error GoTo Err_FillTempTable
- db.Execute "DELETE FROM ztSecondaryBilling ", dbSeeChanges
- strInvDueFromDate = ""
- strInvDueToDate = ""
- strGeneratedFromDate = ""
- strGeneratedToDate = ""
- strSQLConditon = ""
- strCashFlowTypes = ""
- strcontractno = ""
- If Me.chkDueDate = True Then
- If Trim(Nz(Me!txtInvDueFromDate, "")) <> "" Then
- strInvDueFromDate = Format(Trim(Nz(Me!txtInvDueFromDate, "")), "dd-mmm-yyyy")
- End If
- If Trim(Nz(Me!txtInvDueToDate, "")) <> "" Then
- strInvDueToDate = Format(Trim(Nz(Me!txtInvDueToDate, "")), "dd-mmm-yyyy")
- End If
- End If
- If Me.chkGenDate = True Then
- If Trim(Nz(Me!txtGenFromDate, "")) <> "" Then
- strGeneratedFromDate = Format(Trim(Nz(Me!txtGenFromDate, "")), "dd-mmm-yyyy")
- End If
- If Trim(Nz(Me!txtGenToDate, "")) <> "" Then
- strGeneratedToDate = Format(Trim(Nz(Me!txtGenToDate, "")), "dd-mmm-yyyy")
- End If
- End If
- ProgressBar.Visible = True
- txtProgerssBar.Visible = True
- lblImportCaption.Visible = True
- ProgressBar.Value = 0
- txtProgerssBar = "0%"
- lblImportCaption.Caption = "Collecting Data ..."
- 'strFilterDesc = " (Contract_Detail.Contract_Bank_Settlement>0) "
- strFilterDesc = strFilterDesc & " AND (Contract_Detail.Contract_Expiry_Date BETWEEN #01-Apr-2009# AND #31-Mar-2011#) "
- intCnt = 0
- intTotalRecords = 0
- Set rst = Nothing
- If Trim(strFilterDesc) <> "" Then strFilterDesc = " WHERE " & strFilterDesc
- Set rst = db.OpenRecordset("SELECT DISTINCT Count(Contract_Detail.Contract_No) AS TotalContracts FROM Contract_Detail " & strFilterDesc & " ", dbOpenDynaset, dbSeeChanges)
- If Not rst.EOF Then
- intTotalRecords = val(Nz(rst!TotalContracts, 0))
- End If
- rst.Close
- Set rst = Nothing
- blnGroup1 = False
- dblBalanceRV = 0
- TotalPVofRV = 0
- TotalOldPVofRV = 0
- Set rstcontract = Nothing
- Set rstcontract = db.OpenRecordset("SELECT * FROM Contract_Detail " & strFilterDesc & " ORDER BY Contract_Detail.Contract_No ASC ", dbOpenDynaset, dbSeeChanges)
- If Not rstcontract.EOF Then
- rstcontract.MoveFirst
- ProgressBar.MAX = val(intTotalRecords)
- While Not rstcontract.EOF
- Set rstInvoice = Nothing
- blnGroup1 = False
- strPVofRV_BF = ""
- TotalPVofRV = 0
- TotalOldPVofRV = 0
- strSQLConditon = ""
- strSQl = ""
- strNominalRV = ""
- strGRV = ""
- dblContractRewrite = 0
- strRemainingRVAsonDate = "0"
- strBillingPeriod = ""
- intCnt = val(intCnt) + 1
- strcontractno = Trim(Nz(rstcontract!Contract_no, ""))
- dblContractRewrite = val(Nz(rstcontract!Contract_Rewrite, 0))
- 'dblBalanceRV = val(Nz(rstContract!ContractRemainingRV, 0))
- strCustomerName = RetrieveName("Customer_Detail", "Customer_ID", "Customer_Name", val(Nz(rstcontract!Contract_customer_id, 0)))
- If dblContractRewrite <> 0 Then
- strContractVolume = val(Nz(rstcontract!Contract_Asset_Value, 0)) + val(Nz(rstcontract!ContractRewrittenAssetValue, 0))
- strNominalRV = val(Nz(rstcontract!Contract_rv_calculation, 0)) + val(Nz(rstcontract!Contract_old_rv_calculation, 0)) + val(Nz(rstcontract!RevisedNominalRV, 0))
- If Not IsNull(rstcontract!BlueSheetPVResidual) Then
- TotalPVofRV = val(Nz(rstcontract!BlueSheetPVResidual, 0))
- End If
- TotalOldPVofRV = 0
- If val(Nz(rstcontract!Contract_Rewrite, 0)) <> 0 Then
- If val(Nz(rstcontract!RevisedRVPercent, 0)) <> 0 Then
- TotalOldPVofRV = val(TotalOldPVofRV) + val(Nz(rstcontract!RevisedPVOfRV, 0))
- End If
- If val(Nz(rstcontract!ContractPVRVOldEquip, 0)) <> 0 Then
- TotalOldPVofRV = val(TotalOldPVofRV) + val(Nz(rstcontract!ContractPVRVOldEquip, 0))
- End If
- End If
- dblBalanceRV = TotalPVofRV + TotalOldPVofRV
- strPVOfRV = val(TotalPVofRV) + val(TotalOldPVofRV)
- 'strGRV = FV(val(gGRVPercentage) / 1200, (val(Nz(rstcontract!Contract_term, 0)) + IIf(val(Nz(rstcontract!Contract_perdiem, 0)) = 0, 0, IIf(val(Nz(rstcontract!Contract_perdiem, 0)) <= 30, 1, IIf(val(Nz(rstcontract!Contract_perdiem, 0)) <= 60, 2, 3)))), 0, -1 * val(TotalPVofRV))
- strGRV = val(Nz(rstcontract!BlueSheetRVCOF, 0))
- Else
- strContractVolume = val(Nz(rstcontract!Contract_Asset_Value, 0))
- strNominalRV = val(Nz(rstcontract!Contract_rv_calculation, 0))
- TotalPVofRV = val(Nz(rstcontract!BlueSheetPVResidual, 0))
- TotalOldPVofRV = 0
- dblBalanceRV = TotalPVofRV
- strPVOfRV = val(TotalPVofRV) + val(TotalOldPVofRV)
- strGRV = val(Nz(rstcontract!BlueSheetRVCOF, 0))
- End If
- If Trim(strInvDueFromDate) <> "" Then
- strPVofRV_BF = GetRemainingRV(strcontractno, DateAdd("d", -1, strInvDueFromDate), False)
- ElseIf Trim(strGeneratedFromDate) <> "" Then
- strPVofRV_BF = GetRemainingRV(strcontractno, DateAdd("d", -1, Trim(Nz(Me!txtGenFromDate, ""))), True)
- Else
- If dblContractRewrite <> 0 Then
- strPVofRV_BF = TotalPVofRV + TotalOldPVofRV
- Else
- strPVofRV_BF = TotalPVofRV + TotalOldPVofRV
- End If
- End If
- If Trim(strInvDueToDate) <> "" Then
- strRemainingRVAsonDate = GetRemainingRV(strcontractno, CStr(Format(CDate(strInvDueToDate), "dd-mmm-yyyy")), False)
- ElseIf Trim(strGeneratedToDate) <> "" Then
- strRemainingRVAsonDate = GetRemainingRV(strcontractno, CStr(Format(CDate(strGeneratedToDate), "dd-mmm-yyyy")), True)
- Else
- strRemainingRVAsonDate = GetRemainingRV(strcontractno, "", False)
- End If
- strAccountManager = ""
- Set rst = db.OpenRecordset("SELECT Customer_detail.Customer_id, Customer_detail.Customer_support_id, Staff.Staff_id, Staff.Staff_name FROM Customer_detail LEFT OUTER JOIN Staff ON Customer_detail.Customer_support_id = Staff.Staff_id WHERE (Customer_detail.Customer_id = " & val(Nz(rstcontract!Contract_customer_id, 0)) & ") ", dbOpenDynaset, dbSeeChanges)
- If Not rst.EOF Then
- If Not IsNull(rst!Staff_name) Then
- If Trim(Nz(rst!Staff_name, "")) <> "" Then
- strAccountManager = Trim(Nz(rst!Staff_name, ""))
- End If
- End If
- End If
- strAccoundingDate = ""
- strSQLConditon = ""
- Set rstInvoice = Nothing
- If chkRewrite Then
- strSQl = " SELECT DISTINCTROW RewriteRVAllocation.ContractNo, RewriteRVAllocation.RVRecovery, RewriteRVAllocation.RVTransfer, RewriteRVAllocation.RewriteContractNo "
- strSQl = strSQl & " FROM RewriteRVAllocation "
- strSQl = strSQl & " WHERE RewriteRVAllocation.ContractNo='" & strcontractno & "' "
- Set rstInvoice = db.OpenRecordset(strSQl, dbOpenDynaset, dbSeeChanges)
- If Not rstInvoice.EOF Then
- rstInvoice.MoveFirst
- While Not rstInvoice.EOF
- strRewriteContractNo = "": strRVRecovery = "": strRVTransfer = ""
- strinvoiceRecovery = "": strADCustomerName = "": strInvoiceType = ""
- strRewriteContractNo = CStr(Nz(rstInvoice!RewriteContractNo, ""))
- If Trim(strRewriteContractNo) <> "" Then
- Set rst1 = db.OpenRecordset("SELECT * FROM Contract_Detail WHERE Contract_No = '" & strRewriteContractNo & "' ", dbOpenDynaset, dbSeeChanges)
- If Not rst1.EOF Then
- strInvoiceDate = CStr(Nz(rst1!Contract_Accounting_Date, ""))
- If Trim(strInvoiceDate) <> "" Then strInvoiceDate = Format(CDate(strInvoiceDate), "dd-mmm-yyyy")
- strInvGeneratedDate = CStr(Nz(rst1!Contract_Accounting_Date, ""))
- If Trim(strInvGeneratedDate) <> "" Then strInvGeneratedDate = Format(CDate(strInvGeneratedDate), "dd-mmm-yyyy")
- End If
- End If
- strRVRecovery = val(Nz(rstInvoice!RVRecovery, 0))
- strRVTransfer = val(Nz(rstInvoice!RVTransfer, 0))
- strInvoiceNo = strRewriteContractNo
- strInvoiceAmount = val(strRVRecovery)
- strinvoiceRecovery = val(strRVRecovery)
- strInvoiceType = "Rewrite"
- strADCustomerName = ""
- strUpdate = ""
- strUpdate = strUpdate & " INSERT INTO ztSecondaryBilling "
- strUpdate = strUpdate & " ( "
- strUpdate = strUpdate & " CustomerName " & " "
- strUpdate = strUpdate & " , ContractNo " & " "
- strUpdate = strUpdate & " , AccountManager " & " "
- strUpdate = strUpdate & " , InvoiceNo " & " "
- strUpdate = strUpdate & " , InvoiceDate " & " "
- strUpdate = strUpdate & " , InvoiceGeneratedDate " & " "
- strUpdate = strUpdate & " , InvocieAmount " & " "
- strUpdate = strUpdate & " , NominalRV " & " "
- strUpdate = strUpdate & " , PVOfRV " & " "
- strUpdate = strUpdate & " , GRV " & " "
- strUpdate = strUpdate & " , InvoiceRecovery " & " "
- strUpdate = strUpdate & " , InvoiceType " & " "
- strUpdate = strUpdate & " , ADCustomerName " & " "
- strUpdate = strUpdate & " ) VALUES ( "
- strUpdate = strUpdate & " '" & strCustomerName & "' "
- strUpdate = strUpdate & " , '" & strcontractno & "' "
- strUpdate = strUpdate & " , '" & strAccountManager & "' "
- strUpdate = strUpdate & " , '" & strInvoiceNo & "' "
- strUpdate = strUpdate & " , " & g_sDBDateChr & strInvoiceDate & g_sDBDateChr & " "
- strUpdate = strUpdate & " , " & g_sDBDateChr & strInvGeneratedDate & g_sDBDateChr & " "
- strUpdate = strUpdate & " , " & val(strInvoiceAmount) & " "
- If blnGroup1 = False Then
- strUpdate = strUpdate & " , " & val(strNominalRV) & " "
- strUpdate = strUpdate & " , " & val(strPVOfRV) & " "
- strUpdate = strUpdate & " , " & val(strGRV) & " "
- blnGroup1 = True
- Else
- strUpdate = strUpdate & " , 0 "
- strUpdate = strUpdate & " , 0 "
- strUpdate = strUpdate & " , 0 "
- End If
- strUpdate = strUpdate & " , " & val(strinvoiceRecovery) & " "
- strUpdate = strUpdate & " , '" & CStr(strInvoiceType) & "' "
- strUpdate = strUpdate & " , '" & CStr(strADCustomerName) & "' "
- strUpdate = strUpdate & " ) "
- If Trim(strUpdate) <> "" Then
- Debug.Print strUpdate
- db.Execute strUpdate, dbSeeChanges
- End If
- rstInvoice.MoveNext
- Wend
- End If
- End If
- ''''' Inertia Invoice
- If chkInertia Then
- If CBool(rstcontract!ShortTermContract) = False Then
- strSQLConditon = ""
- If Trim(strInvDueFromDate) <> "" Then
- If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
- strSQLConditon = strSQLConditon & " Contract_CashFlow.CashFlowDate >= Format(" & g_sDBDateChr & Trim(strInvDueFromDate) & g_sDBDateChr & ",'dd-mmm-yyyy') "
- End If
- If Trim(strInvDueToDate) <> "" Then
- If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
- strSQLConditon = strSQLConditon & " Contract_CashFlow.CashFlowDate <= Format(" & g_sDBDateChr & Trim(strInvDueToDate) & g_sDBDateChr & ",'dd-mmm-yyyy') "
- End If
- If Trim(strGeneratedFromDate) <> "" Then
- If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
- strSQLConditon = strSQLConditon & " Contract_CashFlow.CashFlowGeneratedDate >= Format(" & g_sDBDateChr & Left(strGeneratedFromDate, 2) + "-" + Mid(strGeneratedFromDate, 4, 3) + "-" + Right(strGeneratedFromDate, 4) & g_sDBDateChr & ",'dd-mmm-yyyy') "
- End If
- If Trim(strGeneratedToDate) <> "" Then
- If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
- strSQLConditon = strSQLConditon & " Contract_CashFlow.CashFlowGeneratedDate <= Format(" & g_sDBDateChr & Left(strGeneratedToDate, 2) + "-" + Mid(strGeneratedToDate, 4, 3) + "-" + Right(strGeneratedToDate, 4) & g_sDBDateChr & ",'dd-mmm-yyyy') "
- End If
- If Trim(strSQLConditon) <> "" Then
- strSQl = " SELECT Contract_cashflow.*, CashflowType.CashflowTypeDesc, ContractInvoice.InvoiceRVRecovery "
- strSQl = strSQl & " FROM (Contract_cashflow INNER JOIN CashflowType ON Contract_cashflow.CashflowTypeID = CashflowType.CashflowTypeID) LEFT JOIN ContractInvoice ON (Contract_cashflow.CashflowNumber = ContractInvoice.CashFlowNumber) AND (Contract_cashflow.CashflowContractNo = ContractInvoice.InvoiceContractNo) "
- strSQl = strSQl & " WHERE Contract_cashflow.CashFlowContractNo='" & strcontractno & "' "
- strSQl = strSQl & " AND Contract_cashflow.CashFlowTypeID IN(1) AND " & strSQLConditon & " "
- Else
- strSQl = " SELECT Contract_cashflow.*, CashflowType.CashflowTypeDesc, ContractInvoice.InvoiceRVRecovery "
- strSQl = strSQl & " FROM (Contract_cashflow INNER JOIN CashflowType ON Contract_cashflow.CashflowTypeID = CashflowType.CashflowTypeID) LEFT JOIN ContractInvoice ON (Contract_cashflow.CashflowNumber = ContractInvoice.CashFlowNumber) AND (Contract_cashflow.CashflowContractNo = ContractInvoice.InvoiceContractNo) "
- strSQl = strSQl & " WHERE Contract_cashflow.CashFlowContractNo='" & strcontractno & "' "
- strSQl = strSQl & " AND Contract_cashflow.CashFlowTypeID IN(1) "
- End If
- Set rstInvoice = db.OpenRecordset(strSQl, dbOpenDynaset, dbSeeChanges)
- If Not rstInvoice.EOF Then
- rstInvoice.MoveFirst
- While Not rstInvoice.EOF
- strInvoiceNo = "": strInvoiceDate = "": strInvoiceAmount = "": strInvoiceType = "": strAllocationAmount = ""
- strBillingPeriod = ""
- If Not IsNull(rstInvoice!CashflowNumber) Then
- If Trim(rstInvoice!CashflowNumber) <> "" Then
- strInvoiceID = CStr(Trim(rstInvoice!CashflowNumber))
- End If
- End If
- If Not IsNull(rstInvoice!CashflowDate) Then
- If Trim(rstInvoice!CashflowDate) <> "" Then
- strInvoiceNo = Right(Year(Trim(rstInvoice!CashflowDate)), 2) & String(2 - Len(Month(Trim(rstInvoice!CashflowDate))), "0") & Month(Trim(rstInvoice!CashflowDate)) & "-" & Trim(strcontractno)
- End If
- End If
- If Not IsNull(rstInvoice!CashflowDate) Then
- If Trim(rstInvoice!CashflowDate) <> "" Then
- strInvoiceDate = CStr(Trim(rstInvoice!CashflowDate))
- End If
- End If
- strBillingPeriod = ""
- Set rst = db.OpenRecordset("SELECT Contract_Frequency FROM Contract_Detail WHERE Contract_No = '" & Nz(strcontractno, "") & "' ", dbOpenDynaset, dbSeeChanges)
- If Not rst.EOF Then
- strContractFrequency = Nz(rst!Contract_frequency, 0)
- If Trim(strInvoiceDate) <> "" And val(strContractFrequency) > 0 Then
- strBillingPeriod = Format(CDate(strInvoiceDate), "dd-mmm-yyyy") & " to " & Format(DateAdd("d", -1, CDate(Format(DateAdd("m", Round(val(strContractFrequency) / 30), CDate(strInvoiceDate)), "dd-mmm-yyyy"))), "dd-mmm-yyyy")
- Else
- strBillingPeriod = ""
- End If
- End If
- rst.Close
- If Not IsNull(rstInvoice!CashFlowGeneratedDate) Then
- If Trim(rstInvoice!CashFlowGeneratedDate) <> "" Then
- strInvGeneratedDate = CStr(Trim(Nz(rstInvoice!CashFlowGeneratedDate, "")))
- End If
- End If
- If Not IsNull(rstInvoice!CashflowAmount) Then
- If Trim(rstInvoice!CashflowAmount) <> "" Then
- strInvoiceAmount = val(Nz(rstInvoice!CashflowAmount, 0)) + val(Nz(rstInvoice!CashFlowServiceRental, 0))
- End If
- End If
- If Not IsNull(rstInvoice!InvoiceRVRecovery) Then
- If Trim(rstInvoice!InvoiceRVRecovery) <> "" Then
- strAllocationAmount = CStr(Trim(rstInvoice!InvoiceRVRecovery))
- End If
- End If
- '' If Not IsNull(rstInvoice!ContractStatusDesc) Then
- '' If Trim(rstInvoice!ContractStatusDesc) <> "" Then
- '' strInvoiceType = CStr(Trim("Inertia"))
- '' End If
- '' End If
- strInvoiceType = CStr(Trim("Inertia"))
- Set rst = db.OpenRecordset("SELECT * FROM ztSecondaryBilling WHERE ContractNo = '" & strcontractno & "' AND InvoiceID=" & strInvoiceID & " AND InvoiceNo = '" & strInvoiceNo & "' ", dbOpenDynaset, dbSeeChanges)
- If Not rst.EOF Then
- strInvoiceAmount = 0
- End If
- rst.Close
- strUpdate = ""
- strUpdate = strUpdate & " INSERT INTO ztSecondaryBilling "
- strUpdate = strUpdate & " ( "
- strUpdate = strUpdate & " CustomerName " & " "
- strUpdate = strUpdate & " , ContractNo " & " "
- strUpdate = strUpdate & " , AccountManager " & " "
- strUpdate = strUpdate & " , InvoiceID " & " "
- strUpdate = strUpdate & " , InvoiceNo " & " "
- strUpdate = strUpdate & " , InvoiceDate " & " "
- If Trim(strInvGeneratedDate) <> "" Then
- strUpdate = strUpdate & " , InvoiceGeneratedDate " & " "
- End If
- strUpdate = strUpdate & " , InvocieAmount " & " "
- strUpdate = strUpdate & " , NominalRV " & " "
- strUpdate = strUpdate & " , PVOfRV " & " "
- strUpdate = strUpdate & " , GRV " & " "
- strUpdate = strUpdate & " , InvoiceRecovery " & " "
- strUpdate = strUpdate & " , InvoiceType " & " "
- strUpdate = strUpdate & " , ADCustomerName " & " "
- strUpdate = strUpdate & " ) VALUES ( "
- strUpdate = strUpdate & " '" & strCustomerName & "' "
- strUpdate = strUpdate & " , '" & strcontractno & "' "
- strUpdate = strUpdate & " , '" & strAccountManager & "' "
- strUpdate = strUpdate & " , " & val(strInvoiceID) & " "
- strUpdate = strUpdate & " , '" & strInvoiceNo & "' "
- strUpdate = strUpdate & " , " & g_sDBDateChr & strInvoiceDate & g_sDBDateChr & " "
- If Trim(strInvGeneratedDate) <> "" Then
- strUpdate = strUpdate & " , " & g_sDBDateChr & strInvGeneratedDate & g_sDBDateChr & " "
- End If
- strUpdate = strUpdate & " , " & val(strInvoiceAmount) & " "
- If blnGroup1 = False Then
- strUpdate = strUpdate & " , " & val(strNominalRV) & " "
- strUpdate = strUpdate & " , " & val(strPVOfRV) & " "
- strUpdate = strUpdate & " , " & val(strGRV) & " "
- blnGroup1 = True
- Else
- strUpdate = strUpdate & " , 0 "
- strUpdate = strUpdate & " , 0 "
- strUpdate = strUpdate & " , 0 "
- End If
- strUpdate = strUpdate & " , " & val(strAllocationAmount) & " "
- strUpdate = strUpdate & " , '" & CStr(strInvoiceType) & "' "
- strUpdate = strUpdate & " , '" & CStr(strADCustomerName) & "' "
- strUpdate = strUpdate & " ) "
- If Trim(strUpdate) <> "" Then
- Debug.Print strUpdate
- db.Execute strUpdate, dbSeeChanges
- End If
- rstInvoice.MoveNext
- Wend
- End If
- End If
- End If
- ''''' Sale Invoice
- If chkSale Then
- strSQLConditon = ""
- If Trim(strInvDueFromDate) <> "" Then
- If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
- strSQLConditon = strSQLConditon & " ADInvoice.InvoiceDate >= Format(" & g_sDBDateChr & Trim(strInvDueFromDate) & g_sDBDateChr & ",'dd-mmm-yyyy') "
- End If
- If Trim(strInvDueToDate) <> "" Then
- If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
- strSQLConditon = strSQLConditon & " ADInvoice.InvoiceDate <= Format(" & g_sDBDateChr & Trim(strInvDueToDate) & g_sDBDateChr & ",'dd-mmm-yyyy') "
- End If
- If Trim(strGeneratedFromDate) <> "" Then
- If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
- strSQLConditon = strSQLConditon & " ADInvoice.InvoiceGeneratedDate >= Format(" & g_sDBDateChr & Left(strGeneratedFromDate, 2) + "-" + Mid(strGeneratedFromDate, 4, 3) + "-" + Right(strGeneratedFromDate, 4) & g_sDBDateChr & ",'dd-mmm-yyyy') "
- End If
- If Trim(strGeneratedToDate) <> "" Then
- If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
- strSQLConditon = strSQLConditon & " ADInvoice.InvoiceGeneratedDate <= Format(" & g_sDBDateChr & Left(strGeneratedToDate, 2) + "-" + Mid(strGeneratedToDate, 4, 3) + "-" + Right(strGeneratedToDate, 4) & g_sDBDateChr & ",'dd-mmm-yyyy') "
- End If
- strSQl = ""
- If Trim(strSQLConditon) <> "" Then
- strSQl = strSQl & " SELECT DISTINCT ADInvoice.InvoiceNo, ADInvoice.InvoiceDate, ADInvoice.InvoiceID AS InvoiceID, ADRVAllocation.ContractNo AS ContractNo, Sum(ADAsset.AssetPrice) AS SumOfAssetPrice, ADRVAllocation.ADRVAllocation, ADInvoice.InvoiceTotal, ADInvoice.InvoiceTypeID, ADInvoice.InvoiceGeneratedDate "
- strSQl = strSQl & " FROM (ADInvoice LEFT JOIN ADRVAllocation ON ADInvoice.InvoiceID = ADRVAllocation.InvoiceID) LEFT JOIN ADAsset ON (ADRVAllocation.InvoiceID = ADAsset.AssetInvoiceID) AND (ADRVAllocation.ContractNo = ADAsset.AssetContractNo) "
- strSQl = strSQl & " GROUP BY ADInvoice.InvoiceNo, ADInvoice.InvoiceDate, ADInvoice.InvoiceID, ADRVAllocation.ContractNo, ADRVAllocation.ADRVAllocation, ADInvoice.InvoiceTotal, ADInvoice.InvoiceTypeID, ADInvoice.InvoiceGeneratedDate "
- strSQl = strSQl & " HAVING (((ADRVAllocation.ContractNo)='" & Trim(Nz(strcontractno, "")) & "') AND ((ADInvoice.InvoiceTypeID)=1)) "
- strSQl = strSQl & " AND " & strSQLConditon & " "
- strSQl = strSQl & " Union "
- strSQl = strSQl & " SELECT DISTINCT ADInvoice.InvoiceNo, ADInvoice.InvoiceDate, ADAsset.AssetDamagesInvoiceID AS InvoiceID, ADRVAllocation.ContractNo AS ContractNo, Sum(ADAsset.AssetInvoiceCost) AS SumOfAssetPrice, ADRVAllocation.ADRVAllocation, ADInvoice.InvoiceTotal, ADInvoice.InvoiceTypeID, ADInvoice.InvoiceGeneratedDate "
- strSQl = strSQl & " FROM (ADInvoice LEFT JOIN ADRVAllocation ON ADInvoice.InvoiceID = ADRVAllocation.InvoiceID) LEFT JOIN ADAsset ON (ADRVAllocation.InvoiceID = ADAsset.AssetInvoiceID) AND (ADRVAllocation.ContractNo = ADAsset.AssetContractNo) "
- strSQl = strSQl & " GROUP BY ADInvoice.InvoiceNo, ADInvoice.InvoiceDate, ADAsset.AssetDamagesInvoiceID, ADRVAllocation.ContractNo, ADRVAllocation.ADRVAllocation, ADInvoice.InvoiceTotal, ADInvoice.InvoiceTypeID, ADInvoice.InvoiceGeneratedDate "
- strSQl = strSQl & " HAVING (((ADAsset.AssetDamagesInvoiceID) Is Not Null) AND ((ADRVAllocation.ContractNo)='" & Trim(Nz(strcontractno, "")) & "') AND ((ADInvoice.InvoiceTypeID)=1)) "
- strSQl = strSQl & " AND " & strSQLConditon & " "
- Else
- strSQl = strSQl & " SELECT DISTINCT ADInvoice.InvoiceNo, ADInvoice.InvoiceDate, ADInvoice.InvoiceID AS InvoiceID, ADRVAllocation.ContractNo AS ContractNo, Sum(ADAsset.AssetPrice) AS SumOfAssetPrice, ADRVAllocation.ADRVAllocation, ADInvoice.InvoiceTotal, ADInvoice.InvoiceTypeID, ADInvoice.InvoiceGeneratedDate "
- strSQl = strSQl & " FROM (ADInvoice LEFT JOIN ADRVAllocation ON ADInvoice.InvoiceID = ADRVAllocation.InvoiceID) LEFT JOIN ADAsset ON (ADRVAllocation.InvoiceID = ADAsset.AssetInvoiceID) AND (ADRVAllocation.ContractNo = ADAsset.AssetContractNo) "
- strSQl = strSQl & " GROUP BY ADInvoice.InvoiceNo, ADInvoice.InvoiceDate, ADInvoice.InvoiceID, ADRVAllocation.ContractNo, ADRVAllocation.ADRVAllocation, ADInvoice.InvoiceTotal, ADInvoice.InvoiceTypeID, ADInvoice.InvoiceGeneratedDate "
- strSQl = strSQl & " HAVING (((ADRVAllocation.ContractNo)='" & Trim(Nz(strcontractno, "")) & "') AND ((ADInvoice.InvoiceTypeID)=1)) "
- strSQl = strSQl & " Union "
- strSQl = strSQl & " SELECT DISTINCT ADInvoice.InvoiceNo, ADInvoice.InvoiceDate, ADAsset.AssetDamagesInvoiceID AS InvoiceID, ADRVAllocation.ContractNo AS ContractNo, Sum(ADAsset.AssetInvoiceCost) AS SumOfAssetPrice, ADRVAllocation.ADRVAllocation, ADInvoice.InvoiceTotal, ADInvoice.InvoiceTypeID, ADInvoice.InvoiceGeneratedDate "
- strSQl = strSQl & " FROM (ADInvoice LEFT JOIN ADRVAllocation ON ADInvoice.InvoiceID = ADRVAllocation.InvoiceID) LEFT JOIN ADAsset ON (ADRVAllocation.InvoiceID = ADAsset.AssetInvoiceID) AND (ADRVAllocation.ContractNo = ADAsset.AssetContractNo) "
- strSQl = strSQl & " GROUP BY ADInvoice.InvoiceNo, ADInvoice.InvoiceDate, ADAsset.AssetDamagesInvoiceID, ADRVAllocation.ContractNo, ADRVAllocation.ADRVAllocation, ADInvoice.InvoiceTotal, ADInvoice.InvoiceTypeID, ADInvoice.InvoiceGeneratedDate "
- strSQl = strSQl & " HAVING (((ADAsset.AssetDamagesInvoiceID) Is Not Null) AND ((ADRVAllocation.ContractNo)='" & Trim(Nz(strcontractno, "")) & "') AND ((ADInvoice.InvoiceTypeID)=1)) "
- End If
- Set rstInvoice = Nothing
- ' strSQL = "SELECT Invoice.* FROM Invoice WHERE ADInvoice.Invoice_contract_no='" & strContractNo & "' "
- Set rstInvoice = db.OpenRecordset(strSQl, dbOpenDynaset, dbSeeChanges)
- If Not rstInvoice.EOF Then
- rstInvoice.MoveFirst
- While Not rstInvoice.EOF
- strInvoiceNo = "": strInvoiceDate = "": strInvoiceAmount = "": strInvoiceType = "": strAllocationAmount = ""
- strBillingPeriod = ""
- If Not IsNull(rstInvoice!InvoiceID) Then
- If Trim(rstInvoice!InvoiceID) <> "" Then
- strInvoiceID = CStr(Trim(rstInvoice!InvoiceID))
- End If
- End If
- If Not IsNull(rstInvoice!InvoiceNo) Then
- If Trim(rstInvoice!InvoiceNo) <> "" Then
- strInvoiceNo = CStr(Trim(rstInvoice!InvoiceNo))
- End If
- End If
- If Not IsNull(rstInvoice!InvoiceDate) Then
- If Trim(rstInvoice!InvoiceDate) <> "" Then
- strInvoiceDate = CStr(Trim(rstInvoice!InvoiceDate))
- End If
- End If
- If Not IsNull(rstInvoice!InvoiceGeneratedDate) Then
- If Trim(rstInvoice!InvoiceGeneratedDate) <> "" Then
- strInvGeneratedDate = CStr(Trim(rstInvoice!InvoiceGeneratedDate))
- End If
- End If
- If Not IsNull(rstInvoice!InvoiceTotal) Then
- If Trim(rstInvoice!InvoiceTotal) <> "" Then
- strInvoiceAmount = CStr(Trim(rstInvoice!InvoiceTotal))
- End If
- End If
- If Not IsNull(rstInvoice!ADRVAllocation) Then
- If Trim(rstInvoice!ADRVAllocation) <> "" Then
- strAllocationAmount = CStr(Trim(rstInvoice!ADRVAllocation))
- End If
- End If
- If Not IsNull(rstInvoice!InvoiceTypeID) Then
- If val(Nz(rstInvoice!InvoiceTypeID, 0)) = 1 Then
- strInvoiceType = CStr(Trim("Sales Invoice"))
- ElseIf val(Nz(rstInvoice!InvoiceTypeID, 0)) = 2 Then
- strInvoiceType = CStr(Trim("Repair Invoice"))
- End If
- Else
- strInvoiceType = CStr(Trim("Sales Invoice"))
- End If
- Set rst = db.OpenRecordset("SELECT * FROM ztSecondaryBilling WHERE InvoiceNo = '" & strInvoiceNo & "' AND InvoiceDate=" & g_sDBDateChr & strInvoiceDate & g_sDBDateChr & " ", dbOpenDynaset, dbSeeChanges)
- If Not rst.EOF Then
- strInvoiceAmount = 0
- End If
- rst.Close
- strUpdate = ""
- strUpdate = strUpdate & " INSERT INTO ztSecondaryBilling "
- strUpdate = strUpdate & " ( "
- strUpdate = strUpdate & " CustomerName " & " "
- strUpdate = strUpdate & " , ContractNo " & " "
- strUpdate = strUpdate & " , AccountManager " & " "
- strUpdate = strUpdate & " , InvoiceID " & " "
- strUpdate = strUpdate & " , InvoiceNo " & " "
- strUpdate = strUpdate & " , InvoiceDate " & " "
- If Trim(strInvGeneratedDate) <> "" Then
- strUpdate = strUpdate & " , InvoiceGeneratedDate " & " "
- End If
- strUpdate = strUpdate & " , InvocieAmount " & " "
- strUpdate = strUpdate & " , NominalRV " & " "
- strUpdate = strUpdate & " , PVOfRV " & " "
- strUpdate = strUpdate & " , GRV " & " "
- strUpdate = strUpdate & " , InvoiceRecovery " & " "
- strUpdate = strUpdate & " , InvoiceType " & " "
- strUpdate = strUpdate & " , ADCustomerName " & " "
- strUpdate = strUpdate & " ) VALUES ( "
- strUpdate = strUpdate & " '" & strCustomerName & "' "
- strUpdate = strUpdate & " , '" & strcontractno & "' "
- strUpdate = strUpdate & " , '" & strAccountManager & "' "
- strUpdate = strUpdate & " , " & val(strInvoiceID) & " "
- strUpdate = strUpdate & " , '" & strInvoiceNo & "' "
- strUpdate = strUpdate & " , " & g_sDBDateChr & strInvoiceDate & g_sDBDateChr & " "
- If Trim(strInvGeneratedDate) <> "" Then
- strUpdate = strUpdate & " , " & g_sDBDateChr & strInvGeneratedDate & g_sDBDateChr & " "
- End If
- strUpdate = strUpdate & " , " & val(strInvoiceAmount) & " "
- If blnGroup1 = False Then
- strUpdate = strUpdate & " , " & val(strNominalRV) & " "
- strUpdate = strUpdate & " , " & val(strPVOfRV) & " "
- strUpdate = strUpdate & " , " & val(strGRV) & " "
- blnGroup1 = True
- Else
- strUpdate = strUpdate & " , 0 "
- strUpdate = strUpdate & " , 0 "
- strUpdate = strUpdate & " , 0 "
- End If
- strUpdate = strUpdate & " , " & val(strAllocationAmount) & " "
- strUpdate = strUpdate & " , '" & CStr(strInvoiceType) & "' "
- strUpdate = strUpdate & " , '" & CStr(strADCustomerName) & "' "
- strUpdate = strUpdate & " ) "
- If Trim(strUpdate) <> "" Then
- db.Execute strUpdate, dbSeeChanges
- End If
- rstInvoice.MoveNext
- Wend
- End If
- End If
- ''''' Repair Invoice
- If chkRepair Then
- strSQLConditon = ""
- If Trim(strInvDueFromDate) <> "" Then
- If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
- strSQLConditon = strSQLConditon & " ADInvoice.InvoiceDate >= Format(" & g_sDBDateChr & Trim(strInvDueFromDate) & g_sDBDateChr & ",'dd-mmm-yyyy') "
- End If
- If Trim(strInvDueToDate) <> "" Then
- If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
- strSQLConditon = strSQLConditon & " ADInvoice.InvoiceDate <= Format(" & g_sDBDateChr & Trim(strInvDueToDate) & g_sDBDateChr & ",'dd-mmm-yyyy') "
- End If
- If Trim(strGeneratedFromDate) <> "" Then
- If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
- strSQLConditon = strSQLConditon & " ADInvoice.InvoiceGeneratedDate >= Format(" & g_sDBDateChr & Left(strGeneratedFromDate, 2) + "-" + Mid(strGeneratedFromDate, 4, 3) + "-" + Right(strGeneratedFromDate, 4) & g_sDBDateChr & ",'dd-mmm-yyyy') "
- End If
- If Trim(strGeneratedToDate) <> "" Then
- If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
- strSQLConditon = strSQLConditon & " ADInvoice.InvoiceGeneratedDate <= Format(" & g_sDBDateChr & Left(strGeneratedToDate, 2) + "-" + Mid(strGeneratedToDate, 4, 3) + "-" + Right(strGeneratedToDate, 4) & g_sDBDateChr & ",'dd-mmm-yyyy') "
- End If
- strSQl = ""
- If Trim(strSQLConditon) <> "" Then
- strSQl = strSQl & " SELECT DISTINCT ADInvoice.InvoiceNo, ADInvoice.InvoiceDate, ADInvoice.InvoiceID AS InvoiceID, ADRVAllocation.ContractNo AS ContractNo, Sum(ADAsset.AssetPrice) AS SumOfAssetPrice, ADRVAllocation.ADRVAllocation, ADInvoice.InvoiceTotal, ADInvoice.InvoiceTypeID, ADInvoice.InvoiceGeneratedDate "
- strSQl = strSQl & " FROM (ADInvoice LEFT JOIN ADRVAllocation ON ADInvoice.InvoiceID = ADRVAllocation.InvoiceID) LEFT JOIN ADAsset ON (ADRVAllocation.InvoiceID = ADAsset.AssetInvoiceID) AND (ADRVAllocation.ContractNo = ADAsset.AssetContractNo) "
- strSQl = strSQl & " GROUP BY ADInvoice.InvoiceNo, ADInvoice.InvoiceDate, ADInvoice.InvoiceID, ADRVAllocation.ContractNo, ADRVAllocation.ADRVAllocation, ADInvoice.InvoiceTotal, ADInvoice.InvoiceTypeID, ADInvoice.InvoiceGeneratedDate "
- strSQl = strSQl & " HAVING (((ADRVAllocation.ContractNo)='" & Trim(Nz(strcontractno, "")) & "') AND ((ADInvoice.InvoiceTypeID)=2)) "
- strSQl = strSQl & " AND " & strSQLConditon & " "
- strSQl = strSQl & " Union "
- strSQl = strSQl & " SELECT DISTINCT ADInvoice.InvoiceNo, ADInvoice.InvoiceDate, ADAsset.AssetDamagesInvoiceID AS InvoiceID, ADRVAllocation.ContractNo AS ContractNo, Sum(ADAsset.AssetInvoiceCost) AS SumOfAssetPrice, ADRVAllocation.ADRVAllocation, ADInvoice.InvoiceTotal, ADInvoice.InvoiceTypeID, ADInvoice.InvoiceGeneratedDate "
- strSQl = strSQl & " FROM (ADInvoice LEFT JOIN ADRVAllocation ON ADInvoice.InvoiceID = ADRVAllocation.InvoiceID) LEFT JOIN ADAsset ON (ADRVAllocation.InvoiceID = ADAsset.AssetInvoiceID) AND (ADRVAllocation.ContractNo = ADAsset.AssetContractNo) "
- strSQl = strSQl & " GROUP BY ADInvoice.InvoiceNo, ADInvoice.InvoiceDate, ADAsset.AssetDamagesInvoiceID, ADRVAllocation.ContractNo, ADRVAllocation.ADRVAllocation, ADInvoice.InvoiceTotal, ADInvoice.InvoiceTypeID, ADInvoice.InvoiceGeneratedDate "
- strSQl = strSQl & " HAVING (((ADAsset.AssetDamagesInvoiceID) Is Not Null) AND ((ADRVAllocation.ContractNo)='" & Trim(Nz(strcontractno, "")) & "') AND ((ADInvoice.InvoiceTypeID)=2)) "
- strSQl = strSQl & " AND " & strSQLConditon & " "
- Else
- strSQl = strSQl & " SELECT DISTINCT ADInvoice.InvoiceNo, ADInvoice.InvoiceDate, ADInvoice.InvoiceID AS InvoiceID, ADRVAllocation.ContractNo AS ContractNo, Sum(ADAsset.AssetPrice) AS SumOfAssetPrice, ADRVAllocation.ADRVAllocation, ADInvoice.InvoiceTotal, ADInvoice.InvoiceTypeID, ADInvoice.InvoiceGeneratedDate "
- strSQl = strSQl & " FROM (ADInvoice LEFT JOIN ADRVAllocation ON ADInvoice.InvoiceID = ADRVAllocation.InvoiceID) LEFT JOIN ADAsset ON (ADRVAllocation.InvoiceID = ADAsset.AssetInvoiceID) AND (ADRVAllocation.ContractNo = ADAsset.AssetContractNo) "
- strSQl = strSQl & " GROUP BY ADInvoice.InvoiceNo, ADInvoice.InvoiceDate, ADInvoice.InvoiceID, ADRVAllocation.ContractNo, ADRVAllocation.ADRVAllocation, ADInvoice.InvoiceTotal, ADInvoice.InvoiceTypeID, ADInvoice.InvoiceGeneratedDate "
- strSQl = strSQl & " HAVING (((ADRVAllocation.ContractNo)='" & Trim(Nz(strcontractno, "")) & "') AND ((ADInvoice.InvoiceTypeID)=2)) "
- strSQl = strSQl & " Union "
- strSQl = strSQl & " SELECT DISTINCT ADInvoice.InvoiceNo, ADInvoice.InvoiceDate, ADAsset.AssetDamagesInvoiceID AS InvoiceID, ADRVAllocation.ContractNo AS ContractNo, Sum(ADAsset.AssetInvoiceCost) AS SumOfAssetPrice, ADRVAllocation.ADRVAllocation, ADInvoice.InvoiceTotal, ADInvoice.InvoiceTypeID, ADInvoice.InvoiceGeneratedDate "
- strSQl = strSQl & " FROM (ADInvoice LEFT JOIN ADRVAllocation ON ADInvoice.InvoiceID = ADRVAllocation.InvoiceID) LEFT JOIN ADAsset ON (ADRVAllocation.InvoiceID = ADAsset.AssetInvoiceID) AND (ADRVAllocation.ContractNo = ADAsset.AssetContractNo) "
- strSQl = strSQl & " GROUP BY ADInvoice.InvoiceNo, ADInvoice.InvoiceDate, ADAsset.AssetDamagesInvoiceID, ADRVAllocation.ContractNo, ADRVAllocation.ADRVAllocation, ADInvoice.InvoiceTotal, ADInvoice.InvoiceTypeID, ADInvoice.InvoiceGeneratedDate "
- strSQl = strSQl & " HAVING (((ADAsset.AssetDamagesInvoiceID) Is Not Null) AND ((ADRVAllocation.ContractNo)='" & Trim(Nz(strcontractno, "")) & "') AND ((ADInvoice.InvoiceTypeID)=2)) "
- End If
- Set rstInvoice = Nothing
- Set rstInvoice = db.OpenRecordset(strSQl, dbOpenDynaset, dbSeeChanges)
- If Not rstInvoice.EOF Then
- rstInvoice.MoveFirst
- While Not rstInvoice.EOF
- strInvoiceNo = "": strInvoiceDate = "": strInvoiceAmount = "": strInvoiceType = "": strAllocationAmount = ""
- strBillingPeriod = ""
- If Not IsNull(rstInvoice!InvoiceID) Then
- If Trim(rstInvoice!InvoiceID) <> "" Then
- strInvoiceID = CStr(Trim(rstInvoice!InvoiceID))
- End If
- End If
- If Not IsNull(rstInvoice!InvoiceNo) Then
- If Trim(rstInvoice!InvoiceNo) <> "" Then
- strInvoiceNo = CStr(Trim(rstInvoice!InvoiceNo))
- End If
- End If
- If Not IsNull(rstInvoice!InvoiceDate) Then
- If Trim(rstInvoice!InvoiceDate) <> "" Then
- strInvoiceDate = CStr(Trim(rstInvoice!InvoiceDate))
- End If
- End If
- If Not IsNull(rstInvoice!InvoiceGeneratedDate) Then
- If Trim(rstInvoice!InvoiceGeneratedDate) <> "" Then
- strInvGeneratedDate = CStr(Trim(rstInvoice!InvoiceGeneratedDate))
- End If
- End If
- If Not IsNull(rstInvoice!InvoiceTotal) Then
- If Trim(rstInvoice!InvoiceTotal) <> "" Then
- strInvoiceAmount = CStr(Trim(rstInvoice!InvoiceTotal))
- End If
- End If
- If Not IsNull(rstInvoice!ADRVAllocation) Then
- If Trim(rstInvoice!ADRVAllocation) <> "" Then
- strAllocationAmount = CStr(Trim(rstInvoice!ADRVAllocation))
- End If
- End If
- If Not IsNull(rstInvoice!InvoiceTypeID) Then
- If val(Nz(rstInvoice!InvoiceTypeID, 0)) = 1 Then
- strInvoiceType = CStr(Trim("Sales Invoice"))
- ElseIf val(Nz(rstInvoice!InvoiceTypeID, 0)) = 2 Then
- strInvoiceType = CStr(Trim("Repair Invoice"))
- End If
- Else
- strInvoiceType = CStr(Trim("Repair Invoice"))
- End If
- Set rst = db.OpenRecordset("SELECT * FROM ztSecondaryBilling WHERE InvoiceNo = '" & strInvoiceNo & "' AND InvoiceDate=" & g_sDBDateChr & strInvoiceDate & g_sDBDateChr & " ", dbOpenDynaset, dbSeeChanges)
- If Not rst.EOF Then
- strInvoiceAmount = 0
- End If
- rst.Close
- strUpdate = ""
- strUpdate = strUpdate & " INSERT INTO ztSecondaryBilling "
- strUpdate = strUpdate & " ( "
- strUpdate = strUpdate & " CustomerName " & " "
- strUpdate = strUpdate & " , ContractNo " & " "
- strUpdate = strUpdate & " , AccountManager " & " "
- strUpdate = strUpdate & " , InvoiceID " & " "
- strUpdate = strUpdate & " , InvoiceNo " & " "
- strUpdate = strUpdate & " , InvoiceDate " & " "
- If Trim(strInvGeneratedDate) <> "" Then
- strUpdate = strUpdate & " , InvoiceGeneratedDate " & " "
- End If
- strUpdate = strUpdate & " , InvocieAmount " & " "
- strUpdate = strUpdate & " , NominalRV " & " "
- strUpdate = strUpdate & " , PVOfRV " & " "
- strUpdate = strUpdate & " , GRV " & " "
- strUpdate = strUpdate & " , InvoiceRecovery " & " "
- strUpdate = strUpdate & " , InvoiceType " & " "
- strUpdate = strUpdate & " , ADCustomerName " & " "
- strUpdate = strUpdate & " ) VALUES ( "
- strUpdate = strUpdate & " '" & strCustomerName & "' "
- strUpdate = strUpdate & " , '" & strcontractno & "' "
- strUpdate = strUpdate & " , '" & strAccountManager & "' "
- strUpdate = strUpdate & " , " & val(strInvoiceID) & " "
- strUpdate = strUpdate & " , '" & strInvoiceNo & "' "
- strUpdate = strUpdate & " , " & g_sDBDateChr & strInvoiceDate & g_sDBDateChr & " "
- If Trim(strInvGeneratedDate) <> "" Then
- strUpdate = strUpdate & " , " & g_sDBDateChr & strInvGeneratedDate & g_sDBDateChr & " "
- End If
- strUpdate = strUpdate & " , " & val(strInvoiceAmount) & " "
- If blnGroup1 = False Then
- strUpdate = strUpdate & " , " & val(strNominalRV) & " "
- strUpdate = strUpdate & " , " & val(strPVOfRV) & " "
- strUpdate = strUpdate & " , " & val(strGRV) & " "
- blnGroup1 = True
- Else
- strUpdate = strUpdate & " , 0 "
- strUpdate = strUpdate & " , 0 "
- strUpdate = strUpdate & " , 0 "
- End If
- strUpdate = strUpdate & " , " & val(strAllocationAmount) & " "
- strUpdate = strUpdate & " , '" & CStr(strInvoiceType) & "' "
- strUpdate = strUpdate & " , '" & CStr(strADCustomerName) & "' "
- strUpdate = strUpdate & " ) "
- If Trim(strUpdate) <> "" Then
- db.Execute strUpdate, dbSeeChanges
- End If
- rstInvoice.MoveNext
- Wend
- End If
- End If
- Dim strCreditNoteAdjustedInvoice As String
- strCreditNoteAdjustedInvoice = ""
- '''''' Credit Note
- If chkCreditNote Then
- Set rstInvoice = Nothing
- strSQLConditon = ""
- If Trim(strInvDueFromDate) <> "" Then
- If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
- strSQLConditon = strSQLConditon & " CreditNote.creditdate >= Format(" & g_sDBDateChr & Trim(strInvDueFromDate) & g_sDBDateChr & ",'dd-mmm-yyyy') "
- End If
- If Trim(strInvDueToDate) <> "" Then
- If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
- strSQLConditon = strSQLConditon & " CreditNote.creditdate <= Format(" & g_sDBDateChr & Trim(strInvDueToDate) & g_sDBDateChr & ",'dd-mmm-yyyy') "
- End If
- If Trim(strGeneratedFromDate) <> "" Then
- If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
- strSQLConditon = strSQLConditon & " CreditNote.CreditNoteGeneratedDate >= Format(" & g_sDBDateChr & Left(strGeneratedFromDate, 2) + "-" + Mid(strGeneratedFromDate, 4, 3) + "-" + Right(strGeneratedFromDate, 4) & g_sDBDateChr & ",'dd-mmm-yyyy') "
- End If
- If Trim(strGeneratedToDate) <> "" Then
- If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
- strSQLConditon = strSQLConditon & " CreditNote.CreditNoteGeneratedDate <= Format(" & g_sDBDateChr & Left(strGeneratedToDate, 2) + "-" + Mid(strGeneratedToDate, 4, 3) + "-" + Right(strGeneratedToDate, 4) & g_sDBDateChr & ",'dd-mmm-yyyy') "
- End If
- If Trim(strSQLConditon) <> "" Then
- 'strSQL = "SELECT CreditNote.CreditID, CreditNote.CreditNoteNo, CreditNote.CreditDate, CreditNote.Customer_ID, CreditNote.Contract_No, CreditNote.TotalAmount, CreditNote.AdjustedPVofRV, CreditNote.CreditNoteGeneratedDate, CreditNoteDetail.InvoiceID, CreditNoteDetail.InvoiceNo, CreditNoteDetail.InvoiceDate, CreditNoteDetail.RentalAmount, CreditNoteDetail.LeaseTaxAmount"
- 'strSQL = strSQL & " FROM CreditNote INNER JOIN CreditNoteDetail ON CreditNote.CreditID = CreditNoteDetail.CreditID "
- 'strSQL = strSQL & " WHERE (((CreditNote.Contract_No)='" & Nz(strContractNo, "") & "')) "
- 'strSQL = strSQL & " AND " & strSQLConditon & " "
- ' strSQL = " SELECT CreditNote.CreditID, CreditNote.CreditNoteNo, CreditNote.CreditDate, CreditNote.Customer_ID, CreditNoteRVAllocation.Contract_No, CreditNote.TotalAmount, CreditNoteRVAllocation.AdjustedAmount, CreditNote.CreditNoteGeneratedDate, CreditNoteDetail.InvoiceID, CreditNoteDetail.InvoiceNo, CreditNoteDetail.InvoiceDate, CreditNoteDetail.RentalAmount, CreditNoteDetail.LeaseTaxAmount "
- ' strSQL = strSQL & " FROM (CreditNote INNER JOIN CreditNoteDetail ON CreditNote.CreditID = CreditNoteDetail.CreditID) LEFT JOIN CreditNoteRVAllocation ON CreditNote.CreditID = CreditNoteRVAllocation.CreditID "
- ' strSQL = strSQL & " WHERE (((CreditNoteRVAllocation.Contract_No)='" & Nz(strContractNo, "") & "')) "
- ' strSQL = strSQL & " AND " & strSQLConditon & " "
- strSQl = " SELECT CreditNote.CreditID, CreditNote.CreditNoteNo, CreditNote.CreditDate, CreditNote.Customer_ID, CreditNoteRVAllocation.Contract_No, CreditNote.TotalAmount, CreditNoteRVAllocation.AdjustedAmount, CreditNote.CreditNoteGeneratedDate "
- strSQl = strSQl & " FROM CreditNote LEFT JOIN CreditNoteRVAllocation ON CreditNote.CreditID = CreditNoteRVAllocation.CreditID "
- strSQl = strSQl & " WHERE (((CreditNoteRVAllocation.Contract_No)='" & Nz(strcontractno, "") & "')) "
- strSQl = strSQl & " AND " & strSQLConditon & " "
- Else
- 'strSQL = "SELECT CreditNote.CreditID, CreditNote.CreditNoteNo, CreditNote.CreditDate, CreditNote.Customer_ID, CreditNote.Contract_No, CreditNote.TotalAmount, CreditNote.AdjustedPVofRV, CreditNote.CreditNoteGeneratedDate, CreditNoteDetail.InvoiceID, CreditNoteDetail.InvoiceNo, CreditNoteDetail.InvoiceDate, CreditNoteDetail.RentalAmount, CreditNoteDetail.LeaseTaxAmount"
- 'strSQL = strSQL & " FROM CreditNote INNER JOIN CreditNoteDetail ON CreditNote.CreditID = CreditNoteDetail.CreditID "
- 'strSQL = strSQL & " WHERE (((CreditNote.Contract_No)='" & Nz(strContractNo, "") & "')) "
- ' strSQL = " SELECT CreditNote.CreditID, CreditNote.CreditNoteNo, CreditNote.CreditDate, CreditNote.Customer_ID, CreditNoteRVAllocation.Contract_No, CreditNote.TotalAmount, CreditNoteRVAllocation.AdjustedAmount, CreditNote.CreditNoteGeneratedDate, CreditNoteDetail.InvoiceID, CreditNoteDetail.InvoiceNo, CreditNoteDetail.InvoiceDate, CreditNoteDetail.RentalAmount, CreditNoteDetail.LeaseTaxAmount "
- ' strSQL = strSQL & " FROM (CreditNote INNER JOIN CreditNoteDetail ON CreditNote.CreditID = CreditNoteDetail.CreditID) LEFT JOIN CreditNoteRVAllocation ON CreditNote.CreditID = CreditNoteRVAllocation.CreditID "
- ' strSQL = strSQL & " WHERE (((CreditNoteRVAllocation.Contract_No)='" & Nz(strContractNo, "") & "')) "
- strSQl = " SELECT CreditNote.CreditID, CreditNote.CreditNoteNo, CreditNote.CreditDate, CreditNote.Customer_ID, CreditNoteRVAllocation.Contract_No, CreditNote.TotalAmount, CreditNoteRVAllocation.AdjustedAmount, CreditNote.CreditNoteGeneratedDate "
- strSQl = strSQl & " FROM CreditNote LEFT JOIN CreditNoteRVAllocation ON CreditNote.CreditID = CreditNoteRVAllocation.CreditID "
- strSQl = strSQl & " WHERE (((CreditNoteRVAllocation.Contract_No)='" & Nz(strcontractno, "") & "')) "
- End If
- Set rstInvoice = db.OpenRecordset(strSQl, dbOpenDynaset, dbSeeChanges)
- If Not rstInvoice.EOF Then
- rstInvoice.MoveFirst
- While Not rstInvoice.EOF
- strInvoiceNo = "": strInvoiceDate = "": strInvoiceAmount = "": strInvoiceType = "": strAllocationAmount = ""
- strBillingPeriod = ""
- If Not IsNull(rstInvoice!creditid) Then
- If Trim(rstInvoice!creditid) <> "" Then
- strInvoiceID = CStr(Trim(rstInvoice!creditid))
- End If
- End If
- If Not IsNull(rstInvoice!creditnoteno) Then
- If Trim(rstInvoice!creditnoteno) <> "" Then
- strInvoiceNo = CStr(Trim(rstInvoice!creditnoteno))
- End If
- End If
- If Not IsNull(rstInvoice!creditdate) Then
- If Trim(rstInvoice!creditdate) <> "" Then
- strInvoiceDate = CStr(Trim(rstInvoice!creditdate))
- End If
- End If
- If Not IsNull(rstInvoice!CreditNoteGeneratedDate) Then
- If Trim(rstInvoice!CreditNoteGeneratedDate) <> "" Then
- strInvGeneratedDate = CStr(Trim(rstInvoice!CreditNoteGeneratedDate))
- End If
- End If
- Set rst = db.OpenRecordset("SELECT (SUM(CreditNoteDetail.RentalAmount)+SUM(CreditNoteDetail.ServiceRental)) AS TotalAmount FROM CreditNoteDetail WHERE CreditNoteDetail.CreditID = " & val(Nz(rstInvoice!creditid, 0)) & " ", dbOpenDynaset, dbSeeChanges)
- If Not rst.EOF Then
- If Not IsNull(rst!TotalAmount) Then
- If Trim(rst!TotalAmount) <> "" Then
- strInvoiceAmount = CStr(Trim(rst!TotalAmount))
- End If
- End If
- End If
- rst.Close
- ' If Not IsNull(rstInvoice!AdjustedPVofRV) Then
- ' If Trim(rstInvoice!AdjustedPVofRV) <> "" Then
- ' strInvoiceAmount = CStr(Trim(rstInvoice!AdjustedPVofRV))
- ' End If
- ' End If
- If Not IsNull(rstInvoice!AdjustedAmount) Then
- If Trim(rstInvoice!AdjustedAmount) <> "" Then
- strAllocationAmount = CStr(Trim(rstInvoice!AdjustedAmount))
- End If
- End If
- strCreditNoteAdjustedInvoice = ""
- Set rst = db.OpenRecordset("SELECT InvoiceNo FROM CreditNoteDetail WHERE CreditNoteDetail.CreditID = " & val(Nz(rstInvoice!creditid, 0)) & " ", dbOpenDynaset, dbSeeChanges)
- If Not rst.EOF Then
- rst.MoveFirst
- While Not rst.EOF
- If Trim(strCreditNoteAdjustedInvoice) <> "" Then strCreditNoteAdjustedInvoice = strCreditNoteAdjustedInvoice & "; "
- strCreditNoteAdjustedInvoice = strCreditNoteAdjustedInvoice & NZZ(rst!InvoiceNo)
- rst.MoveNext
- Wend
- End If
- rst.Close
- ' If Not IsNull(rstInvoice!InvoiceDate) Then
- ' If Trim(rstInvoice!InvoiceDate) <> "" Then
- ' 'strCreditNoteAdjustedInvoice = Right(Year(Trim(rstInvoice!InvoiceDate)), 2) & String(2 - Len(Month(Trim(rstInvoice!InvoiceDate))), "0") & Month(Trim(rstInvoice!InvoiceDate)) & "-" & Trim(strContractNo)
- ' strCreditNoteAdjustedInvoice = CStr(Trim(rstInvoice!InvoiceNo))
- ' End If
- ' End If
- strInvoiceType = CStr(Trim("Credit Note"))
- Set rst = db.OpenRecordset("SELECT * FROM ztSecondaryBilling WHERE InvoiceNo = '" & strInvoiceNo & "' AND InvoiceDate=" & g_sDBDateChr & strInvoiceDate & g_sDBDateChr & " ", dbOpenDynaset, dbSeeChanges)
- If Not rst.EOF Then
- strInvoiceAmount = 0
- End If
- rst.Close
- strUpdate = ""
- strUpdate = strUpdate & " INSERT INTO ztSecondaryBilling "
- strUpdate = strUpdate & " ( "
- strUpdate = strUpdate & " CustomerName " & " "
- strUpdate = strUpdate & " , ContractNo " & " "
- strUpdate = strUpdate & " , AccountManager " & " "
- strUpdate = strUpdate & " , InvoiceID " & " "
- strUpdate = strUpdate & " , InvoiceNo " & " "
- strUpdate = strUpdate & " , InvoiceDate " & " "
- If Trim(strInvGeneratedDate) <> "" Then
- strUpdate = strUpdate & " , InvoiceGeneratedDate " & " "
- End If
- strUpdate = strUpdate & " , InvocieAmount " & " "
- strUpdate = strUpdate & " , NominalRV " & " "
- strUpdate = strUpdate & " , PVOfRV " & " "
- strUpdate = strUpdate & " , GRV " & " "
- strUpdate = strUpdate & " , InvoiceRecovery " & " "
- strUpdate = strUpdate & " , InvoiceType " & " "
- strUpdate = strUpdate & " , ADCustomerName " & " "
- strUpdate = strUpdate & " ) VALUES ( "
- strUpdate = strUpdate & " '" & strCustomerName & "' "
- strUpdate = strUpdate & " , '" & strcontractno & "' "
- strUpdate = strUpdate & " , '" & strAccountManager & "' "
- strUpdate = strUpdate & " , " & val(strInvoiceID) & " "
- strUpdate = strUpdate & " , '" & strInvoiceNo & "' "
- strUpdate = strUpdate & " , " & g_sDBDateChr & strInvoiceDate & g_sDBDateChr & " "
- If Trim(strInvGeneratedDate) <> "" Then
- strUpdate = strUpdate & " , " & g_sDBDateChr & strInvGeneratedDate & g_sDBDateChr & " "
- End If
- strUpdate = strUpdate & " , " & val(strInvoiceAmount) & " "
- If blnGroup1 = False Then
- strUpdate = strUpdate & " , " & val(strNominalRV) & " "
- strUpdate = strUpdate & " , " & val(strPVOfRV) & " "
- strUpdate = strUpdate & " , " & val(strGRV) & " "
- blnGroup1 = True
- Else
- strUpdate = strUpdate & " , 0 "
- strUpdate = strUpdate & " , 0 "
- strUpdate = strUpdate & " , 0 "
- End If
- strUpdate = strUpdate & " , " & val(strAllocationAmount) & " "
- strUpdate = strUpdate & " , '" & CStr(strInvoiceType) & "' "
- strUpdate = strUpdate & " , '" & CStr(strADCustomerName) & "' "
- strUpdate = strUpdate & " ) "
- If Trim(strUpdate) <> "" Then
- db.Execute strUpdate, dbSeeChanges
- End If
- rstInvoice.MoveNext
- Wend
- End If
- End If
- '''''' Short Term
- If chkShortTerm Then
- Set rstInvoice = Nothing
- strSQLConditon = ""
- If Trim(strInvDueFromDate) <> "" Then
- If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
- strSQLConditon = strSQLConditon & " ShortTermRVAllocation.STCashFlowDate>= Format(" & g_sDBDateChr & Trim(strInvDueFromDate) & g_sDBDateChr & ",'dd-mmm-yyyy') "
- End If
- If Trim(strInvDueToDate) <> "" Then
- If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
- strSQLConditon = strSQLConditon & " ShortTermRVAllocation.STCashFlowDate<= Format(" & g_sDBDateChr & Trim(strInvDueToDate) & g_sDBDateChr & ",'dd-mmm-yyyy') "
- End If
- If Trim(strGeneratedFromDate) <> "" Then
- If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
- strSQLConditon = strSQLConditon & " ShortTermRVAllocation.STRVAllocatedDate>= Format(" & g_sDBDateChr & Left(strGeneratedFromDate, 2) + "-" + Mid(strGeneratedFromDate, 4, 3) + "-" + Right(strGeneratedFromDate, 4) & g_sDBDateChr & ",'dd-mmm-yyyy') "
- End If
- If Trim(strGeneratedToDate) <> "" Then
- If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
- strSQLConditon = strSQLConditon & " ShortTermRVAllocation.STRVAllocatedDate<= Format(" & g_sDBDateChr & Left(strGeneratedToDate, 2) + "-" + Mid(strGeneratedToDate, 4, 3) + "-" + Right(strGeneratedToDate, 4) & g_sDBDateChr & ",'dd-mmm-yyyy') "
- End If
- If Trim(strSQLConditon) <> "" Then
- strSQl = " SELECT ShortTermRVAllocation.STAlloc_ID, ShortTermRVAllocation.AllocContract_No, ShortTermRVAllocation.STContract_No, ShortTermRVAllocation.STCashFlowNumber, ShortTermRVAllocation.STCashFlowDate, nz([CashflowAmount],0)+nz([CashFlowServiceRental],0) AS InvoiceAmount, ShortTermRVAllocation.AllocAmount, ShortTermRVAllocation.STRVAllocatedDate "
- strSQl = strSQl & " FROM ShortTermRVAllocation INNER JOIN Contract_cashflow ON (ShortTermRVAllocation.STContract_No = Contract_cashflow.CashflowContractNo) AND (ShortTermRVAllocation.STCashFlowNumber = Contract_cashflow.CashflowNumber) "
- strSQl = strSQl & " WHERE (((ShortTermRVAllocation.AllocContract_No) = '" & Nz(strcontractno, "") & "')) "
- strSQl = strSQl & " AND " & strSQLConditon & " "
- strSQl = strSQl & " ORDER BY ShortTermRVAllocation.AllocContract_No, ShortTermRVAllocation.STContract_No, ShortTermRVAllocation.STCashFlowDate "
- Else
- strSQl = " SELECT ShortTermRVAllocation.STAlloc_ID, ShortTermRVAllocation.AllocContract_No, ShortTermRVAllocation.STContract_No, ShortTermRVAllocation.STCashFlowNumber, ShortTermRVAllocation.STCashFlowDate, nz([CashflowAmount],0)+nz([CashFlowServiceRental],0) AS InvoiceAmount, ShortTermRVAllocation.AllocAmount, ShortTermRVAllocation.STRVAllocatedDate "
- strSQl = strSQl & " FROM ShortTermRVAllocation INNER JOIN Contract_cashflow ON (ShortTermRVAllocation.STContract_No = Contract_cashflow.CashflowContractNo) AND (ShortTermRVAllocation.STCashFlowNumber = Contract_cashflow.CashflowNumber) "
- strSQl = strSQl & " WHERE (((ShortTermRVAllocation.AllocContract_No) = '" & Nz(strcontractno, "") & "')) "
- strSQl = strSQl & " ORDER BY ShortTermRVAllocation.AllocContract_No, ShortTermRVAllocation.STContract_No, ShortTermRVAllocation.STCashFlowDate "
- End If
- Set rstInvoice = db.OpenRecordset(strSQl, dbOpenDynaset, dbSeeChanges)
- If Not rstInvoice.EOF Then
- rstInvoice.MoveFirst
- While Not rstInvoice.EOF
- strInvoiceNo = "": strInvoiceDate = "": strInvoiceAmount = "": strInvoiceType = "": strAllocationAmount = ""
- strRewriteContractNo = ""
- strBillingPeriod = ""
- strADCustomerName = ""
- strInvGeneratedDate = ""
- If Not IsNull(rstInvoice!STAlloc_ID) Then
- If Trim(rstInvoice!STAlloc_ID) <> "" Then
- strInvoiceID = CStr(Trim(rstInvoice!STAlloc_ID))
- End If
- End If
- If Not IsNull(rstInvoice!STContract_No) Then
- If Trim(rstInvoice!STContract_No) <> "" Then
- strInvoiceNo = CStr(Trim(rstInvoice!STContract_No))
- End If
- End If
- If Not IsNull(rstInvoice!STCashFlowDate) Then
- If Trim(rstInvoice!STCashFlowDate) <> "" Then
- strInvoiceDate = CStr(Trim(rstInvoice!STCashFlowDate))
- End If
- End If
- If Not IsNull(rstInvoice!InvoiceAmount) Then
- If Trim(rstInvoice!InvoiceAmount) <> "" Then
- strInvoiceAmount = CStr(Trim(rstInvoice!InvoiceAmount))
- End If
- End If
- If Not IsNull(rstInvoice!AllocAmount) Then
- If Trim(rstInvoice!AllocAmount) <> "" Then
- strAllocationAmount = CStr(Trim(rstInvoice!AllocAmount))
- End If
- End If
- If Not IsNull(rstInvoice!STRVAllocatedDate) Then
- If Trim(rstInvoice!STRVAllocatedDate) <> "" Then
- strInvGeneratedDate = CStr(Trim(rstInvoice!STRVAllocatedDate))
- End If
- End If
- strInvoiceType = CStr(Trim("Short Term"))
- Set rst = db.OpenRecordset("SELECT * FROM ztSecondaryBilling WHERE InvoiceNo = '" & strInvoiceNo & "' AND InvoiceDate=" & g_sDBDateChr & strInvoiceDate & g_sDBDateChr & " ", dbOpenDynaset, dbSeeChanges)
- If Not rst.EOF Then
- strInvoiceAmount = 0
- End If
- rst.Close
- strUpdate = ""
- strUpdate = strUpdate & " INSERT INTO ztSecondaryBilling "
- strUpdate = strUpdate & " ( "
- strUpdate = strUpdate & " CustomerName " & " "
- strUpdate = strUpdate & " , ContractNo " & " "
- strUpdate = strUpdate & " , AccountManager " & " "
- strUpdate = strUpdate & " , InvoiceID " & " "
- strUpdate = strUpdate & " , InvoiceNo " & " "
- strUpdate = strUpdate & " , InvoiceDate " & " "
- If Trim(strInvGeneratedDate) <> "" Then
- strUpdate = strUpdate & " , InvoiceGeneratedDate " & " "
- End If
- strUpdate = strUpdate & " , InvocieAmount " & " "
- strUpdate = strUpdate & " , NominalRV " & " "
- strUpdate = strUpdate & " , PVOfRV " & " "
- strUpdate = strUpdate & " , GRV " & " "
- strUpdate = strUpdate & " , InvoiceRecovery " & " "
- strUpdate = strUpdate & " , InvoiceType " & " "
- strUpdate = strUpdate & " , ADCustomerName " & " "
- strUpdate = strUpdate & " ) VALUES ( "
- strUpdate = strUpdate & " '" & strCustomerName & "' "
- strUpdate = strUpdate & " , '" & strcontractno & "' "
- strUpdate = strUpdate & " , '" & strAccountManager & "' "
- strUpdate = strUpdate & " , " & val(strInvoiceID) & " "
- strUpdate = strUpdate & " , '" & strInvoiceNo & "' "
- strUpdate = strUpdate & " , " & g_sDBDateChr & strInvoiceDate & g_sDBDateChr & " "
- If Trim(strInvGeneratedDate) <> "" Then
- strUpdate = strUpdate & " , " & g_sDBDateChr & strInvGeneratedDate & g_sDBDateChr & " "
- End If
- strUpdate = strUpdate & " , " & val(strInvoiceAmount) & " "
- If blnGroup1 = False Then
- strUpdate = strUpdate & " , " & val(strNominalRV) & " "
- strUpdate = strUpdate & " , " & val(strPVOfRV) & " "
- strUpdate = strUpdate & " , " & val(strGRV) & " "
- blnGroup1 = True
- Else
- strUpdate = strUpdate & " , 0 "
- strUpdate = strUpdate & " , 0 "
- strUpdate = strUpdate & " , 0 "
- End If
- strUpdate = strUpdate & " , " & val(strAllocationAmount) & " "
- strUpdate = strUpdate & " , '" & CStr(strInvoiceType) & "' "
- strUpdate = strUpdate & " , '" & CStr(strADCustomerName) & "' "
- strUpdate = strUpdate & " ) "
- If Trim(strUpdate) <> "" Then
- db.Execute strUpdate, dbSeeChanges
- End If
- rstInvoice.MoveNext
- Wend
- End If
- End If
- Me.Repaint
- DoEvents
- If val(intCnt) <= val(Nz(ProgressBar.MAX, 0)) Then
- ProgressBar.Value = val(intCnt)
- txtProgerssBar = CStr(Round((val(intCnt) / val(Nz(ProgressBar.MAX, 0))) * 100)) & "%"
- If val(intCnt / 2) = CInt(intCnt / 2) Then
- lblImportCaption.Caption = "Collecting Data ..."
- Else
- lblImportCaption.Caption = "Collecting Data ...."
- End If
- End If
- Me.Repaint
- DoEvents
- rstcontract.MoveNext
- Wend
- End If
- FillTempTable = True
- Exit_FillTempTable:
- On Error Resume Next
- DoCmd.Hourglass False
- ProgressBar.MAX = 1
- ProgressBar.Value = 0
- txtProgerssBar = ""
- lblImportCaption.Caption = ""
- ProgressBar.Visible = False
- txtProgerssBar.Visible = False
- lblImportCaption.Visible = False
- cmdOK.Enabled = True
- DoEvents
- Exit Function
- Err_FillTempTable:
- If Err.Number <> 0 Then
- Select Case Err
- Case 2467
- Case Else
- MsgBox "Error in FillTempTable():" & vbCrLf & Err.Description, vbInformation, "RIMS"
- End Select
- 'Resume
- FillTempTable = False
- Resume Exit_FillTempTable
- End If
- End Function