AccessBlog.net

News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Alex
Name:Alex Dybenko

Location:Moscow, Russia

Thursday, December 17, 2009

RecordsetClone in Access 2007 forms

I did not use Access 2007 much, most of my customers still on Access 2003, which works fine. But recently, when I tested my applications on Access 2010, I found that calculating number of records in subform using RecordsetClone.RecordCount property produces #Name error. Talking about Northwind’s Orders form expression on main form’s textbox is:

=[Orders Subform].[Form].[RecordsetClone].[RecordCount]

Allen Brown mentioned it as a bug. But looks like it is not, this is a feature! Beta Engineer told me that RecordsetClone.RecordCount is replaced with Count() function (was usable in previous Access versions), which you can use it as following.  You have to add an expression field “txtCount” in the subform footer and set the control source to =Count(1). Then on the mainform add a textbox that refers to this subform control =[Orders Subform].[Form]![txtCount]. Same way like you deal with Sum() in subforms. Count() is something like Count in SQL, so in sample above 1 is a field number, and you can also use Count(*).

image

Count() also works in Access 2003, and would be good idea to start using in instead of RecordsetClone.RecordCount to make your applications compatible to newer Access versions.

Labels: , ,

2 Comments:

Blogger DataPlus - Custom Data Services said...

Good catch Alex. I was just using Sum() in the footer yesterday and think using Count() in this way is more straightforward.

I couldn't bring myself to upgrade to 2007 but have been beta testing 2010. I like it, it has some good new features and I will begin to move to it when it's released.

4:26 PM  
Blogger Alex Dybenko said...

Hi,
yes, i think i even used Count() or Max() once, but completely forgot about it, just get used RecordsetClone.RecordCount from old times.
Agree that 2010 looks vry good, looking forward for release!

1:25 PM  

Post a Comment

Links to this post:

Create a Link

<< Home