[ALMOST RESOLVED] Selecting number of records to display

[aikidokid]

I'm having trouble working out which part(s) of your code are related to that - you have shown us lots of parts that seem to be irrelevant to it, and missed out parts that seem to be needed (such as variable declaration/initialisation).

I usually don't show enough of my code.
I have this part that adds the totals: (Unless I am totally missing your point - I have before )

            If rs!Finance_Expenditure & "" = 0 Then
                .TextMatrix(lngRow, 4) = ""
            Else
                .TextMatrix(lngRow, 4) = Format(rs!Finance_Expenditure.Value & "", "#####0.00")
            End If

            CurExpenditure = CurExpenditure + Val(rs!Finance_Expenditure.Value & "")

            If rs!Finance_Income.Value & "" = 0 Then
                .TextMatrix(lngRow, 5) = ""
            Else
                .TextMatrix(lngRow, 5) = Format(rs!Finance_Income.Value & "", "#####0.00")
            End If

            CurIncome = CurIncome + Val(rs!Finance_Income.Value & "")
            CurTotal = (CurIncome - CurExpenditure)

            If CurTotal < 0 Then
                .TextMatrix(lngRow, 6) = Format(CurTotal, "#####0.00")
                .Row = lngRow
                .Col = 6
                .CellForeColor = vbRed
            Else
                .TextMatrix(lngRow, 6) = Format(CurTotal, "#####0.00")
            End If

I will run through this again.

Oh, and this code should be in at least two different subs - the point of paging is that you don't repeatedly create/fill the recordset (the code before "Dim intRecord"), but simply move around within it as needed (the line after it).

I didn't think I was repeatedly creating/filling the recordset!

The recordset should stay open, and you should not be re-using it for other work (the "SELECT SUM" bits); use other recordset objects for that instead.

Consider this changed.

The important thing you need to deal with for the totals tho, is that you do not get all data when you read the recordset in this way - you only get the page of records that you asked for.

As the user could easily go to the last page (without looking at the ones before it) or back a page (say from page 3 to page 2) you cannot use a variable to do the work, unless your code 'secretly' loops thru all of the pages before it.

What you could do is, at the end of reading a page, find the total using an SQL statement ("SELECT SUM(??) ...") with an appropriate Where clause to only show the data so far (based on values from the last row of data you have displayed - date and ID in this case).

I don't know if this is too simplistic, but as this database is never going to be huge (it's only for a small club, so I guess no more than maybe 10 - 20 records in a month!) and knowing that each page is going to be 15 records (set in intRecordsPerPage) could I just have an array loaded at the beginning storing the total up to every 15 records and then use this to add to each next page?