473,324 Members | 2,124 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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

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
1 2012
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
by: David Mitchell | last post by:
I use the above function in queries for a number of forms and reports. The reports take approx 20 seconds to open. There are only 100 product id's in tblProducts. My concern is that the time will...
5
by: Wired Hosting News | last post by:
I tried to be breif and give a scenario so as not to be overlooked because it was soooo long. Let me give you real world. I am a manufacturer of goods and produce 11 items that are distributed...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.