Zero Count of Records in Sub-form 1

[rosieb]

I've put a control in the header of a sub-form which gives a count of the number of records and use this for some calculations on my main form.

However, where there are no records, I cannot get this to return a zero. Initially I assumed the value in the control was null, but I've tried:

IIf(IsNull(count([Surname])),0,Count([Surname]))

which gives a blank which, in turn, gives errors in my calculations.

So, can anyone tell me what the value is, if it isn't zero or null.


Rosie
"Never express yourself more clearly than you think" (Niels Bohr)

 

[Crowley16]

hey rosie...

didn't see it was you till I actually read all the post...

anyway...

try putting:

count(nz(Surname, ""))

--------------------
Procrastinate Now!

 

[TheAceMan1]

How are ya rosieb . . . . .

Try:

[blue]   =[Form].[RecordsetClone].[RecordCount][/blue]

See Ya! . . . . . .

 

[rosieb]

Hi Crowley, amazing who you run into...

Hi AceMan

Thanks both, unfortunately neither solution works when there are no records in the subform.

Rosie
"Never express yourself more clearly than you think" (Niels Bohr)

 

[PHV]

And this ?
Nz(Count([Surname]), 0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rosieb]

PHV
Thanks, but no, not even that.

(and it seemed such a simple thing.)

Rosie
"Never express yourself more clearly than you think" (Niels Bohr)

 

[RoyVidar]

Where is this expression? Subform or the main form?

The count of records in a form, should be available through the mentioned methods:

[tt]=count([fieldname])
=count(*)
=[Form].[RecordsetClone].[RecordCount][/tt]

There may be some exceptions, for instance if the sub forms allowadditions property is set to no, where it shows nothing, and it's a bit messy trying to get the number. I don't even think the on current event of the subform fires in such cases.

Could it be possible to use some event from the main form?

I think something like this might work, for instance in the on current event of the main form:

[tt]dim rs as dao.recordset
set rs=me!frmNameOfSubformControl.form.recordsetclone
msgbox rs.recordcount[/tt]

Note - use the subform control name, which may differ from the name of the subform as viewed in the database window. You may also need a [tt]rs.movelast[/tt] to retrieve the correct recordcount, as the dao recordset isn't always fully populated unless all records are "accessed".

Roy-Vidar

 

[rosieb]

Hi Roy

It's simply a control in the header of a sub-form.

But you've hit the nail on the head. AllowAdditions is set to NO.

I've got my calculation working on the main form using
=[MaxAttendees]-[sfrmInstView].[Form].[RecordsetClone].[RecordCount]

But I was just puzzled as to why I couldn't get the count to work properly on the subform.

Thanks for that.

(Crowley, AceMan, PHV, sorry for missing out the critical fact.)

Rosie
"Never express yourself more clearly than you think" (Niels Bohr)