Solved

Sum datasheet subform field on main form

Posted on 2007-03-21
14
551 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
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
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 Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

830 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