Solved

Sum datasheet subform field on main form

Posted on 2007-03-21
14
557 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
14 Comments
 
LVL 75
ID: 18768337
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
ID: 18768345
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
ID: 18768357
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 75
ID: 18768377
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
ID: 18768403
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
ID: 18768439
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
ID: 18768446
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
 
LVL 75
ID: 18768467
Sorry ... Initially, I thought ... ok ... confirming ... the control with the Sum expression is ... on the Main form, right?

mc
0
 

Author Comment

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

Tim
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 total points
ID: 18768488
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
ID: 18768498
Your code solution resolved it. Thanks!

Cheers,
Tim
0
 
LVL 75
ID: 18768515
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
ID: 18768824
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
ID: 18768887
Mindclutter ... always present ...

Glad it worked out ...

mx
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

636 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