学习Excel技术,关注微信公众号:
excelperfect
本文讲述如何连接用户窗体与ADO记录集,最终创建一个与Access窗体相似的用户窗体,可以导航至前一条记录、下一条记录、第一条记录、最后一条记录,等等。
设计用户窗体
在VBE中,插入一个用户窗体,在其中添加文本框、按钮、标签等控件,如下图1所示。
图1
这个用户窗体中的控件及相应的名称如下:
用户窗体名称:ufEmployee
用户窗体标题:员工记录
在用户窗体中,从上至下,从左至右,创建下面的控件:
员工ID文本框名称:tbxEEID
Tag:Field0
姓氏文本框名称:tbxLastName
Tag:Field1
名字文本框名称:tbxFirstName
Tag:Field2
出生日期文本框名称:tbxBirthDate
Tag:Field3
雇用日期文本框名称:tbxHireDate
Tag:Field4
命令按钮名称:cmdFirst
Caption: <<
Tag:ButtonFirst
命令按钮名称:cmdPrev
Caption: <
Tag:ButtonPrev
命令按钮名称:cmdNext
Caption: >
Tag:ButtonNext
命令按钮名称:cmdLast
Caption:>>
Tag:ButtonLast
编写代码
下面,编写代码来管理这个用户窗体,即处理数据和用户窗体中的控件。
这个用户窗体需要6个事件:Initialize(当用户窗体打开时)、QueryClose(当用户窗体关闭时),以及每个命令按钮的单击事件。
需要使用事件代码将记录集的当前记录显示在文本框中、以及阻止用户错误操作,例如当处于第一条记录时单击命令按钮cmdPrev(<)。
在用户窗体模块中,声明两个模块级的变量:
Dim mADOCon As ADODB.Connection
Dim mADORs As ADODB.Recordset
使用模块级变量的原因是:当用户窗体开启时,Connection和Recordset对象在作用域内。
下面的过程使用记录集中当前记录的数据填充文本框。
Private Sub FillTextBoxes() Dim cTxtBx As Control Dim lFldNo As Long For Each cTxtBx In Me.Controls If cTxtBx.Tag Like "Field*"Then lFldNo = Mid(cTxtBx.Tag, 6) cTxtBx.Text = mADORs.Fields(lFldNo) End If Next cTxtBxEnd Sub
记得前面在设计用户窗体时给文本框设置的Tag属性吗?接下来看看我们为什么设置这个属性。
上面的程序代码遍历用户窗体中所有的控件,如果控件具有像Field0、Field1、Field2等形式的标签(tag),就从记录集中获取与标签相同名称字段的数据来填充相应的文本框。当打开用户窗体或者单击任一按钮时,需要改变文本框来响应事件。因此,Initialize事件和四个按钮中任一按钮的单击事件都将调用上面的程序。在调用上面的程序之前,这些事件将首先设置当前记录。
上述程序代码的一个好处是,你可以添加和删除文本框,而无须修改代码。前提是只要正确填写了Tag属性,就会正确填充文本框。然而,不会检查来确保字段实际存在。例如,如果设置一个Tag为Field10,但记录集仅有9个字段,那就会弄混淆。在示例代码中,为了简单起见,我们不会创建这样明确的错误检查代码。
下面的程序代码控制命令按钮(CommandButtons)。假如已经到达第一条记录,我们不想提供给用户到达第一条记录或者前一条记录的按钮选项,因为已经是第一条记录,没有更前一条记录了。同样地,当前记录是最后一条记录时,禁用最后一条记录和下一条记录按钮。每次触发一个事件时,都要确保按钮响应当前的状态。
Private Sub DisableButtons(ParamArray aBtnTags() As Variant) Dim i As Long Dim ctl As Control For Each ctl In Me.Controls ctl.Enabled = True For i = LBound(aBtnTags) To UBound(aBtnTags) If ctl.Tag = aBtnTags(i) Then ctl.Enabled = False Exit For End If Next i Next ctlEnd Sub
对于上面的程序,使用了ParamArray参数,可以发送任意数量想要禁用的按钮的名称。例如,如果想禁用第一个和前一个按钮,就可以像下面这样调用该程序:
DisableButtons “ButtonFirst”, “ButtonPrev”
除了传递给参数的按钮外,每个按钮都可用,即能被用户单击。如果不带任何参数调用该程序,那么所有的按钮都是可用的。该程序遍历用户窗体中的所有按钮,将其Tag属性与参数列表对比,如果发现匹配则禁用该控件。该程序不会区分文本框、命令按钮或任何其它类型的控件。你只需确保使用了正确的Tag属性,以免禁用了你不想禁用的控件。
现在,创建记录集,确保使用有用的信息来填充文本框。
无论用户窗体在什么时候打开都会触发Initialize事件,此时适合创建记录集。这样,无论何时用户窗体显示,我们都知道有可用的记录集存在。
Private Sub UserForm_Initialize() DimsConn As String Dim sSQL As String Dim sDbPath As String Dim sDbName As String '存储数据库的路径和名称 '可以修改为你自已的路径和名称 sDbPath ="C:\Users\Administrator\Documents\" sDbName = "Northwind" '存储连接字符串 sConn = "DSN=MS Access Database;" sConn = sConn & "DBQ=" &sDbPath & "" & sDbName & ".mdb;" sConn = sConn & "DefaultDir="& sDbPath & ";" sConn = sConn &"DriverId=281;FIL=MS Access;MaxBuffersize=2048;PageTimeout=5;" '存储SQL语句 sSQL = "SELECT 雇员.雇员ID,雇员.姓氏," sSQL = sSQL & "雇员.名字,雇员.出生日期,雇员.雇用日期 " sSQL = sSQL & "FROM " &sDbPath & "" & sDbName & ".mdb" & ".雇员" '此处要注意如果路径中有空格则需添加单引号来引用 '创建新的连接和记录集 Set mADOCon = New ADODB.Connection Set mADORs = New ADODB.Recordset mADORs.CursorLocation = adUseClient '打开连接和记录集 mADOCon.Open sConn mADORs.Open sSQL, mADOCon, adOpenDynamic '到第一条记录 mADORs.MoveFirst '调用特定目的的过程 FillTextBoxes DisableButtons "ButtonFirst","ButtonPrev"End Sub
这个程序的大部分代码是设置字符串,那些烦人的连接字符串和SQL语句是难以编写的。其中一种方法是在Excel中创建外部数据表(数据>获取外部数据),然后读取Connection和CommandText属性。接着,做一些修改,例如从连接字符串中移除ODBC以及使用变量代替数据库信息。一旦建立了外部数据表,可在立即窗口输入:
Print Sheet1.QueryTables(1).Connection
Print Sheet1.QueryTables(1).CommandText
将获得两个长字符串,可以将其复制并粘贴到代码中。也可以像上面程序中一样,将代码分成几个字符串。
当关闭用户窗体时,将触发QueryClose事件。如果用户窗体不打开,就不需要记录集,因此在该事件中要将其释放并清空内存。
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) mADORs.Close mADOCon.Close Set mADORs = Nothing Set mADOCon = NothingEnd Sub
下面,介绍如何编写命令按钮代码,使记录来回移动。
基本上有两类命令按钮,一类按钮到达记录集的两端即第一条记录和最后一条记录,一类按钮一次移动一条记录。所有的四个命令按钮都做三件事:使正确的记录为当前记录、调用FillTextBoxes过程和调用DisableButtons过程。“移动一条记录”按钮有一个额外的步骤,即决定哪条记录是当前记录。如果是第一条或最后一条记录,需要禁用不同的命令按钮。
Private Sub cmdFirst_Click() mADORs.MoveFirst FillTextBoxes DisableButtons "ButtonFirst","ButtonPrev"End Sub Private Sub cmdLast_Click() mADORs.MoveLast FillTextBoxes DisableButtons "ButtonLast","ButtonNext"End Sub Private Sub cmdNext_Click() mADORs.MoveNext FillTextBoxes If mADORs.AbsolutePosition =mADORs.RecordCount Then DisableButtons "ButtonLast","ButtonNext" Else DisableButtons End IfEnd Sub Private Sub cmdPrev_Click() mADORs.MovePrevious FillTextBoxes If mADORs.AbsolutePosition = 1 Then DisableButtons "buttonFirst","ButtonPrev" Else DisableButtons End IfEnd Sub
上述程序代码中, MoveNext方法、MoveFirst方法、MovePrevious方法和MoveLast方法都是Recordset对象的方法。
最终的效果如下图2所示。
图2
注意,如果你运行这个用户窗体出现错误,检查一个是否在VBE中设置了对Microsoft ActiveXData Objects 2.X Library的引用;或者是否Initialize事件中对数据库的引用路径和名称有误。
有兴趣研究本示例的朋友,可以在完美Excel公众号的底部发送消息:
导航记录集
下载示例工作簿。