design for viewing 2000 records

[normajean]

Hi:
I have built an Access 2000 front end that displays data from a backend SQL database. The recordset is returned via a vba pass-through query, and since I send over all the criteria to the server (in a parsed 'Where' statement) the data is returned with very good performance.
My problem is that the size of the recordset is sooo large, (about 2000 records per user). The first page of the form gets populated really fast, but when the user wants to scroll down to view accounts further down in the form, there is a big performance hit.
The recordset size really can't be further restricted - the users are insurance billing reps at my hospital and the manager wants the billers to be able to view ALL of their open accounts.
Note: there will be NO updates to any of these records.
One idea I have read about is to control 'paging' on a form by using a cache? I have never used this capability and am wondering if this is the best idea for me? Would this mean disabling the scroll bars? Any other ideas or gotchas? Any advice would really be appreciated!
Thanks in advance, Normajean

 

[normajean]

I see what you mean; 2000 does seem like alot. I'll need to offer them the ability (via combo boxes) to further restrict the dataset.
What do you think of specifying default selections in several criteria-selection combo boxes when the form opens up? (Ex: Account age ("> 30 days"), insurances ("All") . (They would be able to change the criteria using the combo boxes if they want to see a different set of accounts). I'm assuming that it would be fairly simple to set a combo box default value, is that correct?

Then, I can add code to the pass-through query to use those values (defaults or new selections in the combo boxes).

What if they reset all combo box options to 'All' (i.e. no criteria specified for any combo box. Then all 2000 records would come back - should I just let them suffer the consequences of really poor performance that 2000 records gives, or do you have a better idea?
Thanks again for your insight! Normajean

 

[Autoeng]

This thread should help you out.

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=40954&highlight=search+form

 

[normajean]

Thanks autoeng and Pat! Really appreciate the ideas and design advice. Normajean