Re: Subform Calculated Sum with Error on Main form

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Don Kline (Dkline001_at_comcast.net)
Date: 03/18/04 

Date: Thu, 18 Mar 2004 03:34:46 -0500

I'll try this when I get into my office today. At the moment it's about 3:30
AM and - yes - I am losing sleep over this.

I'm still trying to figure out why when the RecordCount is zero, why the
subform field is blank and why the mainform shows the error. The MsgBox
tells me the value is zero if no records but the IIf on the subform results
in a blank.

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
news:4058ffe7$0$22503$5a62ac22@freenews.iinet.net.au...
> The RecordCount reflects the number of records that have been loaded at
the
> time you ask for it. Once they are all loaded, you get the right count,
but
> initially you typically get 1 (because 1 has loaded) or zero (if there are
> none).
>
> Forcing the RecordsetClone to MoveLast, followed by a Recalc of the form
> should give the correct count.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Dkline" <Dkline001@comcast.net> wrote in message
> news:OSb6mWEDEHA.1544@TK2MSFTNGP09.phx.gbl...
> > Progress!
> >
> > =IIf(Forms![Policy Inventory2]![frm2004
> > Premiums].Form.RecordsetClone.RecordCount=0,0,1)
> >
> > Combo45 is the listbox from which I am selecting the active case. So I
> did
> > an AfterUpdate which displays a MsgBox with the result.
> >
> > Private Sub Combo45_AfterUpdate()
> > Dim varSum As Long
> > varSum = Forms![Policy Inventory2]![frm2004
> > Premiums].Form.RecordsetClone.RecordCount
> > MsgBox varSum, , "Scheduled Premium"
> > varSum = Forms![Policy
> > Inventory2]![frmWDByPolicy].Form.RecordsetClone.RecordCount
> > MsgBox varSum, , "Withdrawal"
> > End Sub
> >
> > The MsgBox shows either a 0 or the correct number of records.
> >
> > BUT...
> >
> > On the subform in the footer I have my total field.
> >
> > =IIf(Forms![Policy Inventory2]![frm2004
> > Premiums].Form.RecordsetClone.RecordCount=0,0,1)
> >
> > If there are 1 or more records, I get the 1 in the field and the Main
Form
> > also shows the 1.
> >
> > If there are 0 records, I don't get a 0 in the field, I get a blank. The
> > Main Form shows "#Error"
> >
> > So I'm still missing something.
> >
> >
> > "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> > news:405791c5$0$22511$5a62ac22@freenews.iinet.net.au...
> > > The Immediate Window is the best way to debug. Press Ctrl+G.
> > >
> > > The "Form" bit is a reference to the current form. In the Immediate
> > window,
> > > you will need to use "Forms!Formname" instead. For a main form,
that's:
> > > ? Forms![FormName].RecordsetClone.RecordCount
> > > For a subform:
> > > ?
> > Forms![FormName]![SubformControlName].Form.RecordsetClone.RecordCount
> > >
> > > A zero in place of Null is fine.
> > >
> > > Make sure this text box that displays the results does not have a Name
> the
> > > same as one of your fields.
> > >
> > > --
> > > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > > Tips for Access users - http://allenbrowne.com/tips.html
> > > Reply to group, rather than allenbrowne at mvps dot org.
> > >
> >
> >
>
>

Relevant Pages

  • Re: Resize sub forms using vba - no top property
    ... I'm using Access 2000 and had to change the recordcount bit to get it to work: ... > I would like to be able to do a similar thing with the subform objects, ... >>>its internal data physical size is ie what size it needs to be to display ... >>>You can use the InsideHeight and InsideWidth properties to determine the ...
    (microsoft.public.access.modulesdaovba)
  • Re: calculated caption on tab control
    ... If the recordcount required is the ... Private Sub Form_Open ... Then in the Form_Current event of the subform ... Public Sub RefreshTabCaptions() ...
    (comp.databases.ms-access)
  • Re: Get count of rows in subform programmatically
    ... Examine the RecordCount of the RecordsetClone of the Form in the subform ... Allen Browne - Microsoft MVP. ... rather than allenbrowne at mvps dot org. ...
    (microsoft.public.access.formscoding)
  • Re: Creating sequential numbers in subform
    ... to get an accurate recordcount you need to Move to the end of the ... Private Sub Form_BeforeInsert ... || I'm creating my first Access form and am trying to figure out how to ... || field within a subform generate sequential numbers. ...
    (microsoft.public.access.forms)
  • Re: Detect if subform has records
    ... Test the RecordCount of the RecordsetClone of the Form in the Subform ... append query to append questionaire, sometime user click the button twice ...
    (microsoft.public.access.forms)