First 10, Next 10, etc

[hidsugiura]

Hi,

I've looked all over for a solution to this but I'm having problem finding a GOOD solution to it.. mainly cos it's hard to Google!

I'm using a Access FE and a SQL BE... well, to be more precise, using a Azure BE.

I'm trying to see if there's an easy way to do that thing where when u get records returned, it only shows the first ten, then you have a button to show the next 10 etc. Maybe with back and forward records. Bit like what you get on forums with pages.

Reason being, I'm wanting to locate my Azure DB in a different geographical location and I want to minimise the amount of data being returned at a time.

I'd be grateful if someone could point me in the right direction for this problem!

Thanks in advance.

 

[DavidAtWork]

Have you tried using:
For 1-10
"SELECT TOP 10 * FROM table1 ORDER BY sortField"

Then for 11-20:
"SELECT TOP 10 * FROM table1 ORDER BY sortField
WHERE PKfield NOT IN (SELECT TOP 10 PKfield FROM table1 ORDER BY sortField)"

David

 

[NeutronFlux]

Have you read up on this?
http://stackoverflow.com/questions/1541212/microsoft-access-and-paging-large-datasets

Basically, create a Query that adds a column with rankings. Personally, I would create the ranking column based on primary key if it's an autonumber. Then you just need a Select statement that selects records with ranking 1-10, 11-20, etc.

Code might look something like this:

Const RecordsPerPage = 2
Dim lbRecord As Integer

Function PageRecords(lbRecord As Integer)
    Dim strSQL As String
    Dim ubRecord As Integer
    
    If FormRecordCount > lbRecord + RecordsPerPage - 1 Then
        ubRecord = lbRecord + RecordsPerPage - 1
    Else
        ubRecord = FormRecordCount
    End If
    
    strSQL = "SELECT * FROM QueryRanking WHERE Ranking BETWEEN " & lbRecord & " AND " & ubRecord
    Debug.Print strSQL
    Me.Form.RecordSource = strSQL
End Function

Function FormRecordCount() As Integer
    Dim rs As DAO.Recordset
    Dim lngCount As Long

    Set rs = CurrentDb.OpenRecordset("Table1")
    
    With rs
        If .BOF And .EOF Then
            FormRecordCount = 0
        Else
            FormRecordCount = .RecordCount
        End If
    End With

End Function

Private Sub cmdPrevious_Click()
    If lbRecord > RecordsPerPage Then
        lbRecord = lbRecord - RecordsPerPage
        PageRecords lbRecord
    End If
End Sub

Private Sub cmdNext_Click()
    If FormRecordCount > lbRecord + RecordsPerPage Then
        lbRecord = lbRecord + RecordsPerPage
        PageRecords lbRecord
    End If
End Sub

Private Sub Form_Load()

    If FormRecordCount > 0 Then
        lbRecord = 1
        PageRecords lbRecord
    End If
    
End Sub

I haven't taken into account all the bugs that could occur if some records get added or deleted, but I'll leave that to you.

 

[hidsugiura]

Yeah I've seen both those techniques... pretty similar.. both a matter of having a button or whatever with Show 1-10, 11-20, 21-30 etc.. and showing groups that way. But what happens if it returns 105 records and you've only done code for 100 records.

It'd be nicer if it were like ebay.

It tells you how many records were returned (which should be easy), then a box at the bottom which says Page 1 of xxx.. with buttons that go left and right, each right button click increasing the page number and showing the next 10 records...

I'm surprised no-one has done this before in Access. I know what the argument is.. with SQL server BE, it's so fast that it's pretty much instantaneous even if you're bringing back a lot of info.

My only problem is that I'm trying it with a SQL Azure DB on the other side of the world and when u get 50+ records being returned, there's noticeable lag.

I know what you're thinking...

1) Why are you putting it into an Asia server when u are in the UK? It's cos half my users are in China... so either it's fast for them or it's fast for us in the UK.

2) Have u thought about replication? Yes I have.. but.. I just don't like it... the massive changes I'd have to make to replace unique primary keys etc etc.

3) Don't let the user return so many results... trouble is, users will always need to know.. for example.. all sales for a certain customer in a certain season... the user won't always know whether it'll return loads of rows or 1 or 2.

Hmm tricky... thanks for the advice anyway!

 

[hidsugiura]

Ahh wait.. let me look at the extra bits you added! Thanks