Hi Bob,
I have tried and tested your coding...It works very well...Thanks very much...
but it seems that I have to manually calculated the quantity on hand on tblStocktakes...and enter the value to the table tblStocktakes...and supposed if i have over 300 products...it can be tiredsome
And If I want to check the stock quantity before the stocktakeDate on the stocktake tables...it always return the quantity value entered at the StocktakeDate field...eg...productID = 7...qtyInStock(before any Purchase) = 50...stocktakeDate = 25/08/2003...customers have purchase twice on 15/08/2003 for OrderAmount=10...and on 18/08/2003 for orderAmount = 10....I set the stocktakeDate on 20/08/2003 with the quantity=30 (50-10-10)...
When I enter ?OnHand (7, 17-08-2003) at the Immediate window....it always return quantity=30...which it supposed to be 40 (50-10)...because customer only purchase once on 15/08/2003
any other way to get around this?
Here is the copy of my table structure:
http://www24.brinkster.com/kimho76/tablesRel.gif (u have to copy paste to new windows exp to see it)
and this is my coding...modified from yours:
Function OnHand(vProductID As Variant, Optional vAsOfDate As Variant) As Long
'Purpose: Return the quantity-on-hand for a product.
'Arguments: vProductID = the product to report on.
' vAsOfDate = the date at which quantity is to be calculated.
' If missing, all transactions are included.
'Return: Quantity on hand. Zero on error.
Dim db As DAO.Database 'CurrentDb()
Dim rs As DAO.Recordset 'Various recordsets.
Dim lngProduct As Long 'vProductID as a long.
Dim strAsOf As String 'vAsOfDate as a string.
Dim strSTDateLast As String 'Last Stock Take Date as a string.
Dim strDateClause As String 'Date clause to use in SQL statement.
Dim strSQL As String 'SQL statement.
Dim lngQtyLast As Long 'Quantity at last stocktake.
Dim lngQtyAcq As Long 'Quantity acquired since stocktake.
Dim lngQtyPurchased As Long 'Quantity used since stocktake.
If Not IsNull(vProductID) Then
'Initialize: Validate and convert parameters.
Set db = CurrentDb()
lngProduct = vProductID
If IsDate(vAsOfDate) Then
strAsOf = "#" & Format$(vAsOfDate, "mm\/dd\/yyyy") & "#"
End If
'Get the last stocktake date and quantity for this product.
If Len(strAsOf) > 0 Then
strDateClause = " AND (StockTakeDate <= " & strAsOf & ")"
End If
strSQL = "SELECT TOP 1 StockTakeDate, Quantity FROM tblStockTake " & _
"WHERE ((ProductID = " & lngProduct & ")" & strDateClause & _
") ORDER BY StockTakeDate DESC;"
Set rs = db.OpenRecordset(strSQL)
With rs
If .RecordCount > 0 Then
strSTDateLast = "#" & Format$(!StockTakeDate, "mm\/dd\/yyyy") & "#"
lngQtyLast = Nz(!Quantity, 0)
End If
End With
rs.Close
'Build the Date clause
If Len(strSTDateLast) > 0 Then
If Len(strAsOf) > 0 Then
strDateClause = " Between " & strSTDateLast & " And " & strAsOf
Else
strDateClause = " >= " & strSTDateLast
End If
Else
If Len(strAsOf) > 0 Then
strDateClause = " <= " & strAsOf
Else
strDateClause = vbNullString
End If
End If
'Get the quantity acquired since then.
strSQL = "SELECT Sum(tblAcquisitionDetails.Quantity) AS QuantityAcq " & _
"FROM tblAcquisition INNER JOIN tblAcquisitionDetails ON tblAcquisition.AcqID = tblAcquisitionDetails.fkAcqID " & _
"WHERE ((tblAcquisitionDetails.ProductID = " & lngProduct & ")"
If Len(strDateClause) = 0 Then
strSQL = strSQL & ");"
Else
strSQL = strSQL & " AND (tblAcquisition.AcqDate " & strDateClause & "));"
End If
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
lngQtyAcq = Nz(rs!QuantityAcq, 0)
End If
rs.Close
'Get the quantity used since then.
strSQL = "SELECT Sum(tblOrderDetails.AmountOrdered) AS QuantityPurchased " & _
"FROM tblOrders INNER JOIN tblOrderDetails ON " & _
"tblOrders.OrderID = tblOrderDetails.fkOrderID " & _
"WHERE ((tblOrderDetails.fkProductID = " & lngProduct & ")"
If Len(strDateClause) = 0 Then
strSQL = strSQL & ");"
Else
strSQL = strSQL & " AND (tblOrders.OrderDate " & strDateClause & "));"
End If
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
lngQtyPurchased = Nz(rs!QuantityPurchased, 0)
End If
rs.Close
'Assign the return value
OnHand = lngQtyLast + lngQtyAcq - lngQtyPurchased
End If
Set rs = Nothing
Set db = Nothing
Exit Function
End Function
Actually...I have done similar function like this using TRIGGER on oracle sql...this is the coding:
SQL> -- Trigger for checking quantity in stock
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER product_qty_stock_check
2 AFTER INSERT OR UPDATE on asg_orderline
3 for each row
4
5 DECLARE
6 qty_in_hand NUMBER (4);
7 qty_aft_sales NUMBER (4);
8
9 BEGIN
10 SELECT qty_in_stock INTO qty_in_hand
11 FROM asg_product
12 WHERE id = :new.product_id;
13
14 qty_aft_sales := qty_in_hand - :new.qty_ordered;
15
16 if qty_aft_sales < 0 then
17 raise_application_error(-20003,'Cannot make an order because there is not enough stock');
18 else
19 UPDATE asg_product SET qty_in_stock = qty_aft_sales
20 WHERE id = :new.product_id;
21 end if;
22 END;
23 /
Trigger created.
SQL> select * from asg_product where id = 30002;
Sunshine Shoes Distributor
===========================
SALES DEPARTMENT Sales Rep Responsibility
ID PRODUCT_NAME PRICE QTY_IN_STOCK
---------- ------------------------------ ---------- ------------
30002 ************************ Junior orange 150 1000
SQL> INSERT INTO asg_orderline (order_line_no, product_id, product_name, qty_ordered, price, order_id)
2 VALUES (&order_line_no, &product_id, '&product_name', &qty_ordered, &price, '&order_id');
Enter value for order_line_no: 1
Enter value for product_id: 30002
Enter value for product_name: ************************ Junior orange
Enter value for qty_ordered: 23
Enter value for price: 150
Enter value for order_id: 0019
old 2: VALUES (&order_line_no, &product_id, '&product_name', &qty_ordered, &price, '&order_id')
new 2: VALUES (1, 30002, '************************ Junior orange', 23, 150, '0019')
1 row created.
SQL> select * from asg_product where id = 30002;
Sunshine Shoes Distributor
===========================
SALES DEPARTMENT Sales Rep Responsibility
ID PRODUCT_NAME PRICE QTY_IN_STOCK
---------- ------------------------------ ---------- ------------
30002 ************************ Junior orange 150 977
So, is it possible to create a function like this with Access XP and VBA?
in which the stock quantity would be automatically reduced...wihout having to manually enter the stock quantity at the stocktake table...
and the stock quantity itself would return the updated quantity ...regardless of the stocktakeDate I set on the stocktake table....and How? sorry if it is too long Bob...I hope...my explanation is clear enough...Anyway...Thanks once again Bob...you've been a great help for me :)
Or is there anyone can help?
Thank you in advanced
Cheers
Fehrer
Check quantity stock level
