Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Dynamic ListBox ComboBox Contents

Introduction

How about displaying different sets of un-related values in a List Box; Values in different column layouts interchangeably? Perhaps, the changeover 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, and Bound Column 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 on how to use it with various parameter Values and VBA Code structure. All we have to do is to Copy this Code and Customize it 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 the F1 key to display the Help Document. When the Help Document is open look for the Hyperlink with the description User-defined Function and clicks 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
  1. Copy the above code into a new Standard Module in your Database and save it.
  2. Open a new Form in Design View and create a List Box on it.
  3. Click on the List Box to select it, if it is not already in the selected state.
  4. Display the Property Sheet (View - - > Properties).
  5. Insert the Function name ListMDBs in the Row Source Type Property overwriting the value Table/Query.
  6. Save the Form.
  7. Open the Form in the normal view.

A list of databases from the specified 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:


Taking Files List From Specific Folder

You may modify the following entry in the Program to take the listing of Word or Excel files from the default Folder or from a specific Folder.

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 for different requirements. After the initializing phase, the Function is called repeatedly by the System to obtain other values, like 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, it 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 the 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. In each repeated Call the required 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, for the System to identify this one from other similar processes running, and this is set with the System Timer under the following statements:

Case acLBOpen ' Open.
' Generate a 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.

Using Table Record Field Values in ListBox.

If you understood or have a general idea as to 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 mentioned at the beginning of this Article.

Second Example with Employees Table

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

  1. Import the Employees Table from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb.
  2. 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
  3. Open the Form in Design View with the List Box that we created earlier.
  4. Click on the List Box to select it.
  5. Display the Property Sheet (View- ->Properties).
  6. 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 the 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 lengths. You may try this with different values to understand them better.

  7. Create a Command Button on the Form.
  8. Ensure that the Command Button is in the selected state and display the Property Sheet.
  9. 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.
  10. Copy and Paste the following lines of code in the middle of the Event Procedure.
    Me.List40.RowSourceType = "ListMDBs"
    Me.List40.Requery
    
  11. Change the name of the List Box (the name in Bold Letters) to match the name of your own List Box.
  12. Create another Command Button below the first one.
  13. Repeat the Procedure in Step-10 and 11 for the On Click Property of the second Command Button.
  14. Copy and Paste the following code in the middle of the On Click Event Procedure:
    Me.List40.RowSourceType = "ListBoxValues"
    Me.List40.Requery
    
  15. 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.
  16. Save and Close the Form.
  17. Open the Form in Normal View.

    Since you have already inserted the ListMDBs User Defined Function in Row Source Type Property earlier the list of databases will appear in the List Box first.

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

    A sample image of the List Box with Employee List is given below:

  19. 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 run the same functions from the Row Source Type Property.

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.

Share:

No comments:

Post a Comment

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code