By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,902 Members | 1,409 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,902 IT Pros & Developers. It's quick & easy.

Re-write of Allen Browne's "OnHand" function -- opinions?

P: n/a
Hi Folks,

I'm still in the process of rewriting my Parts Inventory application, and
still using good old Access97 to do it.

I'm attempting to modify Allen Browne's code from:
http://members.iinet.net.au/~allenbr...Inventory.html
to suit a few ideas of my own.

I have thought that I might "simplify" Allen's table structure a little,
using just one table (tblTransactions) to record both sale and reciept
transactions.
The other problem I was having was that I have the same part stored at
multiple locations, so "tblStockTake" wasn't really working for my situation
either.
I decided that perhaps summing the qty's at all locations (in tblLocation)
might do the job for me.

I have posted the modified code below, and would like to have some input
before proceeding much further. You'll be able to see the original code
which has been commented out and replaced by my own ...

Am I on the right track, or headed for a wreck? :-)

************************************************** ****************
Function OnHand(vProductID As Variant, Optional vAsOfDate As Variant) As
Long

'This has been re-written to include my table / field names...

'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 lngQtyUsed 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 & ")"
strDateClause = " AND (CountDate <= " & strAsOf & ")"
End If

'strSQL = "SELECT TOP 1 StockTakeDate, Quantity FROM tblStockTake "
& _
"WHERE ((ProductID = " & lngProduct & ")" & strDateClause & _
") ORDER BY StockTakeDate DESC;"

strSQL = ""
strSQL = strSQL & "SELECT Sum(Nz([tblLocation]![Qty],0)) AS Count,
tblLocation.PartID, "
strSQL = strSQL & "tblLocation.CountDate "
strSQL = strSQL & "FROM tblLocation "
strSQL = strSQL & "WHERE ((PartID = " & lngProduct & ")" &
strDateClause
strSQL = strSQL & ") GROUP BY tblLocation.PartID,
tblLocation.CountDate; "

'Debug.Print strSQL
Set rs = db.OpenRecordset(strSQL)
With rs
If .RecordCount > 0 Then
'strSTDateLast = "#" & Format$(!StockTakeDate,
"mm\/dd\/yyyy") & "#"
strSTDateLast = "#" & Format$(!CountDate, "mm\/dd\/yyyy") &
"#"
'lngQtyLast = Nz(!Quantity, 0)
lngQtyLast = Nz(!Count, 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(tblAcqDetail.Quantity) AS QuantityAcq " & _
"FROM tblAcq INNER JOIN tblAcqDetail ON tblAcq.AcqID =
tblAcqDetail.AcqID " & _
"WHERE ((tblAcqDetail.ProductID = " & lngProduct & ")"
strSQL = ""
strSQL = strSQL & "SELECT Sum(IIf([tblTransactions]![TransType] Like
"
strSQL = strSQL & ""
strSQL = strSQL & chr$(34) & "*Receipt*"
strSQL = strSQL & chr$(34) & ",[TransQty],0)) AS QuantityAcq "
strSQL = strSQL & "FROM tblTransactions "
strSQL = strSQL & "WHERE ((tblTransactions.PartID = " & lngProduct &
")"

If Len(strDateClause) = 0 Then
strSQL = strSQL & ");"
Else
'strSQL = strSQL & " AND (tblAcq.AcqDate " & strDateClause &
"));"
strSQL = strSQL & " AND (tblTransactions.TransDate " &
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(tblInvoiceDetail.Quantity) AS QuantityUsed " &
_
"FROM tblInvoice INNER JOIN tblInvoiceDetail ON " & _
"tblInvoice.InvoiceID = tblInvoiceDetail.InvoiceID " & _
"WHERE ((tblInvoiceDetail.ProductID = " & lngProduct & ")"
strSQL = ""
strSQL = strSQL & "SELECT Sum(IIf([tblTransactions]![TransType] Like
"
strSQL = strSQL & chr$(34) & "*Sale*"
strSQL = strSQL & chr$(34) & ",[TransQty],0)) AS QuantityUsed "
strSQL = strSQL & "FROM tblTransactions "
strSQL = strSQL & "WHERE ((tblTransactions.PartID = " & lngProduct &
")"

If Len(strDateClause) = 0 Then
strSQL = strSQL & ");"
Else
'strSQL = strSQL & " AND (tblInvoice.InvoiceDate " &
strDateClause & "));"
strSQL = strSQL & " AND (tblTransactions.TransDate " &
strDateClause & "))"
End If

Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
lngQtyUsed = Nz(rs!QuantityUsed, 0)

End If
rs.Close

'Assign the return value
OnHand = lngQtyLast + lngQtyAcq - lngQtyUsed
End If

Set rs = Nothing
Set db = Nothing
Exit Function
End Function

************************************************** ****************
--
Thanks,
Don
=============================
Use My*****@Telus.Net for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

================================
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
"Don Leverton" <le****************@telusplanet.net> wrote:

I haven't reviewed your code but have the following comments.
I have thought that I might "simplify" Allen's table structure a little,
using just one table (tblTransactions) to record both sale and reciept
transactions.
I'd agreed with this approach. I have one set of transactions, a header and a
details table, which have at least eight or nine different types of transactions but
all in one table. POs, receipts, issues, transfers to other jobs, adjustments,
returns and a few more. But they're all in one table.

(One interesting quirk is that you may order 200' of pipe but you'll get 5 randomish
lengths generally varying from 38' to 42'. So you may receive 195' or 210'. You
never quite know. But that's enough to complete the PO.

Also you may order one type of pipe but get a substitution by the vendor for a better
grade of steel which the engineering company will accept.

Also you order olets in various sizes but get it shipped in a range of sizes. Ie a
4"x20" olet may be received as a 2"-8"x20" olet.

There has to be a batch number associated with each inventory item for complete
tracability of each item from a given batch of steel no matter who and where it was
manufactured to the wholesaler to the welding shop to a given location within a
plant or refinery. It has happened where a steel company made a mistake on the
composition of some steel and they had to locate and replace each piece of pipe,
elbow, tee,flange or whatever made from that batch of steel anywhere in the world.
At their expense.

There were many more quirks. <smile>)
The other problem I was having was that I have the same part stored at
multiple locations, so "tblStockTake" wasn't really working for my situation
either. I decided that perhaps summing the qty's at all locations (in tblLocation)
might do the job for me.


Here I created a table by Item and location with the quantities. (Actually it was
job as a number of jobs can go through the shop but the same idea) But whenever I
have denormalized data I also ensure I have logic to locate any problems. So I
created queries which add up the transaction details against this summary table to
show any discrepancies.

This usually shows me two problems. One is where I've got a bug in my logic.
Another is where a transaction was abormally terminated due to some oddity. I likely
should've thought about using Commit and such but never never gotten around to using
such logic.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.