學習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公衆号的底部發送消息:
導航記錄集
下載下傳示例工作簿。