[AccessD] What to do when subreport used as textbox source hasno data

Nancy,

    If the RecordSource for a subreport has no data, the only event that gets fired (for the report acting as SourceObject for the subreport control) is its open event. The subreport behaves as if its various sections (and controls located thereon) do not exist. Calculations referring to such controls are therefore bound to return an error.

    In order to overcome such a situation, the formula to be used in the parent report (either in a calculated text box or in VBA code) has to be as follows (Q_SR is the name of query acting as RecorSource for subreport, SR is the name of subreport control on parent report, and TxtSR is the name of control on subreport whose value is to be obtained) -

    ReqdValue = IIf(DCount("*","Q_SR") > 0, SR("TxtSR"), 0)

    It would be nice if you could kindly try it out and let me know.

Regards,
A.D.Tejpal
--------------
  ----- Original Message ----- 
  From: Nancy Lytle 
  To: Access Developers discussion and problem solving 
  Sent: Thursday, October 09, 2003 06:31
  Subject: Re: [AccessD] What to do when subreport used as textbox source hasno data


  Thanks everyone, I used the IIf statement and it works for the Sections that
  have no subreport data, but for the sections with it, it nows comes up
  "error#".

  Code I used for the txtAB Control Source

  =[njl_qryProgInitAuth].[Report]![Initial
  Authorization]+[njl_qryProgChanges].[Report]![SumOfAuthorizationChanges]-IIf
  ([rptExec_SP].[Report].[HasData],[rptExec_Sp].[Report]![SumComm],0)-IIf([rpt
  Exec_SP].[Report].[HasData],[rptExec_Sp].[Report]![SumOblig],0)

  I guess I should have said that this subreport is in the ProgramCode Footer
  section. Could that be a problem?

  TIA,
  Nancy
  ----- Original Message ----- 
  From: "Ron Allen" <chizotz at charter.net>
  To: "Access Developers discussion and problem solving"
  <accessd at databaseadvisors.com>
  Sent: Wednesday, October 08, 2003 7:39 PM
  Subject: Re: [AccessD] What to do when subreport used as textbox source has
  no data


  > Hi Nancy,
  >
  > The problem, I believe, is that when there is a null value
  > the whole expression evaluates to null. I have solved this
  > a few ways, but the easiest in most cases is something
  > like this: txtAB = iif(Sub1.field,Sub1.field,0) +
  > iif(sub2.field,sub2.field,0)- iif(sub3.field,
  > sub3.field,0). I work mainly in Acc97, but in Acc2k and
  > above there is also a NV function you could look at. This
  > isn't perfect for all cases, but it does work for me.
  >
  > HTH,
  >
  > Ron
  >
  >
  >
  > On Wed, 8 Oct 2003 19:00:43 -0400
  >   "Nancy Lytle" <LYTLEN at peoplepc.com> wrote:
  > >I have a main report with 3 subreports, all are related
  > >by the field
  > >ProgramCode.  There is a record in three of the reports
  > >(Main and 2
  > >subreports) for every ProgramCode.  I also use the values
  > >in the subreports
  > >to populate a text box - txtAB = Sub1.field + sub2.field-
  > >sub3.field -
  > >sub3.field.  This works fine except for the case where
  > >the ProgramCode has
  > >no record in Subreport3, then that subreport doesn't show
  > >and the textbox is
  > >filled with error#.  How do I get the textbox to use
  > >txtAB = Sub1.field +
  > >sub2.field where there is no sub report for the linked
  > >field.
  > >
  > >Hope I explained this okay.
  > >TIA
  > >Nancy L
  > >
  > >N_Lytle at terpalum.umd.edu
  > >240-274-0069

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20031009/ab726065/attachment-0001.html>