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
| Public Sub Budget_Mensualisé_Douzième()
Dim db As Database
Dim rs, rs2 As Recordset
Dim sql As String
Dim i As Integer
Set db = CurrentDb
sql = "SELECT * FROM Budget WHERE Type='Budget' AND libellé_N3='Frais postaux' or Type Like 'E?' AND libellé_N3='Frais postaux' UNION "
sql = sql & " SELECT * FROM Budget WHERE Type='Budget' AND libellé_N3='Cessions d''immobilisations corporelles' OR Type Like 'E?' AND libellé_N3='Cessions d''immobilisations corporelles' UNION"
sql = sql & " SELECT * FROM Budget WHERE Type='Budget' AND libellé_N3='Formation' or Type Like 'E?' AND libellé_N3='Formation' UNION "
sql = sql & " SELECT * FROM Budget WHERE Type='Budget' AND libellé_N3='Litiges sociaux' or Type Like 'E?' AND libellé_N3='Litiges sociaux' UNION"
sql = sql & " SELECT * FROM Budget WHERE Type='Budget' AND libellé_N3='Personnel extérieur' or Type Like 'E?' AND libellé_N3='Personnel extérieur' UNION"
sql = sql & " SELECT * FROM Budget WHERE Type='Budget' AND libellé_N3='Charges variables Locaux' or Type Like 'E?' AND libellé_N3='Charges variables Locaux';"
Set rs = db.OpenRecordset(sql)
Set rs2 = db.OpenRecordset(sql)
If rs.EOF = False Then rs.MoveFirst
If rs2.EOF = False Then rs2.MoveFirst
Do While rs.EOF = False And rs2.EOF = False
For i = 1 To 12 Step 1
rs.AddNew
rs!Type = rs2!Type & "_mensualisé_douzième"
rs!Libellé_N3 = rs2!Libellé_N3
rs!CC_DEPT = rs2!CC_DEPT
rs!Année = rs2!Année
rs!Entité = rs2!Entité
rs!Solde = rs2!Solde / 12
rs!Mois = i
rs.Update
Next
rs.MoveNext
rs2.MoveNext
Loop
DoCmd.RunSQL ("INSERT INTO Archive_Budget VALUES(Date(), 'Budget_mensualisé_douzième','8.333','8.333','8.333','8.333','8.333','8.333','8.333','8.333','8.333','8.333','8.333','8.333')")
End Sub |
Partager