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
| Private Sub Commande25_Click()
Dim i As Date, j As Date, l As Date, y As Date, k As Integer, m As Integer, z As Integer
DoCmd.SetWarnings False
CurrentDb.Execute "CREATE TABLE tblMois_tmp (" _
& " id_tmp AutoIncrement CONSTRAINT idxid Primary Key, " _
& " moisdeb_tmp Date, " _
& " moisfin_tmp Date, " _
& " numannee_tmp Integer, nummois_tmp Integer, numjour_tmp Integer, " _
& " CONSTRAINT idxanmois UNIQUE (numannee_tmp, nummois_tmp))", dbFailOnError
i = Me.txtmoisdeb
j = Me.txtmoisfin
k = DateDiff("m", DateSerial(Year(i), Month(i), 1), DateSerial(Year(j), Month(j), 1))
For z = 0 To k
l = DateAdd("m", z, i)
CurrentDb.Execute "INSERT INTO tblMois_tmp (moisdeb_tmp, moisfin_tmp, numannee_tmp, nummois_tmp, numjour_tmp)" _
& " VALUES (#" & Format(l, "mm/dd/yyyy") & "#, #" & DateSerial(Year(l), Month(l) + 1, 0) & "#, " & Year(l) & ", " & Month(l) & ", " & Day(DateSerial(Year(l), Month(l) + 1, 0)) & ")", dbFailOnError
Next z
CurrentDb.Execute "INSERT INTO tblMois (moisdeb, moisfin, numannee, nummois, numjour)" _
& " SELECT moisdeb_tmp, moisfin_tmp, numannee_tmp, nummois_tmp, numjour_tmp" _
& " FROM tblMois_tmp" _
& " WHERE moisdeb_tmp Not In (SELECT moisdeb FROM tblMois)", dbFailOnError
DoCmd.DeleteObject acTable, "tblMois_tmp"
DoCmd.SetWarnings True
RefreshDatabaseWindow
End Sub |
Partager