VBA窗体之ListView分页显示

实现功能如下：

1、指定每页显示记录：CmbRecNum_Change 

 

 

代码如下：

Option Explicit

Dim cnn As ADODB.Connection     '声名数据库连接对象变量
Dim rst As ADODB.Recordset      '声名记录集对象变量
Dim rstDS As ADODB.Recordset    '声名记录集对象变量
Dim rsPage As Integer           '用于记录当前处于第几页

'窗体加载时，完成数据库的连接，设置显示每页的记录数
Private Sub UserForm_Initialize()
    
    Dim i As Integer
    For i = 1 To 20
        CmbRecNum.AddItem i
    Next
    CmbRecNum.ListWidth = 50
    CmbRecNum.ColumnWidths = 35
    CmbRecNum.Value = 5         '默认一页有 5 条记录
    rsPage = 1                 '默认第一页
    
    '建立数据库的连接
    Set cnn = New ADODB.Connection  '创建连接对象
    cnn_open cnn
    
    '查询表中数据生成记录集
    Dim sql As String
    sql = "select * from 员工 order by 编号 asc"
    Set rst = New ADODB.Recordset
    rst.Open sql, cnn, adOpenKeyset, adLockOptimistic
    
    '生成 ListView 控件的基本框架结构
    With ListView1
        .ColumnHeaders.Clear    '清除表头
        .ListItems.Clear        '清除记录
        .View = lvwReport       '设置显示方式为"报表形式"
        .FullRowSelect = True   '允许选中整行
        .Gridlines = True       '显示网格线
        
        For i = 0 To rst.Fields.Count - 1
            '显示标题,设置标题宽度
            Select Case True
            Case i = 0
                .ColumnHeaders.Add , , rst.Fields(i).Name, 50
            Case i = 2
                .ColumnHeaders.Add , , rst.Fields(i).Name, 100, lvwColumnCenter
            Case InStr("8,9", i)
                .ColumnHeaders.Add , , rst.Fields(i).Name, 130
            Case Else
                .ColumnHeaders.Add , , rst.Fields(i).Name, 50, lvwColumnCenter
            End Select
        Next
    End With
    AddRows rsPage
    
End Sub

'自定义过程，用于调整 ListView 控件上显示当前页的数据
Public Sub AddRows(myPage As Integer)
    On Error Resume Next
    Dim i As Integer, j As Integer
    '创建局部 Recordset 对象 rstDB ，保存 rst 记录集中当前页的记录数据
    Set rstDS = New ADODB.Recordset
    For i = 0 To rst.Fields.Count - 1
        'Append：追加字段
        rstDS.Fields.Append rst.Fields(i).Name, rst.Fields(i).Type, rst.Fields(i).DefinedSize
    Next
    rstDS.Open  '打开局部 Recordset 对象 rstDS
    
    'PageSize：表示记录集每页的记录条数
    rst.PageSize = Val(CmbRecNum.Value)     '重置 rst 每页的记录条数
    rst.AbsolutePage = myPage               '重置 rst 的当前记录页
    
    '将 rst 当前页的记录保存到 rstDS 中
    For i = 1 To rst.PageSize
        rstDS.AddNew        '添加 1 条记录
        For j = 0 To rst.Fields.Count - 1
            rstDS.Fields(j).Value = rst.Fields(j).Value
        Next
        If rst.EOF Then Exit For
        rst.MoveNext        '继续赋值
    Next
    
    '在 ListView 控件中显示当前页的记录数据
    rstDS.MoveFirst     '定位 rstDS 中的第一条记录
    With ListView1
        .ListItems.Clear
        For i = 1 To rstDS.RecordCount
            .ListItems.Add , , rstDS.Fields(0).Value     '添加第1列内容
            For j = 1 To rstDS.Fields.Count - 1
                .ListItems(i).SubItems(j) = rstDS.Fields(j).Value   '添加后续列内容
            Next
            If rstDS.EOF Then Exit For
            rstDS.MoveNext      '继续赋值
        Next
        
    End With
    
    txtPage.Value = myPage & "/" & rst.PageCount
    
End Sub

Sub cnn_open(cnn)
    With cnn
        .Provider = "microsoft.ace.oledb.12.0"
        .ConnectionString = "data source=" & ThisWorkbook.Path & "\学生管理.accdb"
        .Open
    End With
End Sub

Private Sub btnFirst_Click()
    rsPage = 1
    AddRows rsPage
End Sub

Private Sub btnBefore_Click()
    If rsPage <> 1 Then
        rsPage = rsPage - 1
        AddRows rsPage
    End If
End Sub

Private Sub btnNext_Click()
    If rsPage <> rst.PageCount Then
        rsPage = rsPage + 1
        AddRows rsPage
    End If
    
End Sub

Private Sub btnLast_Click()
    rsPage = rst.PageCount
    AddRows rsPage
End Sub

Private Sub btnClose_Click()
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    Set rstDS = Nothing
    Unload Me
End Sub

'组合框Change事件，当改变组合框的值，重新刷新窗体上的记录显示
Private Sub CmbRecNum_Change()
   rsPage = 1
   AddRows rsPage
End Sub