Here is the sql for the query that opens the product form:
SELECT tblAMPItems.lngAMPItemID, tblAMPItems.lngAMPItemType AS Category, tblAMPItems.lngItemSubTypeID AS SubCategory, tblAMPItems.strAMPItemName AS Name, tblAMPItems.strAMPDescription AS Description, OnOrder([tblAMPItems].[lngAMPItemID]) AS OnOrder, OnHold([tblAMPItems].[lngAMPItemID]) AS OnHold, OnHand([tblAMPItems].[lngAMPItemID]) AS OnHand, tblInboardProperties.lngSpecialSeriesID AS IBSeries, tblInboardProperties.strDiameter AS IBDiameter, tblInboardProperties.strPitch AS IBPitch, tblInboardProperties.lngMetalTypeID AS IBMaterial, tblInboardProperties.intBlades AS IBBlades, tblAMPItems.curStandardPrice AS List, [curStandardPrice]*0.9 AS [10% Discount], [curStandardPrice]*0.75 AS [25% Discount], tblAMPItems.ynAddtoOrder
FROM tblAMPItems LEFT JOIN tblInboardProperties ON tblAMPItems.lngAMPItemID = tblInboardProperties.lngAMPItemID
ORDER BY tblAMPItems.strAMPItemName;
It seems to be the onhand calculation that causes the problem and the problem arises at the point at which the function calculates the products sold from the orders/order details table.
These are the functions for all the inventory calculations:
Public Function OnHand(vProductID As Variant, Optional vAsOfDate As Variant) As Integer
'Purpose: Return the quantity on hand for a product
'Arguments: vProductID = the product ID which the function is for
'vAsOfDate - the date at which the quantity is to calculate - if missing, includes all transactions
'Returns: Quantity On Hand - Zero on error
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lngProduct As Long 'Product ID
Dim strAsOf As String 'vAsOfDate as a string
Dim strInvCountLast As String 'Last Inventory Count Date as string
Dim strDateClause As String 'Date clause to use in SQL statement
Dim strSQL As String 'SQL statement
Dim intQtyLast As Integer 'Quantity last inventory count
Dim intQtyAcq As Integer 'Quantity purchased since last inventory count
Dim intQtySold As Integer 'Quantity sold since last inventory count
If Not IsNull(vProductID) Then
'Initialize and convert parameters
Set dbs = CurrentDb
lngProduct = vProductID
If IsDate(vAsOfDate) Then
strAsOf = "#" & Format$(vAsOfDate, "mm\/dd\/yyyy") & "#"
End If
'Get the last inventory count date for this item
If Len(strAsOf) > 0 Then
strDateClause = " AND (datCountDate <= " & strAsOf & ")"
End If
strSQL = "SELECT TOP 1 datCountDate, intCountQty FROM tblAMPItemCount " & _
"WHERE ((lngAMPItemID = " & lngProduct & ")" & strDateClause & _
") ORDER BY datCountDate DESC;"
Set rst = dbs.OpenRecordset(strSQL)
With rst
If .RecordCount > 0 Then
strInvCountLast = "#" & Format$(!datCountDate, "mm\/dd\/yyyy") & "#"
intQtyLast = Nz(!intCountQty, 0)
End If
End With
rst.Close
'Build the Date Clause
If Len(strInvCountLast) > 0 Then
If Len(strAsOf) > 0 Then
strDateClause = " Between " & strInvCountLast & " And " & strAsOf
Else
strDateClause = " >= " & strInvCountLast
End If
Else
If Len(strAsOf) > 0 Then
strDateClause = " <= " & strAsOf
Else
strDateClause = vbNullString
End If
End If
'Get the quantity received since date
strSQL = "SELECT Sum(tblPODetails.intQtyReceived) AS QuantityReceived " & _
"FROM tblPurchaseOrders INNER JOIN tblPODetails on tblPurchaseOrders.lngPONumber = tblPODetails.lngPONumber " & _
"WHERE tblPODetails.lngAMPItemID = " & lngProduct & ""
If Len(strDateClause) = 0 Then
strSQL = strSQL & ";"
Else
If Len(strAsOf) > 0 Then
strSQL = strSQL & " AND tblPODetails.datDateReceived" & strDateClause & " AND tblPurchaseOrders.datPODate <= " & strAsOf & ";"
Else
strSQL = strSQL & " AND tblPODetails.datDateReceived" & strDateClause & ";"
End If
End If
Set rst = dbs.OpenRecordset(strSQL)
If rst.RecordCount > 0 Then
intQtyAcq = Nz(rst!QuantityReceived, 0)
End If
rst.Close
'Get the quantity sold since date
strSQL = "SELECT Sum(tblOrderDetails.intQty) As QuantitySold " & _
"FROM tblOrders INNER JOIN tblOrderDetails ON " & _
"tblOrders.lngWorkorderID = tblOrderDetails.lngWorkOrderID " & _
"WHERE ((tblOrderDetails.lngLinkCriteria = " & lngProduct & " AND lngWorkOrderTypeID = 5)"
If Len(strDateClause) = 0 Then
strSQL = strSQL & ");"
Else
strSQL = strSQL & " AND (tblOrders.datOrderDate " & strDateClause & "));"
End If
Set rst = dbs.OpenRecordset(strSQL)
If rst.RecordCount > 0 Then
intQtySold = Nz(rst!QuantitySold, 0)
End If
rst.Close
'Assign the return value
OnHand = intQtyLast + intQtyAcq - intQtySold
'Deals with negative values
If OnHand < 0 Then
OnHand = 0
End If
End If
End Function
Public Function OnOrder(vProductID As Variant, Optional vAsOfDate As Variant) As Integer
'Purpose: Return the quantity on order for a product
'Arguments: vProductID = the product ID which the function is for
'vAsOfDate - the date at which the quantity is to calculate - if missing, includes all transactions
'Returns: Quantity On Hand - Zero on error
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lngProduct As Long 'Product ID
Dim strAsOf As String 'vAsOfDate as a string
Dim strDateClause As String 'Date clause to use in SQL statement
Dim strSQL As String 'SQL statement
If Not IsNull(vProductID) Then
'Initialize and convert parameters
Set dbs = CurrentDb
lngProduct = vProductID
If IsDate(vAsOfDate) Then
strAsOf = "#" & Format$(vAsOfDate, "mm\/dd\/yyyy") & "#"
End If
'Build the Date Clause
If Len(strAsOf) > 0 Then
strDateClause = "WHERE (((tblPurchaseOrders.datPODate) <= " & strAsOf & ") And ((tblPODetails.datDateReceived) > " & strAsOf & ")) Or (((tblPurchaseOrders.datPODate) <= " & strAsOf & ") And ((tblPODetails.datDateReceived) Is Null)) "
Else
strDateClause = vbNullString
End If
'Get the quantity not received for POs before the AsOf Date
strSQL = "SELECT tblPODetails.lngAMPItemID, Sum([intQtyOrdered]-[intQtyReceived]) AS OpenOrderQty " & _
"FROM tblPurchaseOrders INNER JOIN tblPODetails ON tblPurchaseOrders.lngPONumber = tblPODetails.lngPONumber " & _
strDateClause & _
"GROUP BY tblPODetails.lngAMPItemID " & _
"HAVING (((tblPODetails.lngAMPItemID = " & lngProduct & ") AND Sum(([intQtyOrdered]-[intQtyReceived]))>0)) OR " & _
"(((tblPODetails.lngAMPItemID) = " & lngProduct & ") AND ((Sum([intQtyOrdered]-[intQtyReceived]))>0));"
Set rst = dbs.OpenRecordset(strSQL)
If rst.RecordCount > 0 Then
OnOrder = Nz(rst!OpenOrderQty, 0)
End If
rst.Close
Set rst = dbs.OpenRecordset("tblTempProductInfoForm")
With rst
.AddNew
!Item = lngProduct
!Time = Now()
.Update
End With
rst.Close
End If
End Function
Public Function OnHold(vProductID As Variant, Optional vAsOfDate As Variant) As Integer
'Purpose: Return the quantity on open orders for a product
'Arguments: vProductID = the product ID which the function is for
'vAsOfDate - the date at which the quantity is to calculate - if missing, includes all transactions
'Returns: Quantity On Hand - Zero on error
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lngProduct As Long 'Product ID
Dim strAsOf As String 'vAsOfDate as a string
Dim strDateClause As String 'Date clause to use in SQL statement
Dim strSQL As String 'SQL statement
If Not IsNull(vProductID) Then
'Initialize and convert parameters
Set dbs = CurrentDb
lngProduct = vProductID
If IsDate(vAsOfDate) Then
strAsOf = "#" & Format$(vAsOfDate, "mm\/dd\/yyyy") & "#"
End If
'Build the Date Clause
If Len(strAsOf) > 0 Then
strDateClause = "WHERE (((tblOrders.lngInvoiceNumber)=0) AND ((tblOrders.datOrderDate)<" & strAsOf & ") " & _
"AND ((tblOrderDetails.lngWorkOrderTypeID)=5) AND ((tblOrders.ynIsActive)=True)) " & _
"OR (((tblOrders.datOrderDate)<" & strAsOf & ") AND ((tblOrders.datInvoiceDate)>" & strAsOf & ") " & _
"AND ((tblOrderDetails.lngWorkOrderTypeID)=5) AND ((tblOrders.ynIsActive)=True))"
Else
strDateClause = "WHERE (((tblOrders.lngInvoiceNumber)=0) AND ((tblOrderDetails.lngWorkOrderTypeID)=5) " & _
"AND ((tblOrders.ynIsActive)=True))"
End If
'Get the quantity not received for POs before the AsOf Date
strSQL = "SELECT tblOrderDetails.lngLinkCriteria, Sum([tblOrderDetails].[intQty]) AS OnHoldQty " & _
"FROM tblOrderDetails INNER JOIN tblOrders ON tblOrderDetails.lngWorkOrderID = tblOrders.lngWorkOrderID " & _
strDateClause & _
"GROUP BY tblOrderDetails.lngLinkCriteria " & _
"HAVING tblOrderDetails.lngLinkCriteria = " & lngProduct & ";"
Set rst = dbs.OpenRecordset(strSQL)
If rst.RecordCount > 0 Then
OnHold = Nz(rst!OnHoldQty, 0)
End If
rst.Close
End If
End Function
And by the way, if I were purposely running the query multiple times, I would have known how to change that! Something in the function makes it repeat itself, but I can't figure out what. (There is a relationship in the order table between orders and order details.) I am now going through the data to see if there is a data issue.
