MSHFlexGrid Paginado

[jorevale]

Gracias Geovanny.

Como el asunto me apremia porque es un tema del trabajo, he optado por buscar por internet una solución a mi problema, y la he encontrado cambiando el MSHFlexGrid por un DataGrid, aunque con ello pierdo la belleza y la funcionalidad del primero.

Por si a alguien le interesa abajo pongo el código, que ha sido encontrado en internet en un sitio en portugués que, lamentablemente, ahora no recuerdo.

Lo he adecuado un poco porque - aparte de la paginación, también necesito que los usuarios puedan ordenar por más de un campo, por eso he incluído unos botones de opción y algo más. Aun no está completamente analizado.

De todas formas, si alguien logra hacerlo con el MSHFlexGrid, os lo agradecería.

Salu2

Option Explicit
Private conn As ADODB.Connection
Private rst As ADODB.Recordset
Private subRst As ADODB.Recordset
Const PAGE_SIZE = 1000
Dim reg As Integer
Dim sql As String
Dim VPrimerClick As Boolean

Private Sub Form_Load()

VPrimerClick = False
Set conn = New ADODB.Connection

With conn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=Z:\AUTI\AST\HIST.mdb"
.Open
End With

Set subRst = New ADODB.Recordset
Set rst = New ADODB.Recordset

Command1(2).Caption = "Próximos >" & PAGE_SIZE
Command1(1).Caption = "Anteriores < " & PAGE_SIZE

sql = "SELECT AVI,NOM,FIJ FROM AVISOS order by AVI"

With rst
.CursorLocation = adUseClient
.PageSize = PAGE_SIZE
.Properties("Initial Fetch Size") = PAGE_SIZE
.Open sql, conn, adOpenKeyset, , adAsyncFetchNonBlocking
Set DataGrid1.DataSource = PaginarRecordset(rst)
End With

reg = PAGE_SIZE
lblreg.Caption = reg
End Sub

Private Sub Command1_Click(Index As Integer)

If Option1(0).Value = True Then
If VPrimerClick = True Then
sql = "SELECT AVI,NOM,FIJ FROM AVISOS order by NOM"
With rst
.Close
.CursorLocation = adUseClient
.PageSize = PAGE_SIZE
.Properties("Initial Fetch Size") = PAGE_SIZE
.Open sql, conn, adOpenKeyset, , adAsyncFetchNonBlocking
Set DataGrid1.DataSource = PaginarRecordset(rst)
End With
VPrimerClick = False
End If
Else
If Option1(1).Value = True Then
If VPrimerClick = True Then
sql = "SELECT AVI,NOM,FIJ FROM AVISOS order by NOM"
With rst
.Close
.CursorLocation = adUseClient
.PageSize = PAGE_SIZE
.Properties("Initial Fetch Size") = PAGE_SIZE
.Open sql, conn, adOpenKeyset, , adAsyncFetchNonBlocking
Set DataGrid1.DataSource = PaginarRecordset(rst)
End With
VPrimerClick = False
End If
Else
If VPrimerClick = True Then
sql = "SELECT AVI,NOM,FIJ FROM AVISOS order by FIJ"
With rst
.Close
.CursorLocation = adUseClient
.PageSize = PAGE_SIZE
.Properties("Initial Fetch Size") = PAGE_SIZE
.Open sql, conn, adOpenKeyset, , adAsyncFetchNonBlocking
Set DataGrid1.DataSource = PaginarRecordset(rst)
End With
VPrimerClick = False
End If
End If
End If

Dim novaPagina As Long

With rst
Select Case Index
Case 0
novaPagina = 1
reg = PAGE_SIZE
lblreg.Caption = reg
Case 1
novaPagina = .AbsolutePage - 1
reg = reg - PAGE_SIZE
If reg < PAGE_SIZE Then
reg = PAGE_SIZE
End If
lblreg.Caption = reg
Case 2
novaPagina = .AbsolutePage + 1
reg = reg + PAGE_SIZE
If reg > rst.RecordCount Then
reg = rst.RecordCount
End If
lblreg.Caption = reg
Case 3
novaPagina = .PageCount
reg = rst.RecordCount
lblreg.Caption = reg
End Select

If novaPagina < 1 Or novaPagina > .PageCount Then
Exit Sub
End If

.AbsolutePage = novaPagina
Set DataGrid1.DataSource = PaginarRecordset(rst)

End With
End Sub

Private Function PaginarRecordset(recset As ADODB.Recordset) As ADODB.Recordset

Dim x As Long
Dim fld As Field
Dim origPage As Long

origPage = IIf(recset.AbsolutePage > 0, recset.AbsolutePage, 1)

With subRst
If .State = adStateOpen Then .Close

For Each fld In recset.Fields
.Fields.Append fld.Name, fld.Type, fld.DefinedSize, fld.Attributes
Next fld

.Open
For x = 1 To PAGE_SIZE

If recset.EOF Then Exit For
.AddNew

For Each fld In recset.Fields
subRst(fld.Name) = fld.Value
Next fld

.Update
recset.MoveNext

Next x

.MoveFirst
recset.AbsolutePage = origPage

End With

Set PaginarRecordset = subRst

End Function

Private Sub Form_Unload(Cancel As Integer)
subRst.Close
Set subRst = Nothing
rst.Close
Set rst = Nothing
conn.Close
Set conn = Nothing
End Sub


Private Sub Option1_Click(Index As Integer)
VPrimerClick = True
End Sub