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
================================