Qty on hand and Stock taking

[Guest]

Following the advice offered on Allen Brownes Page, I have designed my stock
control system with a qtyOnhand function. I also required stock takes but
rather than the single stock take table, I modified it to have a stocktake
table and
a stocktakedetail table.
However I am unable to get the onHand function to search properly as I dont
fully understand the select TOP 1.
I have been trying this:

strSQL = "SELECT TOP 1 StockTakeDate, Quantity FROM tblStockTake INNER JOIN
tblStockTakeDetail " & _
"ON tblStockTake.StockTakeID = tblStockTakeDetail.StockTakeID" & _
"WHERE ((ProductID = " & lngProduct & ")" & strDateClause & _
") ORDER BY StockTakeDate DESC;"
but to no avail!
Can anyone offer some Advice.
Thank you.
Kenny

 

[Allen Browne]

Presumably we are talking about the code on this page:
http://allenbrowne.com/AppInventory.html

The StocktakeDetail makes sense where you take stock of lots of products at
once.

The SQL statement looks like what I would expect your tables to need. The
"TOP 1" predicate just returns the first match; combined with the most
recent StockTakeDate (optionally up to a certain date), it tells JET to just
return the date and quantity of the most recent stocktake that included that
product, and don't bother returning all the records for previous stocktakes
as well. The code would still work (a little less efficiently) without the
TOP 1 predicate.

If the SQL statement is failing at the OpenRecordset, you could:
Debug.Print strSQL
Then when it fails, open the Immediate window (Ctrl+G), copy the output
statement, and paste it into SQL View in a query to get the query designer
to help you identify what's wrong.

 

[Guest]

Yep,
Thats the code I was talking about,
I hope you dont mind but I was trying to get yours to work fully with my
ideas before designing my own.

I have always ignored the debugging tools, but maybee its time I learned!

Thank you for the advice,
Kenny