Re: Subform Calculated Sum with Error on Main form

From: Allen Browne (AllenBrowne_at_SeeSig.Invalid)
Date: 03/18/04 

Date: Thu, 18 Mar 2004 09:48:15 +0800

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.
> >
>
>