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 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173
| Public Sub BUDGET()
'variables RECORDSET
Dim RS1 As DAO.Recordset
Dim RS1L As Integer
Dim RS2 As DAO.Recordset
Dim RS2L As Integer
Dim RS3 As DAO.Recordset
Dim RS3L As Integer
'variables programe
Dim ty As String
Dim st As String
Dim x As Integer
Dim arg As String
Dim rec As String
Dim var As String
Dim champ As String
Dim trouve As String
Dim KM As Double
Dim Deb As Date
Dim fini As Date
'variable de transduction
Dim para(1 To 8, 1 To 5) As Variant
para(8, 1) = "PV"
para(8, 2) = "PV"
para(8, 3) = ""
para(8, 4) = ""
para(8, 5) = ""
para(2, 1) = "CA"
para(2, 2) = "CAB"
para(2, 3) = "CAB"
para(2, 4) = "CAB"
para(2, 5) = "CAB"
para(3, 1) = "TRT"
para(3, 2) = "EFPPM"
para(3, 3) = "EFTR"
para(3, 4) = ""
para(3, 5) = ""
para(4, 1) = "KM"
para(4, 2) = "KMS"
para(4, 3) = "KMSTR"
para(4, 4) = ""
para(4, 5) = ""
para(7, 1) = "VIDE"
para(7, 2) = "TKV"
para(7, 3) = ""
para(7, 4) = ""
para(7, 5) = ""
para(6, 1) = "MARGE"
para(6, 2) = ""
para(6, 3) = ""
para(6, 4) = "MNET"
para(6, 5) = "MNET.AO"
para(5, 1) = "CAT"
para(5, 2) = ""
para(5, 3) = ""
para(5, 4) = ""
para(5, 5) = "CAB"
para(1, 1) = "CAN"
para(1, 2) = "CAN"
para(1, 3) = "CANS"
para(1, 4) = "CANS"
para(1, 5) = "CANT"
'Definition des recordset
Set RS1 = Application.CurrentDb.OpenRecordset("SERVICE", dbOpenTable, dbReadOnly)
RS1L = RS1.RecordCount
Set RS2 = Application.CurrentDb.OpenRecordset("BUDGET", dbOpenTable)
RS2L = RS2.RecordCount
Set RS3 = Application.CurrentDb.OpenRecordset("BUDGETTEMP", dbOpenTable)
RS3L = RS3.RecordCount
RS1.MoveFirst
For i = 1 To RS1L
ty = RS1.Fields("TYPE").Value
st = RS1.Fields("SOUS TYPE").Value
arg = RS1.Fields("REF").Value
If arg Like "NB" Then
For J = 1 To 12
RS2.AddNew
RS2.Fields("MOIS").Value = J
RS2.Fields("SERVICE").Value = RS1.Fields("SERVICE").Value
Deb = IIf(J < 10, "01/0" & J & "/" & Year(Date), "01/" & J & "/" & Year(Date))
fini = CDate(DateSerial(Year(Deb), Month(Deb) + 1, 0))
RS2.Fields("NB").Value = ANAEX.Work_Days(Deb, fini, True)
RS2.Update
Next J
Else
Select Case ty
Case "PP": x = IIf(st = "TR", 3, 2)
Case "TR": x = 3
Case "AF": x = 4
Case "GL": x = 5
End Select
For J = 1 To 12
RS2.AddNew
RS2.Fields("MOIS").Value = J
RS2.Fields("SERVICE").Value = RS1.Fields("SERVICE").Value
For k = 1 To 8 Step 1
If para(k, x) <> "" Then
var = para(k, x) & arg
rec = "champ" & (J + 2)
trouve = "champ2 = '" & var & "'"
RS3.Index = "champ2"
RS3.MoveFirst
RS3.Seek "=", var
Select Case k
Case 1
RS2.Fields(para(k, 1)).Value = CDbl(RS3.Fields(rec).Value) * 1000
Case 2
RS2.Fields(para(k, 1)).Value = CDbl(RS3.Fields(rec).Value) * 1000
Case 3
RS2.Fields(para(k, 1)).Value = CDbl(RS3.Fields(rec).Value)
Case 4
RS2.Fields(para(k, 1)).Value = CDbl(RS3.Fields(rec).Value)
Case 5
RS2.Fields(para(k, 1)).Value = CDbl(RS3.Fields(rec).Value) * 1000
Case 6
RS2.Fields(para(k, 1)).Value = CDbl(RS3.Fields(rec).Value) * 1000
Case 7
RS2.Fields(para(k, 1)).Value = CDbl(RS3.Fields(rec).Value) / 100
Case 8
RS2.Fields(para(k, 1)).Value = CDbl(RS3.Fields(rec).Value) * 100
End Select
Deb = IIf(J < 10, "01/0" & J & "/" & Year(Date), "01/" & J & "/" & Year(Date))
fini = CDate(DateSerial(Year(Deb), Month(Deb) + 1, 0))
RS2.Fields("NB").Value = ANAEX.Work_Days(Deb, fini, True)
Else
End If
Next k
RS2.Update
Next J
End If
RS1.MoveNext
Next i
RS2.MoveFirst
Do
If RS2.Fields("SERVICE").Value = "*" Then
i = RS2.Fields("MOIS").Value
rec = "champ" & i + 2
RS3.Index = "champ2"
RS3.Seek "=", "KMS"
KM = RS3.Fields(rec).Value
RS3.Seek "=", "KMSTR"
KM = KM + RS3.Fields(rec).Value
RS2.Edit
RS2.Fields("KM").Value = KM
RS2.Update
Else
End If
RS2.MoveNext
Loop Until RS2.EOF
End Sub |
Partager