Strategy for Form that shows 100 records at a time

[davefwelch]

Working on a project for a client, and I'm usually pretty good at coming up with VBA solutions to unique problems. This one has me stumped.

The recordsource is a query with over 6,000 records. The form currently lists the records in datasheet format with header and footer for things like buttons and filtering. The client wants to be able to go from page to page of the souce query, showing 100 records on the form at a time. But at the same time, they should be able to filter or sort the data source in it's entirety. The person who created the form came up with what seems like an awful solution to the problem. It seems to use a random number generator to determine how many records to portray at a time. I see this in the code as well as in operation, because the number of records on page to page varies. It doesn't even start out at 100! Worse yet, using a sort on the page only sorts the records that are visible.

Does anyone have a strategy they can offer me to solve this problem? Is it even possible to program this functionality?

 

[jdraw]

Can you really see 100 records on a form? I would think a strategy that displayed a page of info would be practical.

Total records/records per page = number of pages

Is there something about records that would have you looking at page 6 instead of page 2 etc? Some buttons to jump to/bypass x pages...

 

[davefwelch]

Yes, there are navigation buttons in the form footer for Next Page and Previous Page. And I can see hidden that someone tried doing the Page 1, Page 2, Page 3 . . . links, but that's not a current function on the form.

And I don't discount what you're saying about 100 being a lot of records to see on the form. There is a vertical scroll bar. I think the initial reasoning for developing the form this way had to do with performance, although I just don't see it.

I think I just need to scrap the idea of having x records per page on the form, but it seemed like an interesting topic otherwise. I have been wholly unable to formulate a successful Google search that would give me an inkling at a solution to this problem. I just know that the one currently in the form is horrible!

Any more input folks? Maybe even just to help me with a good Google search string?

 

[jdraw]

Perhaps you could tell us in overview the kind of records and business environment you are dealing with.

 

[spikepl]

Web solutions do this all the time, it's called Paging. IF you google for paging vba you are bound to find something - essentially it is the question of querying a specific subset of records (and keeping track of how far you are, a task simplifiid in MS SQL but a bit of a pain in Access).

 

[MarkK]

A recordset has an AbsolutePosition property which, if you set it, navigates the current record pointer to that record. A recordset also has a .Move method which takes a numeric parameter, positive or negative, that does exatly what you'd expect.

So if you want to move forward 100 records . . .

Private Sub cmdNext100_Click()
   Me.Recordset.Move 100
   [COLOR="Green"]'Me.Recordset.AbsolutePoition = Me.Recordset.AbsolutePosition + 100[/COLOR]
End Sub

If you want to move to record 300 . .

Private Sub cmdGoTo300_Click()
   Me.Recordset.AbsolutePosition = 299
End Sub

 

[spikepl]

Ah of course.

The web people do it to pull only one pageful of data across the web, whereas you can have a recordset loaded and just display bits of it ...

 

[MarkK]

Yeah, and a recordset knows enough to only partially load itself, as required. After you open a recordset and before you navigate anywhere--or bind to a form--the recordcount is 1.

 

[davefwelch]

Thanks for the suggestions guy!

I have convinced the users that having this functionality doesn't really jive with their more important objective; sorting and filtering the records in order to print a report.

 

[gemma-the-husky]

does the notional 100 records correspond to anything more accessible - like a date. it would be easier to page through a date at a time.

having said that, training the users to getting used to filtering records in a different manner is the better choice!

handling large sets of data is often non straightforward, and needs some reflection and consideration.