[AccessD] Expense subreport

Jim Dettman jimdettman at verizon.net
Wed Feb 15 04:35:04 CST 2012


 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?

-- 
John W. Colby
Colby Consulting

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

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list