dSumRecordset won't trigger and returning #Name? error 2

[Jessica22]

thread702-1608030

I would really love to use the function provided by MajP called dSumRecordset, however, I cannot get it to work? I am using Access 2016. I put in another function with a break point on the first line to ensure vba was triggering. The function I wrote is triggering but dSumRecordset is not. The control that contains the formula is giving #Name? error. Any ideas? Here's the code:

Public Function MyDSum(expr As String, RcrdSet As DAO.Recordset, sz_crtria As String) As Double

  On Error GoTo CATCH:
  If Not sz_crtria = "" Then
    RcrdSet.Filter = sz_crtria
    Set RcrdSet = RcrdSet.OpenRecordset
  End If
  
  While Not (RcrdSet.EOF)
    dSumRecordset = dSumRecordset + (Nz(RcrdSet.Fields(expr), 0))
    RcrdSet.MoveNext
  Wend
  Exit Function
  
CATCH:
  MsgBox Err.Number & " " & Err.Description
End Function

and here is the call: =mydsum("[FY+2BaselineGvt]",[me].[RecordsetClone],"[RP_Program]='A'")

Thank you in advance!!
Jessica

 

[Andrzejek]

Since you renamed [tt]dSumRecordset[/tt] to [tt]MyDSum[/tt], wouldn't you have to do:

...[blue]
MyDSum[/blue] = [blue]MyDSum[/blue] + (Nz(RcrdSet.Fields(expr), 0))
...

---- Andy

There is a great need for a sarcasm font.

 

[Jessica22]

Yep! You are right, however, I only changed it to MyDsum after the #Name? error was produced on the original dsumrecordset so, unfortunately, that doesn't solve the problem. The function's code is not triggering. Here is the code fixed:

Public Function MyDSum(expr As String, RcrdSet As DAO.Recordset, sz_crtria As String) As Double

  On Error GoTo CATCH:
  If Not sz_crtria = "" Then
    RcrdSet.Filter = sz_crtria
    Set RcrdSet = RcrdSet.OpenRecordset
  End If
  
  While Not (RcrdSet.EOF)
    MyDSum = MyDSum + (Nz(RcrdSet.Fields(expr), 0))
    RcrdSet.MoveNext
  Wend
  Exit Function
  
CATCH:
  MsgBox Err.Number & " " & Err.Description
End Function

Good eye, definitely needed fixing, but why won't the function trigger?

Thank You,
Jessica

 

[combo]

As later in the post, you need a function in the form's module to properly call the form:

Public Function getRunSum(expr As String, sz_crtria As String) As Double
  getRunSum = Nz(MyDSum(expr, Me.Recordset, sz_crtria), "")
End Function

And now the call: [tt]=getRunSum("[FY+2BaselineGvt]","[RP_Program]='A'")[/tt]

combo

 

[Andrzejek]

combo,

Since function MyDSum returns Double, so in worst case scenario it would return 0, why NZ function around it? It should never be NULL (right...?)

Plus, getRunSum function returns Double, and the NZ function around MyDSum could (theoretically) return NULL which would be changed to ""

---- Andy

There is a great need for a sarcasm font.

 

[combo]

Since function MyDSum returns Double, so in worst case scenario it would return 0, why NZ function around it? It should never be NULL (right...?)

You are right, I've just slightly modified an example from the linked thread, some posts below the dSumRecordset function code, without analysing possible output.
The issue with the call: =mydsum("[FY+2BaselineGvt]",[me].[RecordsetClone],"[RP_Program]='A'") is that access does not recognise me.RecordsetClone as recordset in the second argument of the function, so it adds square brackets to me.RecordsetClone initially passed and finally displays #Name?
It would be simplest, but less universal, to write single function in form's module with the recordset source in the code and two arguments: field name and filter.


combo

 

[Jessica22]

OK, that is what I suspected . . . I did what you suggested (remove the recordset from the call and move the code to the form's module so that me.recordset is known) and IT WORKED!! Geez, this should honestly be posted on Allen Browne's page (if it's not already), I am going to use this so much! For other people who wish to use this, i did not use the getRunSum, just the MyDSum function: I have totals by Department and when a user filters to a certain department I need the totals to also filter. However, the way the data is structured, I can't make a query show the data (I would actually have to create and union several crosstab queries to get desired data). Therefore, I have to use a DSum but the DSum is looking at the table referenced in the call, not the recordset of the form, hence the need for the function. Big thanks to Andrzejek and combo for the help and CLARITY of the issue, it is so nice to find out WHY Access won't do something (the reasoning is sometimes so hard to come by). Here's the final code:

Public Function MyDSum(expr As String, sz_crtria As String) As Double
  Dim RcrdSet As DAO.Recordset
  
  Set RcrdSet = Me.RecordsetClone
  
  On Error GoTo CATCH:
  If Not sz_crtria = "" Then
    RcrdSet.Filter = sz_crtria
    Set RcrdSet = RcrdSet.OpenRecordset
  End If
  
  While Not (RcrdSet.EOF)
    MyDSum = MyDSum + (Nz(RcrdSet.Fields(expr), 0))
    RcrdSet.MoveNext
  Wend
  Exit Function
  
CATCH:
  MsgBox Err.Number & " " & Err.Description
End Function

And then the control source in the control that is providing the sum: =mydsum("[FY+2BaselineGvt]","RP_Program='A'")


Thank you, thank you, thank you!!
Jessica