Programming & Security

Posted: 11 Sep 2009 09:10 AM PDT

How about displaying different Set of un-related Values in a List Box; Values in different column layouts interchangeably? Perhaps, the change over can be with the click of a Button or based on some other action from the User.

When we create a List Box or Combo Box we are provided with three different options in MS-Access:Table/Query, Value List or Field List to choose from in the Row Source Type Property to fill with values in them. We normally use one of these Options to create a Combo Box or List Box and insert other Property Values, like Column Count, Column Widths, Bound Column etc. manually.

But, we can use a User Defined Function in the Row Source Type Property besides the values mentioned above to fill with Values in a List Box or Combo Box.

Even though this Function is known as a User Defined Function it is actually defined by Microsoft Access and given in the Help Documents with specific rules as how to use it with various parameter Values and VBA Code structure. All we have to do is to Copy this Code and Customize to our specific needs.

You can get the details of this Function, by placing the insertion point in the Row Source Type Property of a List Box or Combo Box Control and by pressing F1 Key to display the Help Document. When the Help Document is open look for the Hyperlink with the description User-define Function and click on it to display the details of various parameters and what they do in the Function.

We will look closely at the second example Code given in the Help Document. The VBA Code is given below and we will use it in a List Box to get a general idea of its usage.


Function ListMDBs(fld As Control, ID As Variant,  row As Variant, col As Variant,  code As Variant) As Variant
    Static dbs(127) As String, Entries As Integer
    Dim ReturnVal As Variant
    ReturnVal = Null
    Select Case code
        Case acLBInitialize                ' Initialize.
            Entries = 0
            dbs(Entries) = Dir("*.MDB")
            Do Until dbs(Entries) = "" Or Entries >= 127
                Entries = Entries + 1
                dbs(Entries) = Dir
            Loop
            ReturnVal = Entries
        Case acLBOpen                        ' Open.
            ' Generate unique ID for control.
            ReturnVal = Timer
        Case acLBGetRowCount            ' Get number of rows.
            ReturnVal = Entries
        Case acLBGetColumnCount    ' Get number of columns.
            ReturnVal = 1
        Case acLBGetColumnWidth    ' Column width.
            ' -1 forces use of default width.
            ReturnVal = -1
        Case acLBGetValue                    ' Get data.
            ReturnVal = dbs(row)
        Case acLBEnd                        ' End.
            Erase dbs
    End Select
    ListMDBs = ReturnVal
End Function

Copy the above Code into a new Standard Module in your Database and save it.

Open a new Form in Design View and create a List Box on it.

Click on the List Box to select it, if it is not already in selected state.

Display the Property Sheet (View - -> Properties).

Insert the Function name ListMDBs in the Row Source Type Property overwriting the value Table/Query.

Save the Form.

Open the Form in normal view.

A list of databases from your default directory (check Tools - -> Options - -> General Tab for your default directory location) will appear in the List Box. A sample image of a ListBox is given below:

You may modify the following entry in the Program to take listing of Word or Excel files in the List Box from different Folder like:

dbs(Entries) = Dir("*.xls")

OR

dbs(Entries) = Dir("C:\My Documents\*.xls")

The dbs(Entries) = Dir in the subsequent calls uses the first call parameter value "C:\My Documents\*.xls" by default and populates the String Array dbs() in the Initialize step of the Program.

This is one of the segments of the Code we can customize and use it for different requirements. After the initializing phase the Function is called repeatedly to obtain other values likes Rows Count, Column Count and others to define the Property Values of the List Box, which we normally set manually on the List Box in design time.

The ColumnWidths, when set with the Value -1 in the Program gives the signal to use the Default Values set manually on the Property Sheet without change. This is useful when we need a mixed format of different column sizes when more than one column of information is displayed.

Finally, the following statements pass the List Box Source Values dbs(row) that we have created under the Initialize stage for displaying in the List Box:

Case acLBGetValue ' Get data.
ReturnVal = dbs(row)

The row holds the value for the actual number of items we have loaded into the dbs() Array of 127 elements declared initially. This value is passed to the Function through the Entries Variable in the following segment of the Code:

Case acLBGetRowCount ' Get number of rows.
ReturnVal = Entries

