Calculated Control Gets Wrong RecordCount

[idbr]

Hi,

I have a calculated control sitting on a UI form that tells the user how many subform Contact (people) records exist for the current mainform Company record.

The ControlSource is:

="Contact Information - " & [Forms]![frmMain]![subfrmContacts].Form.Recordset.RecordCount & IIf([Forms]![frmMain]![subfrmContacts].Form.Recordset.RecordCount=1," Contact"," Contacts")

The users can filter* the records on varying criteria. Applying the filter doesn't update the control though! E.g. if a company has 3 contacts, but only one is pulled into the filter, the control still displays "Contact Information - 3 Contacts"

I've attempted to force the requery in a number of places:

mainform Current()
subform Current()
two navigation (previous record, next record) buttons' Click()

None of these work. However if I execute the same line (forms!frmMain.txtContactCount.requery) from the immediate window the control updates.

Any suggestions as to why this is happening?

Thanks, Iain

*Filter actually changes the form & subform RecordSource & Requeries rather than being a true filter.

 

[Eupher]

Hi, idbr,

Have you tried Me.Recalc?

Ken S.

 

[idbr]

Doesn't help I'm afraid Ken...

 

[pjm]

Why not just set the control source to =Count([someFieldInTheSubform]) then it will update correctly whenever the filter changes.

HTH

pjm