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
| Public Sub resultat()
Dim strSQL As String
Dim rst As DAO.Recordset
Dim rst_resultat As DAO.Recordset
Dim i As Integer
' recopier les lignes pour [SAP Qty] <=1
CurrentDb.Execute "INSERT INTO SAP_Resultat ([SAP Fixed Asset], [no d'ordre], [SAP Qty]) " & _
" SELECT [SAP Fixed Asset], [no d'ordre], [SAP Qty] FROM SAP " & _
" WHERE [SAP Qty]<=1", dbFailOnError
strSQL = "SELECT [SAP Fixed Asset], [no d'ordre], [SAP Qty] FROM SAP " & _
" WHERE [SAP Qty]>1" ' on récupère les lignes avec [SAP Qty]>1
Set rst = CurrentDb.OpenRecordset(strSQL)
Set rst_resultat = CurrentDb.OpenRecordset("SAP_Resultat")
With rst
.MoveFirst
While Not .EOF
For i = 1 To .Fields("[SAP Qty]") ' on boucle sur [SAP Qty]
rst_resultat.AddNew
rst_resultat.Fields("[SAP Fixed Asset]") = .Fields("[SAP Fixed Asset]")
rst_resultat.Fields("[no d'ordre]") = Format(i, "000")
rst_resultat.Fields("[SAP Qty]") = .Fields("[SAP Qty]")
rst_resultat.Update
Next
.MoveNext
Wend
End With
rst.Close
Set rst = Nothing
rst_resultat.Close
Set rst_resultat = Nothing
End Sub |
Partager