[AccessD] Expense subreport

Thanks Jim.

I am trying that and regardless of what I do it pulls the value from the first line record of the 
subreport.  I have a text control that sums the values in the detail section, and then a text 
control that pulls that value down into the footer.  All that works fine, IOW in the subreport it 
displays the sum in the footer.  However when I reference that footer sum control up in the parent 
form it displays the value from the first detail.

John W. Colby
Colby Consulting

Reality is what refuses to go away
when you do not believe in it

On 2/15/2012 5:35 AM, Jim Dettman wrote:
>
>   You can use the following:
>
> Function AvoidError(n As Variant, varReplaceWith As Variant) as Variant
>
> 10      On Error GoTo AvoidError_Error
>
> 20      AvoidError = Nz(n, varReplaceWith)
>
> AvoidError_Exit:
> 30      Exit Function
>
> AvoidError_Error:
> 40      AvoidError = varReplaceWith
> 50      Resume AvoidError_Exit
>
> End Function
>
>   set your control source to:
>
>
> =AvoidError(Forms![myFormName]![mySubformControlName].Form![mysubformcontrol
> ],0)
>
>    You can use this anytime you expect to have no records and possibly an
> error returned.  There is one small rub; in later versions of Access, there
> is an optimization where in some cases, AvoidError will not be called.  This
> is especially true if a domain function is used as part of the expression.
> If that happens, have one control like this:
>
> =Sum(<expression>)
>
> And then create a second control that references the first:
>
> =AvoidError(<reference to first control>)
>
> Jim.
>
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Tuesday, February 14, 2012 11:24 PM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] Expense subreport
>
> I am adding an expense subreport to my invoice.  When there is data (one or
> more records) in the
> subreport all is copacetic, however when there is no data, the subreport
> disappears entirely and the
> controls up on the main report which reference the controls in the subreport
> display #Error.
>
> Any suggestions as to how to handle this?  Do I have to "fake" data for this
> case?
>