[ALMOST RESOLVED] Selecting number of records to display

[aikidokid]

If I want to display the last 30 records from an Access database into a flexgrid, how do I do this?

Is this the correct order of things?

1. Find total number of records
2. Take 30 from that total
3. Loop through from that value to the end

Is it this straight forward?

What if there are no records or less than 30?
What if records have been deleted? Would this stop me using the primary key as this may not still be in numerical order!

And any more problems I haven't thought of.

[szlamany]

Something like this should give you the 30 last primary keys

Select Top 30 PriKeyCol From SomeTable Order by PriKeyCol Desc

Can you get something like that to work??

Then you put that in a derived query:

Select Min(Q1.PriKeyCol) From
     (Select Top 30 PriKeyCol From SomeTable Order by PriKeyCol Desc) as Q1

This should return the "first" of those 30 primary key values - if you have less then 30 - it should still work.

Then it's as simple as:

Select * From SomeTable
    Where PriKeyCol>=(Select Min(Q1.PriKeyCol) From
     (Select Top 30 PriKeyCol From SomeTable Order by PriKeyCol Desc) as Q1)

[si_the_geek]

Just guessing where this is headed... if you want to show all records, but only 30 at a time (similar to the thread lists here) see the FAQ How do I get just a range of records, as you do in search results?

[szlamany]

@si - oh...doing the old mind-reading are we

[si_the_geek]

you never know, it might just be useful!

As usual, you'd answered the actual question too well for me to add much there!

[szlamany]

You know me - ask a question and I give a literal answer!

[aikidokid]

Just guessing where this is headed... if you want to show all records, but only 30 at a time (similar to the thread lists here) see the FAQ How do I get just a range of records, as you do in search results?

Thanks szlamany and si.

Actually si I wasn't going in this direction initially, but after downloading your example and taking a look through it I am going to give it a go to incorporate it into my project.
It may be more than I actually need, but then again, it gives the user more functionality and I get to learn more along the way.

[aikidokid]

you never know, it might just be useful!

Thanks for this example si_the_geek.

It was fairly easy to follow, even for me

It took me a little while to impliment this as I had other option to take into account.
Also, I wanted to understand what I was writing.
I changed some of the variable names to what I would have used, and this made it easier for me to follow.

I have kept this project example for future use.

People like myself, who are trying to teach themselves at home, would be lost without the good folk on this forum.

Thanks again.

[aikidokid]

The one last thing I am trying to achieve is to have a running total showing, carrying on from page one to however many pages there are.
At the moment, each page loaded only shows the total for those records showing.

I have been playing with this for a while, but not got it yet.
Could somebody point me in the right direction so I can have another go please.

Thanks

    strSQL = "SELECT * FROM tbl_Finances"
    strSQL = strSQL & " ORDER BY Finance_Date"
    
    'to ensure we get a RecordCount for our label, set the cursor location to Client
    rs.CursorLocation = adUseClient

    'set up the paging.
    'the page size specifies how many records are on each page
    rs.PageSize = intRecordsPerPage
      
    'the cache size specifies how many records should be stored in memory
    'If Cachesize and PageSize are equal (as they are here) then only
    'the records to be displayed will be cached in the recordset
    rs.CacheSize = intRecordsPerPage
    
    'open the recordset - as there is no WHERE clause on the query, all 31 records
    'in the included example db table will be captured in the recordset.
    rs.Open strSQL, cn, adOpenStatic, adLockReadOnly
    
    'PageCount simply tells us how many pages there are in our recordset object
    intPageCount = rs.PageCount
      
    'show the data
    'stops error from cmdLast
    If intPage = 0 Then
        intPage = rs.PageCount
    Else
        intPage = intPage
    End If
    
    Dim intRecord As Integer

    'move to the appropriate page of the recordset
    rs.AbsolutePage = intPage
    
    With FLX
        .Rows = .FixedRows
        lngRow = .Rows - 1

        blnOldRedraw = .Redraw
        .Redraw = False

          'The For loop will display just the number of records we specified
        For intRecord = 1 To rs.PageSize
            'add the data to the control
                        'Add the row (empty)
            .AddItem ""

            lngRow = lngRow + 1

            'set values one cell at a time
            .TextMatrix(lngRow, 0) = rs!Finance_ID.Value & ""

            .TextMatrix(lngRow, 1) = rs!Finance_Date.Value & ""

            If rs!Finance_Cheque.Value & "" = 0 Then
                .TextMatrix(lngRow, 2) = ""
            Else
                .TextMatrix(lngRow, 2) = rs!Finance_Cheque.Value & ""
            End If

            .TextMatrix(lngRow, 3) = rs!Finance_Transaction.Value & ""

            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

            .TextMatrix(lngRow, 7) = rs!Finance_ID.Value & ""


            'move to the next record within this page
          rs.MoveNext
            'if we have run out of records (which we may on the last page) exit the loop
          If rs.EOF Then Exit For
        Next intRecord

                'make sure last item in flexgrid is visible
        .TopRow = .Rows - 1

        'Re-enable screen updates (if was previously enabled)
        .Redraw = blnOldRedraw

    End With


    'enable/disable buttons as apt
    frmClubFinances.cmdPrev.Enabled = (intPage > 1)
    frmClubFinances.cmdFirst.Enabled = (intPage > 1)
    frmClubFinances.cmdNext.Enabled = (intPage < intPageCount)
    frmClubFinances.cmdLast.Enabled = (intPage < intPageCount)
    
    rs.Close
    
    strSQL = "SELECT SUM (Finance_Expenditure) as TotalEx FROM tbl_Finances"
    rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
    CurExpenditure = CCur(rs(0))
    rs.Close
    
    strSQL = "SELECT SUM (Finance_Income) as TotalIn FROM tbl_Finances"
    rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
    CurIncome = CCur(rs(0))

    rs.Close

    'add totals to textboxes
    TBox1.Text = Format(CurExpenditure, "#####0.00")
    TBox2.Text = Format(CurIncome, "#####0.00")
    TBox3.Text = Format((CurIncome - CurExpenditure), "#####0.00")
    
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

