VBA窗体之ListView分页显⽰实现功能如下：
代码如下：
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 = 1To20
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 = 0To 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 = 0To 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 = 1To rst.PageSize
rstDS.AddNew '添加 1 条记录
For j = 0To 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 = 1To rstDS.RecordCount
.ListItems.Add , , rstDS.Fields(0).Value '添加第1列内容
For j = 1To 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 <> 1Then
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
发布评论