Running Sum in Form/Subform yields "#name?" error

[TerriIT]

thread702-1048752

Hi,
I've been trying unsuccessfuly to get this to work. In a subform, I have followed these directions and looked it over and over, yet still get the "#name?" value displayed in the Total field.

It's not the "#error" as mentioned in another thread on a similar subject. Any ideas or questions on what I can be doing wrong?

I have a main form for a few fields and then in the subform, there is hours to be input. I created the =subsum() unbound textbox at the end of the record and get this "#name?" value displayed. I'd love to see if I can get this hours running total to be displayed on the main form.

Thanks in advance for any help and support!

 

[Ray1127]

If you want a usable answer you'll have to make your question a little clearer. =subsum() refers to a function since that function does not exist in MS Access it must be a user defined function what does it do? Also why a subform?

 

[TerriIT]

I'm sorry for not being clear. I was referring to the FAQ702-5248. On this referred thread, next to last box, it says to put "in the details section of the form, add an unbound textbox. Set the Control Source to CODE =SubSum()"

So that is what I was referring to in my message.

It's a subform because there will be many activities/records with a varying hour durantion for each activity for each staff that is indicated on the main form.

Hope this helps? Been a while since I've been "technical" - so I'm trying to get "back on that horse again." Thanks!

 

[dhookom]

The SubSum() function required some modification by you. Did you do this modification? If so, can you provide your code and some validation that it is correct?

Duane
Hook'D on Access
MS Access MVP

 

[TerriIT]

Here's what I have under the modRunSum (code)

Option Explicit

Public Function frmRunSum(frm As Form, pkName As String, sumName As String)

Dim rst As DAO.Recordset, fld As Field, subTotal

Set rst = frm.RecordsetClone
Set fld = rst(sumName)

'Set starting point.
rst.FindFirst "[" & pkName & "] = " & frm(pkName)

'Running Sum (subTotal) for each record group occurs here.
'After the starting point is set, we sum backwards to record 1.
If Not rst.BOF Then
    Do Until rst.BOF
        subTotal = subTotal + Nz(fld, 0)
        rst.MovePrevious
    Loop
Else
    subTotal = 0
End If

frmRunSum = subTotal

Set fld = Nothing
Set rst = Nothing

End Function

Hope this is what you needed to help? Thanks.

 

[dhookom]

Did you find your call to the SubSum() function? This is what I asked previously.

Duane
Hook'D on Access
MS Access MVP

 

[TerriIT]

I'm sorry, I don't think I know what you mean - the call to the SubSum() function? I'm guessing here you mean did I put in the subform the unbound textbox and it's control source is =subsum() and on the afterupdate event is

 DoEvents
Me.Recalc

?? During and after I composed this reply, I double-checked and they are there and seem to be in place.

 

[TheAceMan1]

How are ya TerriIT . . .

[blue]Next . . . . for each form where you require a runnung sum, add the following code to the corresponding forms code module. This code must be in form module as were passing 'Me' as the current form object. The programmer must supply all items in [purple]purple[/purple].

[blue]Private Function SubSum()
[green]'*************************************************************
'* pkName   - Existing unique fieldname (usually primarykey) *
'* sumName  - Name of the field to runsum                    *
'*************************************************************[/green]
   If Trim(Me![purple][b]pkName[/b][/purple] & "") <> "" Then 'Skip New Record!
      SubSum = frmRunSum(Me, "[purple][b]pkName[/b][/purple]", "[purple][b]sumName[/b][/purple]")
   End If
End Function[/blue]

[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TerriIT]

I GOT IT! Awesome! Thanks guys - for helping me think this through and checking where I put the code(s). It looks like I put it in the main form and not in the Sub-form - big difference there. I'll come back with other stumping issues as I finish this one project and start the next one. Thanks again!