User Calculated Function problem

[FrustratedAlso]

I'm trying to create a user function that I can use on forms etc. I am trying to calculate the qty on hand based on purchase and sales of an item. I tried it on a form and get "#name?" error. I'm new to this and stumped??

Public Function QOH(rNbr As String) As Variant
On Error GoTo Err_Handler
    'Purpose:   Calculate Quantity on Hand
    'Return:    Number, or 0 if no matches.
    'Arguments: rNbr = Inventory Key to match Purchase & Sales Records to.
    '
    'Notes:  1. Use square brackets around field/table names with spaces or odd characters.
    '           2. strField can be a Multi-valued field (A2007 and later), but strOrderBy cannot.
    '           3. Nulls are omitted, zero-length strings (ZLSs) are returned as ZLSs.
    '           4. Returning more than 255 characters to a recordset triggers this Access bug:
    '               http://allenbrowne.com/bug-16.html
    Dim RecPur As DAO.Recordset         'Related Purchase records
    Dim RecSal As DAO.Recordset         'Related Sales Records
    Dim strSqlPur As String            'SQL statement
    Dim strSqlSal As String            'SQL statement
    Dim SumPur As Variant
    Dim SumSal As Variant
    
    'Initialize to Null
    QOH = 0
    SumPur = 0
    SumSal = 0
    
    'Build SQL strings, and get the records.
    'Purchase quantity
    strSqlPur = "SELECT Purchases.Qty From Purchases where Purchases.[InventoryNumber] = '" & rNbr & "'"
    Set RecPur = DBEngine(0)(0).OpenRecordset(strSqlPur, dbOpenDynaset)
    'Loop through the matching records
    Do While Not RecPur.EOF
        If Not IsNull(RecPur(1)) Then
            SumPur = SumPur + RecPur(1)
        End If
        RecPur.MoveNext
    Loop
    
    ' Sales Quantity
    strSqlSal = "SELECT Sales.Quantity From Sales where Sales.[Number] = '" & rNbr & "'"
    Set RecSal = DBEngine(0)(0).OpenRecordset(strSqlSal, dbOpenDynaset)
       'Check if there were no Sales.
    If IsNull(RecSal) Then
        SumSal = 0
    Else
        'Loop through the matching records
        Do While Not RecSal.EOF
           If Not IsNull(RecSal(1)) Then
             SumSal = SumSal + RecSal(1)
           End If
        RecSal.MoveNext
        Loop
    End If
    QOH = SumPur - SumSal
    RecPur.Close
    RecSal.Close
   
Exit_Handler:
    'Clean up
    Set RecPur = Nothing
    Set RecSal = Nothing
    Exit Function
Err_Handler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "QOH()"
    Resume Exit_Handler
End Function

[June7]

Show expression in the textbox ControlSource. Did you remember to use = sign?

[FrustratedAlso]

Yes, I did

Attachment 7050 Attachment 7051

[June7]

Then I am stumped too. Want to provide project for analysis?

[recyan]

Just a couple of questions :

1) where Purchases.[Inventory].[Number] - Is this also a way to access Table field values ?
2) Can we not use a GROUP BY & SUM query instead of looping thro the records ?

Thanks

[June7]

1. What are Purchases, Inventory, Number (project, table, field)? I think the answer is no but not really sure what you are asking.

2. Maybe, might involve several queries

EDIT: Oh, that was recyan asking for clarification. Now the post makes more sense.

[FrustratedAlso]

Apparently Access didn't like the function name. I renamed it and now it gives me the error Attachment 7061 when I use it in a query or form and returns all 0's. Purchases.[Inventory].[Number] is a field name

[June7]

Odd it did not like function name.

Purchases.[Inventory].[Number] is a field name?

[orange]

I'm with recyan

I think this (from your sql) is a syntax error Purchases.[Inventory].[Number]

[FrustratedAlso]

I renamed the field To take the Space out. Now I'm Getting a type mismatch error

Public Function QOH(rNbr As String) As Variant
On Error GoTo Err_Handler
    'Purpose:   Calculate Quantity on Hand
    'Return:    Number, or 0 if no matches.
    'Arguments: rNbr = Inventory Key to match Purchase & Sales Records to.
    '
    'Notes:     1. Use square brackets around field/table names with spaces or odd characters.
    '           2. strField can be a Multi-valued field (A2007 and later), but strOrderBy cannot.
    '           3. Nulls are omitted, zero-length strings (ZLSs) are returned as ZLSs.
    '           4. Returning more than 255 characters to a recordset triggers this Access bug:
    '               http://allenbrowne.com/bug-16.html
    Dim RecPur As DAO.Recordset         'Related Purchase records
    Dim RecSal As DAO.Recordset         'Related Sales Records
    Dim strSqlPur As String                  'SQL statement
    Dim strSqlSal As String                  'SQL statement
    Dim SumPur As Variant
    Dim SumSal As Variant
    
    'Initialize to Null
    QOH = 0
    SumPur = 0
    SumSal = 0
    
    'Build SQL strings, and get the records.
    'Purchase quantity
    strSqlPur = "SELECT Purchases.InventoryNumber, Purchases.Qty FROM Purchases WHERE Purchases.InventoryNumber = '" & rNbr & "'"
    Set RecPur = DBEngine(0)(0).OpenRecordset(strSqlPur, dbOpenDynaset)
    'Loop through the matching records
    Do While Not RecPur.EOF
        If Not IsNull(RecPur(0)) Then
            SumPur = SumPur + RecPur(0)
        End If
        RecPur.MoveNext
    Loop
    
    ' Sales Quantity
    strSqlSal = "SELECT Sales.Number, Sales.Quantity FROM Sales WHERE Sales.Number ='" & rNbr & "'"
    Set RecSal = DBEngine(0)(0).OpenRecordset(strSqlSal, dbOpenDynaset)
       'Check if there were no Sales.
    If IsNull(RecSal) Then
        SumSal = 0
    Else
        'Loop through the matching records
        Do While Not RecSal.EOF
           If Not IsNull(RecSal(0)) Then
             SumSal = SumSal + RecSal(0)
           End If
        RecSal.MoveNext
        Loop
    End If
    QOH = SumPur - SumSal
    RecPur.Close
    RecSal.Close
   
Exit_Handler:
    'Clean up
    Set RecPur = Nothing
    Set RecSal = Nothing
    Exit Function
Err_Handler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "QOH()"
    Resume Exit_Handler
End Function

[June7]

If InventoryNumber is a number type then remove the apostrophe delimiters. Same for Sales.Number.

Number might be a reserved word and advise to avoid reserved words as names. Better would be SalesNum. More informative also.

[FrustratedAlso]

They are both strings.

[orange]

Which line is giving the error?

[FrustratedAlso]

Figured it out, was select two fields had to change to rec (1) instead of rec (2). works great now. Sometimes you just stare at it too long