Code check

[Cowboy]

I'm wondering if someone could look at this code and let me know if I'm doing something wrong?

Thanks in advance if you take a look!

Public Function fnGetAddTransactions(vLastStockTakeID, vLocInvID)
    [COLOR=YellowGreen]' This function will take in the last stock take ID: varLastStockTakeID
    ' which was deteremined using fnGetLastStockTake.
    ' Use the location inventory ID for the specific color requested
    ' It will then add all the "add" transactions up from the last stock take
    ' and return the total count to be added.[/COLOR]
    
    Dim varLastStockTakeID As Variant
    Dim varLocInvID As Variant
        
    Dim dbf As DAO.Database
    Dim rs As DAO.Recordset
    
    Dim strSQL As String
    Dim varAddCount As Variant
    
    varLastStockTakeID = vLastStockTakeID
    varLocInvID = vLocInvID
    
    [COLOR=YellowGreen]' Set the database connection[/COLOR]
    Set dbf = CurrentDb()
    
    
    [COLOR=YellowGreen]' Define SQL statement to get the transaction ID for added stock[/COLOR]
    strSQL = "SELECT pkTransID FROM tblTransactionTypes WHERE txtTransType = 'Add'"
    
    [COLOR=YellowGreen]' Create a recordset from the query[/COLOR]
    Set rs = dbf.OpenRecordset(strSQL)
    
    [COLOR=YellowGreen]' Set a local variable to the transaction ID for added stock[/COLOR]
    If rs.RecordCount > 0 Then
        varTransaction = rs!pkTransID
    Else
        Debug.Print "Issue with the transaction type in the fnGetLastStockTake function."
    End If
    
    [COLOR=YellowGreen]' Close and clear recordset[/COLOR]
    rs.Close
    Set rs = Nothing
    
    
    [COLOR=YellowGreen]' Define SQL statement to look for all the rows with added stock for a specific
    ' color since the last stock take[/COLOR]
    strSQL = "SELECT longTransQty FROM tblLocationInventoryTransactions" & _
            " WHERE fkLocInvID = " & varLocInvID & " AND fkTransTypeID = " & varTransaction & _
            " AND pkLocInvTransID > " & varLastStockTakeID
            
    [COLOR=YellowGreen]' Create a recordset from the query[/COLOR]
    Set rs = dbf.OpenRecordset(strSQL)
    
    [COLOR=YellowGreen]' Set a local variable to the sum of all the add transactions of the color
    ' since the last stock take[/COLOR]
    varAddCount = DSum(rs!longTransQty, rs)
    
    [COLOR=YellowGreen]' Close and clear recordset; clear database connection[/COLOR]
    rs.Close
    Set rs = Nothing
    Set dbf = Nothing
    
    
    [COLOR=YellowGreen]' Pass the total added quantity back[/COLOR]
    fnGetAddTransactions = varAddCount
    

    
End Function

 

[pbaldy]

As noted elsewhere, I don't think the DSum will work. Just do it in the query:

SELECT Sum(longTransQty) AS TotalQty FROM...

and then

varAddCount = rs!TotalQty

 

[Cowboy]

Thank you!

I will try that.

I didn't know I could sum inside a query.

Learning new things everyday. (Which is good!)



UPDATED SECTION OF CODE:

[COLOR=YellowGreen]    ' Define SQL statement to look for all the rows with added stock for a specific
    ' color since the last stock take[/COLOR]
    strSQL = "SELECT [COLOR=Red]Sum(longTransQty) AS AddQty[/COLOR] FROM tblLocationInventoryTransactions" & _
            " WHERE fkLocInvID = " & varLocInvID & " AND fkTransTypeID = " & varTransaction & _
            " AND pkLocInvTransID > " & varLastStockTakeID
            
[COLOR=YellowGreen]    ' Create a recordset from the query[/COLOR]
    Set rs = dbf.OpenRecordset(strSQL)
    
    [COLOR=YellowGreen]' Set a local variable to the sum of all the add transactions of the color
    ' since the last stock take[/COLOR]
    [COLOR=Red]If rs.RecordCount > 0 Then
        varAddCount = rs!AddQty
    Else
        varAddCount = 0
    End If[/COLOR]
    [COLOR=YellowGreen]' Close and clear recordset; clear database connection[/COLOR]
    rs.Close
    Set rs = Nothing
    Set dbf = Nothing
    
    
    [COLOR=YellowGreen]' Pass the total added quantity back[/COLOR]
    fnGetAddTransactions = varAddCount

 

[pbaldy]

Happy to help; post back if you get stuck.