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
| 'On sélectionne les picks tickets dont la quantité est supérieure ou égale à nb_mini
strsql2 = "SELECT BDC.[Pick Ticket #] AS pick_select FROM BDC GROUP BY BDC.[Pick Ticket #] HAVING (((Sum(BDC.Quantity))> " & nb_mini & ") OR ((Sum(BDC.Quantity))= " & nb_mini & "))"
Set rst2 = CurrentDb.OpenRecordset(strsql2)
'on enregistre chaque picks tickets sélectionnés dans la table BDC_mono
Do Until rst2.EOF
num_pick = rst2![pick_select]
'On va chercher la/les ligne(s) correspondante(s) à num_pick et on la/les enregistre(nt) dans la table BDC_mono
strsql3 = "SELECT BDC.[Customer #] AS custom1, BDC.[Customer #1] AS custom2, BDC.[Order #] AS order1, BDC.[Style #] AS style1, BDC.Color AS color1, BDC.[Size Type] AS size_type1, BDC.Size AS size1, BDC.Quantity AS qte1, BDC.[Pick Sequence 1] AS pick1, BDC.[Pick Sequence 11] AS pick11, BDC.[Pick Sequence 2] AS pick2, BDC.[Pick Sequence 3] AS pick3 FROM BDC WHERE (((BDC.[Pick Ticket #])='" & num_pick & "'))"
Set rst3 = CurrentDb.OpenRecordset(strsql3)
Do Until rst3.EOF
custom1 = rst3![custom1]
custom2 = rst3![custom2]
order1 = rst3![order1]
style1 = rst3![style1]
color1 = rst3![color1]
size_type1 = rst3![size_type1]
size1 = rst3![size1]
qte1 = rst3![qte1]
pick1 = rst3![pick1]
pick11 = rst3![pick11]
pick2 = rst3![pick2]
pick3 = rst3![pick3]
'On enregistre le tout dans la table BDC_mono
MonSQL = "INSERT INTO BDC_mono ([Customer #], [Customer #1], [Pick Ticket #], [Order #], [Style #], Color, [Size Type], Size, Quantity, [Pick Sequence 1], [Pick Sequence 11], [Pick Sequence 2], [Pick Sequence 3], traite) VALUES ('" & [custom1] & " ', '" & [custom2] & " ', '" & [num_pick] & " ', '" & [order1] & " ', '" & [style1] & " ', '" & [color1] & " ', '" & [size_type1] & " ', '" & [size1] & " ', '" & [qte1] & " ', '" & [pick1] & " ', '" & [pick11] & " ', '" & [pick2] & " ', '" & [pick3] & " ',0)"
DoCmd.SetWarnings False
DoCmd.RunSQL MonSQL
DoCmd.SetWarnings True
rst3.MoveNext
Loop
rst3.Close
rst2.MoveNext
Loop
rst2.Close |
Partager