[si_the_geek]

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).

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). 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.


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).

[szlamany]

I'm curious how many rows are in the table that we are getting the last 30 of?

Are we talking 300 rows - or 300,000 rows??

I'm curious - because - in general you should limit your trips back to the database for information. The network doesn't need the traffic and the DB doesn't need the overhead.

If you are talking about 300 rows then load them all into the flexgrid and hide rows by setting the .ROWHEIGHT(.ROW)=0 - we do that a real lot (although if the number of rows in the flexgrid is a real, real lot you get some strange flashing).

[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?

[szlamany]

Ok - so you really don't want just 30 records or 20 records - you are try to display a month worth of records - right?

I'm not sure I understood that from any of the prior posts in the thread here - so please clarify.

[aikidokid]

Ok - so you really don't want just 30 records or 20 records - you are try to display a month worth of records - right?

I'm not sure I understood that from any of the prior posts in the thread here - so please clarify.

As this part of the program is going to be storing so few records, I was thinking that if the program displayed all records, that some of the first records would be quite old.
So, initially I was going to give the user the option to select, from the Options form, how many records they wanted to display at any time, e.g. 30 or 60 etc.

After si_the _geek posted his link to his Page Project, I thought I would like to impliment this into my project.
This way the user could open the form and have the most recent records displayed, but still have the option to check back through all records.

I also have a menu option where the user can select certain dates, between which the user can view records, for example, any given month. This displays the totals for the selected timescale.

I hope this does clarify what I am trying to achieve.

[szlamany]

Well - GUI design is really a matter of opinion - I'll share some of my ideas and maybe that will help you achieve your goal.

I don't feel a user has enough information to chose how many rows to return - 20 or 30 - or whatever.

The user should be presented with some drop-down/combo or initial grid of what exists for history.

They then select from that drop-down/combo/initial grid and get details of what they selected.

For example - load a drop-down of all the month/years of activity - sorted descending. So 2007/07 appears first, 2007/06 next (or July 2007 and June 2007 - however you want to display). Then select from that drop/down and you then query the database with the selection.

Since you have just drilled down to a specific time frame - the prior balances are easy to get from the query as well.

Select DateCol, Colx, Coly, Colz, SomeAmount
   ,(Select Sum(SomeAmount)
         From SomeTable Where DateCol < @StartDate) "PriorBalance"
   From SomeTable Where DateCol Between @StartDate and @EndDate

This is just an example of how this might be written.

What we personally like to do in a design is show a small grid of the "periods" - like July, 2007 and June, 2007 in first column of that grid. We make that text appear BLUE and UNDERLINED. To the right of the July, 2007 we might show some totals - dollars and recordcounts.

The user knows that they can double-click the BLUE and UNDERLINED date and have that period be selected from the database.

Another thing we often do is have a CHECKBOX that the user clicks prior to "loading" a screen. That CHECKBOX will have an option like "Show Full History" - if it's not clicked they see just the current/prior or last month (for example) - if it's clicked they see full history.

Basically - my point is that I try to get into the head of the user - what do they want when they arrive at a screen? What is the most common detail they desire to see? And then give options are which they can easily select to "open" that window of detail...

[aikidokid]

Thanks very much for the thoughts szlameny.

What we personally like to do in a design is show a small grid of the "periods" - like July, 2007 and June, 2007 in first column of that grid. We make that text appear BLUE and UNDERLINED. To the right of the July, 2007 we might show some totals - dollars and recordcounts.

Any chance of a screenshot for me to visualise what you have done here?
If not, no problem. I just understand things better when I can see what somebody is explaining.

[szlamany]

Here's a screen shot - the grid (upper right) has all the posting keys - double-click the blue/underlined posting key and that posting key is immediately called up on the screen.

Note that if you enter 2007 in the POSTING KEY field itself and click the [VIEW] button here it loads all the 2007 posting keys in a drop-down.

Clicking [VIEW] without anything in the POSTING key calls up all OPEN posting keys.

Enter 07162007 and clicking [VIEW] brings back a drop-down with 07/16/2007-1 and 07/16/2007-2.

Give the users as many logical approaches to getting the data they need to get at.