Number of records returned on a form

I have recently converted my Client's MS Access database to an SQL Database. After the conversion I have the following issue:

I have a Form, based on a Query as the record source. When a subset of the records in the table (that the query is based on) is selected, the number of records returned in the subset is not displayed at the bottom of the form.

When MS Access the information was available. At the bottom of the form where it states: Records 1 of nn where nn is the number of records. after the conversion the nn is not there.

Any one that can assist to get the nn value showing again?
Anton GreffrathAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Paul Cook-GilesSenior Application DeveloperCommented:
In the OnOpen event of the form, put this code:

DoCmd.GoToRecord , , acLast
DoCmd.GoToRecord , , acFirst

Open in new window

This will force the entire recordset into active  memory, and give Access the data it needs to populate the Record 1 of nn display.  :)
(You can get the same result by pressing Control-End when you open the form.)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
If your BE is a RDBMS, it is far better to severely limit the number of rows returned as the recordset of a form.  You loose any speed advantage you might gain from SQL Server if you bring unfiltered recordsets across the network in order to filter it locally and the larger the recordset, the larger the cost of doing it.

Typically, Access only retrieves enough records to populate the visible rows of a form.  It then requests additional rows in the background or as you scroll through the recordset.  By having to see the n value from Record 1 of n, you are forcing Access to bring down all the records in the recordset whether the user ever will actually need to view them or not.

Many people who convert Access applications from Jet to SQL Server are flabbergasted when the app is sluggish and slower than it ever was with the Jet BE.  Access is tuned to work with Jet/ACE.  You have to take some control to get that same kind of performance against linked SQL Server tables.  And the best place to start is to change your method of filtering.  Let the user enter selection criteria to bring down as few rows as possible.  Then change the selection criteria to go back to the server to get a different set of data.
Anton GreffrathAuthor Commented:
Thanks Paul Cook-Giles, I implemented your solution and it worked 100%
Anton GreffrathAuthor Commented:
Export Exchange Rocks!
Anton GreffrathAuthor Commented:
Thanks Paul. . . .
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.