How to reslove ODBC 3151: Error

syedshaffee
New Member
Join Date: Jan 2012
Posts: 91
How to reslove ODBC 3151: Error

Feb 6 '12, 04:49 AM
hey people ,

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
Code:
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
i known its quite large so please be patient and please suggest me some thing
Tags: None
Rabbit

Recognized Expert MVP

Join Date: Jan 2007

Posts: 12517
#2

Feb 6 '12, 04:56 AM

It would help to know what line the error is on.
Comment
NeoPa

Recognized Expert Moderator MVP

Join Date: Oct 2006

Posts: 32633
#3

Feb 6 '12, 10:25 AM

Please read the instruction at Before Posting (VBA or SQL) Code. Also, please refrain from posting the same question in more than one place. This is even less helpful than posting over a thousand lines of code with no indication of where in the code the problem occurs.

PS. If you have a procedure that contains over a thousand lines of code then this is a big hint that you're doing something very wrong indeed. That's a totally inappropriate size for a single procedure.

Last edited by NeoPa; Feb 6 '12, 10:28 AM.
Comment
Previous template Next
How to reslove ODBC 3151: Error

How to reslove ODBC 3151: Error

Comment

Comment
