Crisan,
Here you go!
Step 1:
Create a query called qryDeleteOrderPlace with this sql:
DELETE OrderPlace.*
FROM OrderPlace;
Step 2:
Create a query called qryOrderPlace with this sql:
SELECT Products.PID, Products.NeededQty, SupplierStocks.StockA, SupplierStocks.StockB, SupplierStocks.StockC, SupplierStocks.StockD, SupplierStocks.StockE
FROM Products INNER JOIN SupplierStocks ON Products.PID = SupplierStocks.PID;
Step 3:
Create a button on your form and put this in the on click event:
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDeleteOrderPlace" ' This Deletes All records in the OrderPlace Table
Dim db As Database
Dim rs, rsOrderPlace As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("qryOrderPlace")
Set rsOrderPlace = db.OpenRecordset("OrderPlace")
With rs
If Not (.EOF And .BOF) Then
.MoveFirst
Do While Not .EOF
myNeededQty = !NeededQty
myPId = !PID
myOrderA = 0
myorderB = 0
myorderC = 0
myorderD = 0
myorderE = 0
If !StockA >= myNeededQty Then
myOrderA = myNeededQty
myNeededQty = 0
Else
myOrderA = !StockA
myNeededQty = myNeededQty - myOrderA
End If
If !StockB >= myNeededQty Then
myorderB = myNeededQty
myNeededQty = 0
Else
myorderB = !StockB
myNeededQty = myNeededQty - myorderB
End If
If !StockC >= myNeededQty Then
myorderC = myNeededQty
myNeededQty = 0
Else
myorderC = !StockC
myNeededQty = myNeededQty - myorderC
End If
If !StockD >= myNeededQty Then
myorderD = myNeededQty
myNeededQty = 0
Else
myorderD = !StockD
myNeededQty = myNeededQty - myorderD
End If
If !StockE >= myNeededQty Then
myorderE = myNeededQty
myNeededQty = 0
Else
myorderE = !StockE
myNeededQty = myNeededQty - myorderE
End If
With rsOrderPlace
.AddNew
!PID = myPId
!OrderA = myOrderA
!OrderB = myorderB
!OrderC = myorderC
!OrderD = myorderD
!OrderE = myorderE
.Update
End With
.MoveNext
Loop
End If
End With
Set db = Nothing
Set rs = Nothing
Set rsOrderPlace = Nothing
DoCmd.SetWarnings True
That's it.
Please let me know if it works for you.
Kevin
dartcoach
|
