Solved

Sum datasheet subform field on main form

Posted on 2007-03-21
14
547 Views
Last Modified: 2013-11-28
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.
0
Comment
Question by:TimothyHughes
  • 8
  • 6
14 Comments
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
0
 

Author Comment

by:TimothyHughes
Comment Utility
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
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
0
 

Author Comment

by:TimothyHughes
Comment Utility
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
0
 

Author Comment

by:TimothyHughes
Comment Utility
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
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Sorry ... Initially, I thought ... ok ... confirming ... the control with the Sum expression is ... on the Main form, right?

mc
0
 

Author Comment

by:TimothyHughes
Comment Utility
yes. I have a simple example if I could get it to you.

Tim
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 500 total points
Comment Utility
Use the code solution ... the issue is probably what you said .... no records on the *datasheet* subform = no control per se ... to reference.  The code solution gets around that issue.

mx
0
 

Author Comment

by:TimothyHughes
Comment Utility
Your code solution resolved it. Thanks!

Cheers,
Tim
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
0
 

Author Comment

by:TimothyHughes
Comment Utility
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
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Mindclutter ... always present ...

Glad it worked out ...

mx
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now