Hi,
Let me tell you how I do it. Then tell me what you think. Collaboration is a
good thing! :-)
Main data entry and display
----------------------------
I have a main form called "frmInventoryAdjustments"
I use a combo-box (cboPartNumber) to find and display the record for that
part number.
The data from tblLocations (PartID, Loc, Qty, CountDate) is displayed and
edited as nessecary in a datasheet style subform called sbfLocations, which
is linked to the main form on PartID. As different parts are selected on the
main form, their corresponding locations and qty's are displayed in the
subform.
This approach is very flexible, because it allows the creation of new
records (for that part that you found on the shelf at location C01, for
example) as well as editing both the location and/or qty. If I move a part
from C01 to C02, its a simple matter of just changing its location right on
this form.
sbfLocations allows me to record the qty counted at each location, and the
total on-hand qty is the calculated sum of all qtys in all locations.
Stock-Taking
------------------
I print out a report called rptStockCheckByLocation, which is sorted and
grouped by Location, Line (3-chracter Mfr code -- i.e AUT for Autolite
spark plugs), and Part number.
This report includes the "assumed" total qty on hand at the time of
printing, followed by a blank underlined space for handwritten corrections.
As I wander around the stock area, beginning with location A01, I count the
qty of each part number. If the qty count at this location is the same as
the assumed count for this location ... I circle the quantity, indicated
that it has been counted and tat it is correct. If the total qty found at
this location is different, I handwrite the new qty in the blank
"corrections" field.
Posting the Adjustments
-------------------------
When the entire stock room has be counted, I can readily identify which
changes must be posted ... they are the items with the hand-written qtys!
"Missed counts" are also fairly obvious ... they are the ones that are
neither circled nor handwritten. I go back and check these, and "zero" them
if they just aren't there anymore.
This method saves me a lot of time, and disk space ... as I only have to
enter the exceptions, as well as modifying an existing record rather than
adding a new one.
When I am satisfied with the count, I am ready to post the changes using
the previously mentioned frmInventoryAdjustments.
Oh yeah ... let's say that I found a part on the shelf in the location that
I was counting, but there is no record of it at that location on the report.
I handwrite these at the bottom of the sheet. When doing my posting, I
discover that this part is not even recorded into the inventory system yet.
I know this because the combo-box ("cboPartNumber") displays a message
telling me that, and offers to add the part number for me. cboPartNumber
uses the NotInList event to display this message, as well as saving me the
work of re-typing the part number again in a new record. The other scenario
is that the part *was already in inventory*, and that it had just been put
away in the "wrong" location.When this happens, I can choose to move it
where it belongs, or count it where it lays. (I usually will put it where it
belongs, except if it is being reserved for a customer.)
Sales and Re-Stocking of Inventory Items
------------------------------------------
This is the part that I am in the process of presently revising. I was of
the mind that I *needed* to perform calculations, and store the results
because of the complexities involved with multiple locations. I had tried to
implement Allen's method previously, but never seemed to have the time to
change my whole structure. What I have come up with is a table that records
transactions (tblTransactions of course) and have modified Allen's code to
suit.
*SO FAR*, it appears to be working, but I need to do further testing before
I give this code my total endorsement.
FYI: I have also just posted this code in a seperate thread, requesting some
feedback.
************************************************** **************************
**********
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
--
HTH,
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
================================
<previous threads snipped>
