Solved

Sum datasheet subform field on main form

Posted on 2007-03-21
14
549 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
 
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 - Access MVP) 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

911 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

20 Experts available now in Live!

Get 1:1 Help Now