Results 1 to 5 of 5

Thread: [RESOLVED] Need help : Page showing row

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    May 2013
    Posts
    328

    Resolved [RESOLVED] Need help : Page showing row

    I am trying to show 5 records on each page.

    I need to display this status : "Showing row 1 - 5" I got this part.
    But moving to 2nd page status doesn't moving, the result must be "Showing row 5 - 10"

    My Codes:

    Code:
    Private Sub Populate_Employee(lPage As Long, Optional Start_From = 0)
    
    Dim rs As ADODB.Recordset
    Dim sqlCriteria As String
    Dim lRow, i As Long
    
    Dim lPageCount As Long
    Dim nPageSize As Integer
    Dim lCount As Long
    Dim T As Single
    Dim rPosition As String
    
    Dim nCOMBINE As String
        
        T = Timer   ' Calculate time of execution
        nPageSize = 5  ' Max Records per Page
        
        sqlCriteria = "SELECT sNUMBER,eLAST,eFIRST FROM sql_EMPLOYEE ORDER BY eLast, eFirst Asc"
        
        Set rs = New ADODB.Recordset
            rs.CursorLocation = adUseClient
            rs.CursorType = adOpenForwardOnly
            rs.LockType = adLockReadOnly
            
            rs.Open sqlCriteria, cn
    
            rs.PageSize = nPageSize
            lPageCount = rs.PageCount
            If lCurrentPage > lPageCount Then
                lCurrentPage = lPageCount
            End If
            
            lblPerPage.Caption = nPageSize
            cboPage.Text = lPage
            lblPages.Caption = lPageCount
            
            rs.AbsolutePage = lCurrentPage
    
            With cboPage
                .Clear
                For i = 1 To lPageCount
                    .AddItem i
                Next i
                    .Text = lPage
            End With
            
            ' ==============================================================='
                With grid_Employee
                    .Redraw = False
                    .Clear
                        lCount = 0
                    
                    Do Until rs.EOF
                        
                        lRow = .AddItem(rs!sNumber)
                            .CellText(lRow, 1) = rs!eLast
                        
                        lCount = lCount + 1
                        If lCount = nPageSize Then
                            Exit Do
                        End If
                        
                    rs.MoveNext
                    Loop
                    .Redraw = True
                End With
                
            ' ==============================================================='
            
            If rs.RecordCount > 0 Then
                rs.Move Start_From * 5, 2
            End If
            
            
            If rs.RecordCount > 0 Then
                rPosition = (Start_From * 5) + 1 & " - "
                If (Start_From * 5) + 1 + 5 >= rs.RecordCount Then
                    rPosition = rPosition & rs.RecordCount
                Else
                    rPosition = rPosition & (Start_From * 5) + 5
                End If
            Else
               rPosition = "0"
            End If
            
            ' ==============================================================='
            
            lblStatus.Caption = "Showing rows " & rPosition & " ( " & rs.RecordCount & " total, Query took " & Timer - T & " sec)"
            
            rs.Close
        Set rs = Nothing
    
    End Sub
    What am I doing wrong?

    Any help guys?

  2. #2

    Thread Starter
    Hyperactive Member
    Join Date
    May 2013
    Posts
    328

    Re: Need help : Page showing row

    Name:  Paging.jpg
Views: 117
Size:  25.3 KB

  3. #3
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Need help : Page showing row

    Since you are using MySQL, you should try to apply its LIMIT statement already at the SQL-Level.
    e.g. "SELECT * FROM tbl LIMIT 5,10" '<- Retrieve rows 6-15

    Olaf

  4. #4
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Need help : Page showing row

    Where are you assigning a value to Start_From ?

    That would appear to be at the root of your problem

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    May 2013
    Posts
    328

    Re: Need help : Page showing row

    I got it.

    If lPage = 1 Then

    rPosition = "1 - " & lCount

    ElseIf (lPage * nPageSize - 20) + 1 >= rs.RecordCount Then

    rPosition = (lPage * nPageSize - 20) + 1 & " - " _
    & rs.RecordCount

    ElseIf lCount < rs.RecordCount Then

    rPosition = (lPage * nPageSize - 20) + 1 & " - " _
    & (lPage * nPageSize - 20) + nPageSize

    End If

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width