Allen Brown browns Inventory Function

[accadacca]

For my own knowledge im trying to create a database to test and sample the function created by Allen Brown.

Using access 2013 to this point I have created exactly as per the instruction by the tutorial. With the exception of my own name conventions
eg. tblacq = AcqT
http://allenbrowne.com/appinventory.html
The tutorial does not cover how to actualy use the function in a form.
So what I have worked out sofar looking at the sample database provided by Rainlover is to create an unbound text control.
I have tried using the function in the AcqDetailF (Sales Form) and also in the ProductF (Product Form) list to get the onhand amount
Product Form: Unbound text field

=Onhand([ProductID])

Aquisition Form:Unbound text field

=Onhand([ProductCombo],[Forms]![AcqF]![txtAcqDate])

Both return the same result and only display the last stock take amount or zero if nothing exist so it works to a certain degree.
The problem is its not calculating the disposed and aquired amounts. Seems Im missing the final piece to the Puzzle and Im unable to find the correct way to call the function from the forms unbound controls.
Thanks Jeff

 

[gemma-the-husky]

in general terms, without checking this particular example, what the onhand should do is

start from the latest stock date (or everything if there is no stock date)
- include the stock at the stock take date or zero
-sum all transactions between that date, and the date you are testing.

So I would look at the code you are using.
There must be a query in there somewhere that sums the transactions.

Try running that separately to see what transactions it returns.
Try running it as a select query, rather than a totals query, so you can see the separate transactions.

It will be something you aren't quite understanding. Maybe your tables are wrong. Maybe the query is wrong.

Be careful with dates - SQL expects US format dates, so if you have UK dates, you have to be careful.

eg 1/12/16 may by default be Jan 12th, Not 1st Dec.

 

[accadacca]

Id say your right about not quite understanding which is why im trying to get this to work.

Im trying to get a handle on how functions work in VBA, I have jumped right in the deep end with a fairly complex bit of code.
I did test the waters with a simple age calculation which works perfect.

Im not quite sure how to run a select query separately within the code.

As with the dates the code is written with that in mind (I asume) as he is from my neck of the woods

This is the Module:

Function OnHand(vProductID As Variant, Optional vAsOfDate As Variant) As Long
'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 long
    Dim strAsof As String
    Dim strSTDatelast As String
    Dim strDateClause As String
    Dim strSQL As String
    Dim lngQtyLast As Long
    Dim lngQtyAcq As Long
    Dim lngQtyUsed As Long
    
    If Not IsNull(vProductID) Then
        'Initialize: Validate and convert parameters.
        Set db = CurrentDb()
        lngProduct = vProductID
        If IsDate(vAsOfDate) Then
            strAsof = "#" & Format$(vAsOfDate, "dd\/mm\/yyyy") & "#"
        End If
        
        'Get the last stocktake date and quantity for this product.
        If Len(strAsof) > 0 Then
            strDateClause = " AND (StockTakeDate <= " & strAsof & ")"
        End If
        strSQL = "SELECT TOP 1 StockTakeDate, Quantity FROM StockTakeT " & _
        "WHERE ((ProductID = " & lngProduct & ")" & strDateClause & _
        ") ORDER BY StockTakeDate DESC;"
        
        Set rs = db.OpenRecordset(strSQL)
        With rs
            If .RecordCount > 0 Then
                strSTDatelast = "#" & Format$(!StockTakeDAte, "dd\/mm\/yyyy") & "#"
                lngQtyLast = Nz(!Quantity, 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(AcqDetailT.Quantity) AS QuantityAcq " & _
            "FROM AcqT INNER JOIN AcqDetailT ON AcqT.AcqID = AcqDetailT.AcqID " & _
            "WHERE ((AcqDetailT.ProductID = " & lngProduct & ")"
        If Len(strDateClause) = 0 Then
            strSQL = strSQL & ");"
        Else
            strSQL = strSQL & " AND (AcqT.AcqDate " & 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(InvoiceDetailT.Quantity) AS QuantityUsed " & _
            "FROM InvoiceT INNER JOIN InvoiceDetailT ON " & _
            "InvoiceT.InvoiceID = InvoiceDetailT.InvoiceID " & _
            "WHERE ((InvoiceDetailT.ProductID = " & lngProduct & ")"
        If Len(strDateClause) = 0 Then
            strSQL = strSQL & ");"
        Else
            strSQL = strSQL & " AND (InvoiceT.InvoiceDate " & 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 + lngQtyUsed - lngQtyUsed
    End If
    
    Set rs = Nothing
    Set db = Nothing
    Exit Function
End Function

 

[jdraw]

More info and examples of functions here.

Good luck