1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120
| Private Sub Balance()
Dim sql As String
Dim sqlTemp As String
Dim rstSrce As DAO.Recordset
Dim rstDest As DAO.Recordset
Dim rstTemp As DAO.Recordset
Dim Compte As String
Dim Espace As String
Dim Cpte1 As String
Dim Cpte2 As String
Dim Cpte3 As String
Dim Z As Integer
Dim I As Long
Dim LC As String
' Suppression du contenu des tables
CurrentDb.Execute "DELETE * FROM tblBalanceCredit_1"
CurrentDb.Execute "DELETE * FROM tblBalanceDebit_1"
CurrentDb.Execute "DELETE * FROM tblBalanceDebitCredit_2"
CurrentDb.Execute "DELETE * FROM tblBalanceDebitCredit_3"
CurrentDb.Execute "DELETE * FROM tblBalanceDebitCredit_4"
' Remplissage des tables tblBalanceCredit_1 & tblBalanceDebit_1
DoCmd.OpenQuery "qryBalanceC"
DoCmd.OpenQuery "qryBalanceD"
' Remplissage conditionnel de la tblBalanceDebitCredit_2
If Me.CptesCentre = True Then ' Montre les comptes centralisateurs
If Me.CptesNuls = True Then ' >> Montre les comptes nuls
DoCmd.OpenQuery "qryBalanceCD_1"
Else
DoCmd.OpenQuery "qryBalanceCD_2" ' >> Ne montre pas les comptes nuls
End If
Else ' Ne montre pas les comptes centralisateurs
If Me.CptesNuls = True Then ' >> Montre les comptes nuls
DoCmd.OpenQuery "qryBalanceCD_3"
Else
DoCmd.OpenQuery "qryBalanceCD_4" ' >> Ne montre pas les comptes nuls
End If
GoTo Fin
End If
Set rstSrce = CurrentDb.OpenRecordset("SELECT * FROM tblBalanceDebitCredit_2 ORDER BY NumCompte")
Set rstDest = CurrentDb.OpenRecordset("tblBalanceDebitCredit_3")
With rstSrce
.MoveLast
.MoveFirst
For I = 1 To .RecordCount
' Insertion d'une ligne vide entre les comptes centralisateurs de base
LC = Len(!NumCompte)
If I > 1 And LC = 1 Then
rstDest.AddNew
Compte = Val(!NumCompte) - 1 & "9999999"
rstDest!NumCompte = Compte
rstDest.Update
End If
rstDest.AddNew
' Traitement du N° de compte
rstDest!NumCompte = !NumCompte
' Traitement du libellé du compte
Espace = ""
For Z = 4 To LC
Espace = Espace & "> "
Next Z
rstDest!Libelle = Espace & !Libelle
' Traitement des montants et des différents totaux(Débit, Crédit)
Cpte1 = left(!NumCompte, 1)
Cpte2 = left(!NumCompte, 2)
Cpte3 = left(!NumCompte, 3)
Select Case LC
Case "1"
sqlTemp = "SELECT '" & Cpte1 & "' AS Cpte, Sum(MontantCredit) AS SC, Sum(MontantDebit) AS SD, Sum(Solde) AS SS " & _
"From tblBalanceDebitCredit_2 WHERE (((NumCompte) Like '" & Cpte1 & "???" & "'))"
Case "2"
sqlTemp = "SELECT '" & Cpte2 & "' AS Cpte, Sum(MontantCredit) AS SC, Sum(MontantDebit) AS SD, Sum(Solde) AS SS " & _
"From tblBalanceDebitCredit_2 WHERE (((NumCompte) Like '" & Cpte2 & "??" & "'))"
Case "3"
sqlTemp = "SELECT '" & Cpte3 & "' AS Cpte, Sum(MontantCredit) AS SC, Sum(MontantDebit) AS SD, Sum(Solde) AS SS " & _
"From tblBalanceDebitCredit_2 WHERE (((NumCompte) Like '" & Cpte3 & "?" & "'))"
Case Else
rstDest!MontantCredit = !MontantCredit
rstDest!MontantDebit = !MontantDebit
GoTo Suite
End Select
Set rstTemp = CurrentDb.OpenRecordset(sqlTemp)
rstDest!MontantCredit = Nz(rstTemp!SC)
rstDest!MontantDebit = Nz(rstTemp!SD)
Suite:
' Calcul du solde
If !Solde = 0 Then
rstDest!Solde = Nz(rstDest!MontantDebit) - Nz(rstDest!MontantCredit) ' Calcul du solde pour comptes centralisateurs
Else
rstDest!Solde = !Solde
End If
rstDest.Update
.MoveNext
Next I
End With
Set rstDest = Nothing
Set rstSrce = Nothing
Set rstTemp = Nothing
Fin:
' Supprime les lignes vides
DoCmd.OpenQuery "qryBalanceCD_5"
' Traitements des formulaires
DoCmd.OpenForm "frmBalance"
Forms!frmBalance.Recalc
DoCmd.Close acForm, Me.name
End Sub |
Partager