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