Fill a UserForm Combo Box List from a Database

The following content is from http://www.fontstuff.com…

There are different ways to create the list of items displayed by
a combo box or a list box on a VBA UserForm. One way is to "hard
code" the list into the UserForm’s Initialize event procedure
using the .AddItem method. This is fine if you know what the
contents of the list should be, and if is not going to change
regularly.

In Excel, you can set the control’s RowSource property to
a range of cells containing the list (the best way is to name the
range and enter that name as the property value). This also allows
you to change the list if you need to without having to edit the VBA
code. You can even use a dynamic range name so that you don’t have
to redefine the range each time you add a new item.

But if you are working in a program other than Excel you have to
generate the list with code. A UserForm in Word or PowerPoint
doesn’t have a range of cells it can refer to. And even if you are
working in Excel maybe you would like to get your list from
somewhere else.

This tutorial explains how to build a UserForm’s combo box or
list box list (they are both treated the same way) by retrieving the
list items from a table in an Access database.

Set a Reference to ADO

Let’s assume that you have a UserForm to which you have added a
combo box or a list box, and that you also have a database that
contains a table from which you can retrieve the list items. The
code that retrieves the information from the database uses ADO
(ActiveX Data Objects). This is a subset of the Visual Basic
programming language specifically designed for communicating with
databases. Microsoft Access, being a database program, already knows
about ADO but if you are using any other Microsoft Office program
you have to set a reference to ADO so that your program knows
how to speak to the database.

In the Visual Basic Editor go to Tools > References to
open the References dialog. In the list of Available
References
you will see that some already have a tick against
them. Unless ADO is already selected, scroll down the list and find
the entry for Microsoft ActiveX Data Objects 2.x Library
(where x is the highest available number – unless you are
programming for an earlier version of Office). Place a tick in the
adjacent checkbox and click the OK button…

If you reopen the References dialog you will see that the
ADO reference has moved to join the other selected ones near the top
of the list.

Collect the Information You Need

Since the code needs to interact with the database file it needs
to know the exact path and filename. As you will see below it uses
this to create a Connection String to open a connection to
the database. The Connection String also specifies the
appropriate driver to use. This example is appropriate for a
Microsoft Access database. If you are working with something else
(such as a database on Microsoft SQL Server) you will have to make
changes. Search for help on ADO Connection Strings to find
out what to use. Here is a typical example of a connection to an
Access database:

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
         "Data Source=C:\Databases\StaffDatabase.mdb"

After successfully connecting to the database ADO uses an SQL
statement to open a recordset which is held in the computer’s
memory. Even if your database table contains just a single field
containing each of the list items in the order you want them, you
still have to use an SQL statement to build the recordset. The SQL
statement I use in this example retrieves a unique list of
Department names from a field named Department contained in a
table called tblStaff. I have also chosen to sort the list
into ascending alphabetical order:

"SELECT DISTINCT [Department] FROM tblStaff ORDER BY
[Department];"

If you are not confident to write your own SQL statement you can
use the query tool in Access to create a query that returns the list
you need, then copy the resulting SQL from the query’s SQL View.

Write the ADO Code

The code should be placed in the UserForm’s Initialize
event procedure. This event fires each time the form is opened so
the list will always be up-to-date. If necessary, right-click the
UserForm and choose View Code to open its code module then
choose UserForm and Initialize from the drop-down
lists (left and right respectively) at the top of the code window to
create an empty procedure. The finished code, tailored to your own
requirements, should look like this:

Private Sub
UserForm_Initialize()
    On Error GoTo
UserForm_Initialize_Err
    Dim cnn As New
ADODB.Connection
    Dim rst As New
ADODB.Recordset
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=C:\Databases\StaffDatabase.mdb"
    rst.Open "SELECT DISTINCT [Department] FROM tblStaff ORDER BY
[Department];", _
             cnn, adOpenStatic
    rst.MoveFirst
    With Me.ComboBox1
        .Clear
        Do
            .AddItem rst![Department]
            rst.MoveNext
        Loop Until rst.EOF
    End With
UserForm_Initialize_Exit:
    On Error Resume Next
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set
cnn = Nothing
    Exit Sub
UserForm_Initialize_Err:
    MsgBox Err.Number & vbCrLf & Err.Description, vbCritical,
"Error!"
    Resume UserForm_Initialize_Exit
End Sub

Remember to edit the
cnn.Open
and rst.Open statements to suit your own
requirements. Change the name of the combo box or list box to match
yours (here it is called ComboBox1), and enter the name of
the field that contains the list items into the AddItem
statement. If you have coded everything correctly your UserForm will
build the list as it opens:

Note that I have included an error handler and exit routine into the
code. It is good practice to include an error handler in any
procedure during which something might go wrong. This is
particularly important when working with databases.

How the Code
Works

Following the error handling instruction and the necessary
variable declarations, the procedure starts by opening a connection
to the database. It then opens a recordset based on the supplied SQL
statement and moves to the first record. It clears any existing
items from the combo box list then proceeds to loop through the
recordset. For each record it adds a new item to the combo box list,
getting the item’s value from the specified field (here it is the
Department
field) before moving to the next record. When the
loop reaches the end of the recordset (EOF = End Of File) it
closes the recordset and the connection connection to the database
then sets their variables to Nothing to clear the computer’s
memory.

Filling a Multi-Column List

The code required to create a multi-column list is slightly
different. Remember to set the ColumnCount and
ColumnWidths
properties of your combo box or list box to the
appropriate values. You will also need to modify the SQL statement
to return as many columns as you need.

In this example two fields
(Code and Country) are brought from a table named
tblISOCountryCodes
. I have also declared a variable i to
act as a counter to keep track of them index number of each row as
it is added to the list. This listing shows how the code differs
from the previous example (the unchanged code is not shown):

Dim
i As Integer
rst.Open "SELECT [Code], [Country] FROM tblISOCOuntryCodes ORDER BY
[Country];", _
         cnn, adOpenStatic
rst.MoveFirst
i = 0
With Me.ListBox1
    .Clear
    Do
        .AddItem
        .List(i, 0) = rst![Code]
        .List(i, 1) = rst![Country]
        i = i + 1
        rst.MoveNext
    Loop Until rst.EOF
End With

As the procedure loops
through the recordset it adds a new item to the list as before, but
this time, since it has to write into several columns, it is a bit
more complicated. The .AddItem method adds a new empty row to
the list but, unlike the previous example, does not specify what it
contains. The variable i is keeping track of the index number
of each new row. The .List property of the (in this example)
list box is then set for each column. It gives the row index (i)
and the column index (0, 1, etc. numbering from zero) and the value
to be written into the list (the appropriate field from the
recordset). Before moving to the next record the value of i
is incremented by one ready for the next item.

This example has just two columns but you can have as many as you
like. Remember to set the control’s properties to accept the
additional columns, adjust the SQL statement to return the required
number of fields and add an extra .List statement for each
additional column.

 

 

by Selva V Pasupathy

Advertisements

1 Comment »

  1. […] Fill a UserForm Combo Box List from a Database […]

RSS feed for comments on this post · TrackBack URI

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: