Link to home
Start Free TrialLog in
Avatar of TimothyHughes
TimothyHughes

asked on

Sum datasheet subform field on main form

I have a form which has a datasheet subform showing detail lines (think along the lines of an invoice header/detail form).  

I wanted to have a subform field summed on the mainform. I accomplished this by putting a Sum() textbox in the subforms footer and referencing it by it's name (it works great!) in a textbox on the mainform.

Here's the problem; if there is no records in the subform, it returns "#Error" as the value of my summed textbox on the main form. I go into debug and find the textbox gives an "Run-time error '9': Subscript out of range". I believe that this is happening because the subform footer does not exist, so I am referencing a non-existent textbox.

Here my question , can I trap this error and force it to be zero if this error happens OR can I go about this in an easier way?

Thanks in advance.
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Try this in the text box:

=IIF(IsNull(Forms!MainFormName!SubFormControlName.Form!YourTextBoxName),Null, <Your Sum Expression here>)

where 'SubFormControlName' is the Name of the subform control on your main form

mx
Just for better clarity ... here is the expression for IsNull    ... I've included spaces for clarity

Forms  !  MainFormName  !  SubFormControlName  .  Form  !  YourTextBoxName

There should be no spaces in the actual expression ... and ... notice the Dot between ControlName and Form.

mx
Avatar of TimothyHughes
TimothyHughes

ASKER

Thanks for the quick response.

I have tried conditional IIF's and also used the NZ() function but the field is not a null; it's returning an error. I want to trap the error OR come up with a better solution around this.

Thanks,
Tim
Sorry ... I actually meant ... for the last part of the expression ...

Forms  !  MainFormName  !  SubFormControlName  .  Form  !  [SomeIDFieldName]

where 'SomeIDFieldName' is the Link Child field name on the subform or some other field in the subform record source.

Can you give this a quick try.  There are code solutions also
mx
Hey MX,

Hang on. I see that your suggestion is different form what I have tried ( I tried to capture the null as the textbox value). Let me test.

Tim
It failed like my examples did as well. Here's the exact line I tried.

=IIf(IsNull(Forms!frm_Prop_Head!Chld_Prop_Detl.Form!curXcost),Null,chld_Prop_Detl.Form!curXcost)

I  have also tried:
=chld_Prop_Detl.Form!curXcost
=nz(chld_Prop_Detl.Form!curXcost,0)
=IIF(isnull(chld_Prop_Detl.Form!curXcost), 0, chld_Prop_Detl.Form!curXcost)

They work if there are details, I get the error if there are no details. I belive this is a case of the subform textbox not existing if there are no details.

Tim
ok ... and here is a code solution:

In the OnCurrent Event of your main form:

Private Sub Form_Current()
   
    If Me.fsub1.Form.RecordsetClone.RecordCount = 0 Then
         
        Me.txtTest.ControlSource = ""
    Else
        Me.txtTest.ControlSource = "=<YourExpressionHere>"
       
    End If

End Sub

Where txtTest = your control name  and fsub1 is the Name of your subform control.

Note:   THIS ... "=<YourExpressionHere>"    must equate to a String expression ... so, be careful with quotes, etc.  Not sure what you exact Sum expression is ?  But this is the drift.  I just tested it to be sure on a form / subform I rigged up.

mx
Sorry ... Initially, I thought ... ok ... confirming ... the control with the Sum expression is ... on the Main form, right?

mc
yes. I have a simple example if I could get it to you.

Tim
ASKER CERTIFIED SOLUTION
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Your code solution resolved it. Thanks!

Cheers,
Tim
WHEW, lol.  Sorry for the confusion.  I can make the 1st trick work when the subform is a Continuous form instead of a datasheet.  I don't normally use datasheet views ... so, I spaced off a little there.  

Anyway ... perseverance prevails ... take care ...

mx
I was trying to trap the error and then work around it. Your approach of finding if there was no records on the subform and then reacting from that worked like a charm. Sometimes you get stuck in a specific mindset and cannot break free.

Thanks for the quick resolve!

tim
Mindclutter ... always present ...

Glad it worked out ...

mx