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
| Dim Message, Title, Default, MyValue
Message = "Saisir le N° du document à copier" ' Set prompt.
Title = "Création d'un BC à partir d'un document existant" ' Set title.
Default = "1" ' Set default.
' Display dialog box at position 100, 100.
MyValue = InputBox(Message, Title, Default, 5000, 3000)
Dim oDb As DAO.Database
Dim oRst As DAO.Recordset
Set oDb = CurrentDb
marequete = "SELECT * FROM dbo_ENTETES_VTES" 'XXXXXXXXXXXX
Set oRst = oDb.OpenRecordset(marequete)
NBF = oRst.RecordCount
If NBF > 0 Then
While Not oRst.EOF
'lecture de la ligne et récup des champs
Var0 = oRst.Fields(0) ' TYP_PIE
Var1 = oRst.Fields(1) ' NUM_SER
Var2 = oRst.Fields(2) ' NUM_PIE
Var3 = oRst.Fields(0) ' COD_CLT
Var4 = oRst.Fields(1) ' DAT_PIE
Var5 = oRst.Fields(2) ' Date_Pice
Var6 = oRst.Fields(0) ' priode_piece
Var7 = oRst.Fields(1) ' anne_piece
Var8 = oRst.Fields(2) ' mois_piece
Var9 = oRst.Fields(0) ' jour_piece
Var10 = oRst.Fields(1) ' COD_REP
Var11 = oRst.Fields(2) ' CLT_FAC
Var12 = oRst.Fields(0) ' FLG_MAJ
Var13 = oRst.Fields(1) ' MONTANT_HT
Var14 = oRst.Fields(14) ' REF_CMD
datejour = Now()
vide = Null
CO = "CO"
E = "E"
' INSERT INTO Store_Information (store_name, Sales, Date)
' SELECT store_name, Sales, Date
' FROM Sales_Information
' WHERE Year(Date) = 1998
sql = "INSERT INTO [ACCESS_ENTETES_VTES] " _
& "(TYP_PIE, CODE_STATUT, COD_CLT, DAT_PIE, COD_REP, CLT_FAC, FLG_MAJ, MT_HT, REF_CMD, SERIE_PIECE, " _
& "TYP_LIGNE, COM1, COM2, COM3, COM4, COM5) " _
& "SELECT ' " + CO + " ', ' " + CO + " ', ' " + Var3 + " ', ' " + datejour + " ', ' " + Var10 + " ', " _
& " ' " + Var11 + " ', ' " + vide + " ', ' " + vide + " ', ' " + vide + " ', ' " + vide + " ', " _
& "' " + vide + " ', ' " + E + " ', ' " + vide + " ', ' " + vide + " ', ' " + vide + " ', ' " + vide + " ', " _
& "' " + vide + " ', " _
& "FROM [ACCESS_ENTETES_VTES] WHERE NUM_ORD =MyValue;"
CurrentDb.Execute sql
Wend
End If |
Partager