Count Empty Continuous Form Records (1 Viewer)

[PNGBill]

Ms Access 2010 accdb
The age old issue of dealing with a continuous form when there may be no records.

This is a SubForm. I have nearly resolved the issue by adding an unbound text box control on the form footer and code to the On Current event of the form.

If Not (Me.Recordset.BOF And Me.Recordset.EOF) Then
        Me!txtRefinanceAmountSum.ControlSource = "=Sum([RefinanceAmount])"      'records exit
    Else
        Me.txtRefinanceAmountSum = 0                                            'no records so use Zero
    End If

If I enter the main form and select a date that has no records in the subform, the correct result appears - $0.00.
If I enter a date where records exist, the correct value result ie the =Sum(... works.

However... If I then select a date with no records, I get nothing ie, the $0.00 does not rsult.

What could be happening here ? Could the fact that there were records be tricking the If Then statement to assume records when there are none ?

 

[spikepl]

I dunno (as to your question).

To display a count for a subform that may or may not have records, I use this :

=IIf(Not IsError([frmOrderlinesSub].[Form]![txtCount]),[frmOrderlinesSub].[Form]![txtCount],0)

Perhaps you could adopt this to your needs.

 

[PNGBill]

Thanks spikepl, I used this adaption of your code

=IIf(Not IsError([Forms]![FrmLoansIssuedReport]![FrmLoansIssuedReportRefinanceSubForm].[Form]![txtLDPK]),[Forms]![FrmLoansIssuedReport]![FrmLoansIssuedReportRefinanceSubForm].[Form]![txtLDPK],0)

Works when records are present but no data at all when no records.

I have referenced the control by using full name of main form and subform.

All controls are on the subform - for this example.

 

[gemma-the-husky]

if you have a non-updateable recordset (or disallowadditions) , and no records, you get a strange form where fields don't get populated, and there is no current event.

allow a new record line if you can, then the current event will probably fire normally

 

[PNGBill]

Thanks Gemma, The form is Yes to AllowAdditions, Deletcions, Edits and Filters but the recordset is an sql with four or five tables which doesn't really allow a new record to be entered.

I would have thought a Requery would have resolved this

My code works, If i closed and opened the form each time