Getting Variable from Module to Report

[dylcon]

Hello,


I am creating a report that will show the quantity on-hand for a specific part. I have used the idea of

http://allenbrowne.com/AppInventory.html

and implemented this function into a Macro. I need the selection from a combobox on the report to be passed to the function within the module, it will run it's course, then pass that calculated quantity for a specific part back to the report after the 'Apply' command button is clicked. I do not know how to get this quantity on the report, however. If someone can help me with this, that would be greatly appreciated.

[ItsMe]

Is the result stored in a general module as a public variable? What is the variable name?

Are you actually using a macro or a public function?

[dylcon]

I am using a macro and it should be. Maybe this will help:

Option Compare Database

Function OnHand(vProductID As Variant, Optional vAsOfDate As Variant) As Long
    'Idea based off of Allen Browne's Inventory Control db
    '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 & ")"
        End If
        strSQL = "SELECT TOP 1 Date, Quantity FROM tblInventoryReset " & _
            "WHERE ((PartID = " & lngProduct & ")" & strDateClause & _
            ") ORDER BY Date DESC;"


        Set rs = db.OpenRecordset(strSQL)
        With rs
            If .RecordCount > 0 Then
                strSTDateLast = "#" & Format$(!StockTakeDate, "mm\/dd\/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(tblReceived.ReceivedQuantity) AS ReceivedQuantity " & _
            "FROM tblReceived " & _
            "WHERE ((tblReceived.PartID = " & lngProduct & ")"
        If Len(strDateClause) = 0 Then
            strSQL = strSQL & ");"
        Else
            strSQL = strSQL & " AND (tblReceived.ReceivedDate " & strDateClause & "));"
        End If


        Set rs = db.OpenRecordset(strSQL)
        If rs.RecordCount > 0 Then
            lngQtyAcq = Nz(rs!ReceivedQuantity, 0)
        End If
        rs.Close


        'Get the quantity used since then.
        strSQL = "SELECT Sum(tblConsumed.ConsumedQuantity) AS ConsumedQuantity " & _
            "FROM tblConsumed " & _
            "WHERE ((tblConsumed.PartID = " & lngProduct & ")"
        If Len(strDateClause) = 0 Then
            strSQL = strSQL & ");"
        Else
            strSQL = strSQL & " AND (tblReceive.ReceivedDate " & 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

[drexasaurus]

There are several ways to do this. If you need to process this this value for every row in the report, you could add this to a column to a query. Something like this in the column value-- NumOnHand: OnHand([InventoryID], [ReportDate])
This would run the function for every row, and seeing that you are opening a recordset for every time the function is accessed, it will likely be slow, especially if the rest of the query is complicated.

You can use a paramerized query if you want the results to affect the constraint of a single query (or to show up as a single column). I googled an example real quick on how to set a paramater via vba and the querydef object real quick: http://bytes.com/topic/access/answer...-query-via-vba

If you want to just set a variable to access via the reports module, you can use a global variable (I hate those), a TempVar (which most people hate, but I find that it's more reliable than global variables, especially in the testing phase), or as I prefer a function and a static variable: something like

Public Function PassToReport(Optional value as variant) As Variant
Static toPass As Variant
    If not IsMissing(value) Then  toPass = value
PassToReport = toPass
End Function