The dbs Variable is declared as a Static Singly Dimensioned Array with 128 elements (0 to 127) to retain the value loaded into it in the Initialize stage when the User Defined Function ListMDBs is called repeatedly by MS-Access. The Parameter Values are automatically passed by MS-Access and we don't need to provide them explicitly. The first Parameter is the name of the List Box Control. The second Parameter ID is essential and this is set with the System Timer under the following statements:

Case acLBOpen ' Open.
' Generate unique ID for control.
ReturnVal = Timer

The System Timer generates new values at every millisecond interval and this ensures that a unique number will always assign to this parameter as a unique Identification Value if more than one User Defined Function is active at the same time.

The Code parameter passes appropriate values used in the Select Case statements and uses the Returned Values to define the List Box Property Values.

If you understood or have a general idea as how this function works to define the contents of a List Box or Combo Box then we can go forward with the trick that I have mentioned at the beginning of this Article.

We will create a Copy of the above Code and modify to create a List Box with Employees Code and First Name Values (two Columns of Values) from the Employees Table of Northwind.mdb database.

Import the Employees Table from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb.

Copy the following VBA Code into the Standard Module of your database and save the Module:


Function ListBoxValues(fld As Control, ID As Variant, row As Variant, col As Variant,
     code As Variant) As Variant
    Static xList(127, 0 To 1) As String, Entries As Integer
    Dim ReturnVal As Variant, k As Integer
    Dim db As Database, rst As Recordset, recCount As Integer
    
    ReturnVal = Null
    Select Case code
        Case acLBInitialize  ' Initialize.
            Set db = CurrentDb
            Set rst = db.OpenRecordset("Employees", dbOpenDynaset)
            Entries = 0
            Do Until rst.EOF
               For k = 0 To 1
                    xList(Entries, k) = rst.Fields(k).Value
               Next
               rst.MoveNext
               Entries = Entries + 1
            Loop
            rst.Close
            ReturnVal = Entries
        Case acLBOpen                    ' Open.
            ' Generate unique ID for control.
            ReturnVal = Timer
        Case acLBGetRowCount         ' Get number of rows.
            ReturnVal = Entries
        Case acLBGetColumnCount    ' Get number of columns.
            ReturnVal = 2
        Case acLBGetColumnWidth    ' Column width.
            ' -1 forces use of default width.
            ReturnVal = -1
        Case acLBGetValue                ' Get data.
            ReturnVal = xList(row, col)
        Case acLBEnd                        ' End.
            Erase xList
    End Select
    ListBoxValues = ReturnVal
End Function

Open the Form in Design View with the List Box that we have created earlier.

Click on the List Box to select it.

Display the Property Sheet (View- ->Properties).

Change the Column Widths Property Value to 0.5";1.5".

The following lines of Code say that use the values set in the Column Widths Property without change:

Case acLBGetColumnWidth ' Column width.
' -1 forces use of default width.
ReturnVal = -1

If the value in the Column Widths Property is a single value (say 1") then a multi-column List will use 1 Inch for all Columns. This may not give a nice look for values with different length. You may try this with different values to understand them better.
Create a Command Button on the Form.
Ensure that the Command Button is in selected state and display the Property Sheet.

Click on the On Click Property and select [EventProcedure] from the Drop Down control and Click on the Build (…) Button to open the VBA Module with the skeleton of the On Click Event Procedure.

Copy and Paste the following lines of Code in the middle of the Event Procedure.


Me.List40.RowSourceType = "ListMDBs"
Me.List40.Requery

Change the name of the List Box (the name in Bold Letters) to match with the name of your own List Box.

Create another Command Button below the first one.

Repeat the Procedure in Step-10 and 11 for the On Click Property of the second Command Button.

Copy and Paste the following Code in the middle of the On Click Event Procedure:


Me.List40.RowSourceType = "ListBoxValues"
Me.List40.Requery

Change the name of the List Box (the name in Bold Letters) in the Code to match with the name of your own List Box.

Save and Close the Form.

Open the Form in Normal View.

ListMDBs

Row Source Type

Click on the second Command Button to change the List Box contents to the Employees List.

List Box with Employee List in two Columns

Click on the first Command Button to change the List Box Contents back to the Database List again.

It works for Combo Boxes in the same way. You may create a Combo Box control and try in the same value.

Don't forget to set the Default Value Property with the value 1, otherwise the Combo Box may not show anything in its Text Box area before you select an item from the List.