Databases Reference
In-Depth Information
Private Sub ComboBox_Tables_Change()
'When Table is Selected or Changed ListBoxes be Updated
'First Clear Existing Entries
Call
ClearListBoxes
'Now Repopulate with Selected Table Fields
' Database Table to Use.
strDBTable = ComboBox_Tables.Text
Call DBConnect
Call
PopulateListBox_AllData
End Sub
The subroutines that accomplish the tasks discussed are now presented in chronological order.
Although clearing a list box is not really an intellectual feat, there is a bug in Excel VBA that has
caused the author aggravation on numerous occasions. The bug is that, when utilizing the
TypeOf
statement, the 'ListBox' object is not recognized. This makes it impossible to clear all list boxes on
a form by just looking at the object type. Some sample code to demonstrate this bug has been
commented out and is presented to the readers so that they may save time and be aware of this pitfall.
Sub ClearListBoxes()
'This will clear every ListBox in the active GUI
Dim C As Control
For Each C In Controls
'VBA Bug - TypeOf 'ListBox' Not Recognized!
'Next Line of Code Will Not Work
'If TypeOf C Is ListBox And C.Name Like "*List*" Then
'Replace with:
If TypeName(C) = "ListBox" And C.Name Like "*List*" Then
'*** TEST ***
'This NEVER Triggered!
'If TypeOf C Is ListBox Then Debug.Print "I am a ListBox"
'This Triggered when Object is a ListBox!
'If TypeName(C) = "ListBox" Then Debug.Print "I am also a ListBox"
'Debug.Print TypeName(C), C.Name
'The Clear Method is not Present in Autocomplete!
C.Clear
End If
Next C
End Sub
A connection is then established to the selected database.
Sub DBConnect()
'Get default Workspace
Set oWorkSpace = DBEngine.Workspaces(0)
'Establish a DSN-less connection to the Data Base
ConnectString = "ODBC;DRIVER={Microsoft Access Driver
(*.mdb)};DBQ=" & strDB
