Printable View
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?
- Find total number of records
- Take 30 from that total
- 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.
Something like this should give you the 30 last primary keys
Can you get something like that to work??Code:Select Top 30 PriKeyCol From SomeTable Order by PriKeyCol Desc
Then you put that in a derived query:
This should return the "first" of those 30 primary key values - if you have less then 30 - it should still work.Code:Select Min(Q1.PriKeyCol) From
(Select Top 30 PriKeyCol From SomeTable Order by PriKeyCol Desc) as Q1
Then it's as simple as:
Code:Select * From SomeTable
Where PriKeyCol>=(Select Min(Q1.PriKeyCol) From
(Select Top 30 PriKeyCol From SomeTable Order by PriKeyCol Desc) as Q1)
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?
@si - oh...doing the old mind-reading are we ;)
:lol: you never know, it might just be useful!
As usual, you'd answered the actual question too well for me to add much there!
You know me - ask a question and I give a literal answer!
Thanks szlamany and si.Quote:
Originally Posted by si_the_geek
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. :D
Thanks for this example si_the_geek.Quote:
Originally Posted by si_the_geek
It was fairly easy to follow, even for me :lol:
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. :thumb:
People like myself, who are trying to teach themselves at home, would be lost without the good folk on this forum. :wave:
Thanks again.
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 :D
Code: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
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).
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).
:lol: I usually don't show enough of my code.Quote:
Originally Posted by si_the_geek
I have this part that adds the totals: (Unless I am totally missing your point - I have before ;))
I will run through this again.Code: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 didn't think I was repeatedly creating/filling the recordset!Quote:
Originally Posted by si_the_geek
Consider this changed. :DQuote:
Originally Posted by si_the_geek
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?Quote:
Originally Posted by si_the_geek
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.
Quote:
Originally Posted by szlamany
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. :D
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.
This is just an example of how this might be written.Code:Select DateCol, Colx, Coly, Colz, SomeAmount
,(Select Sum(SomeAmount)
From SomeTable Where DateCol < @StartDate) "PriorBalance"
From SomeTable Where DateCol Between @StartDate and @EndDate
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...
Thanks very much for the thoughts szlameny.
Any chance of a screenshot for me to visualise what you have done here?Quote:
Originally Posted by szlameny
If not, no problem. I just understand things better when I can see what somebody is explaining.:D
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.