Introduction
Sometimes, you may want to display different sets of unrelated data in the same List Box — each with its own column layout — and switch between them at will. This switch could happen when the user clicks a button or triggers another event.
Normally, when creating a List Box or Combo Box in MS Access, you choose one of three standard Row Source Type options: Table/Query, Value List, or Field List. You then set other properties such as Column Count, Column Widths, and Bound Column manually.
However, there’s another, often overlooked option — you can assign a User-Defined Function to the Row Source Type property. This function can populate the List Box or Combo Box dynamically through VBA code, allowing full control over the data and its layout.
Interestingly, while it’s called a user-defined function, this feature is actually built into Microsoft Access. The documentation provides the structure, required parameters, and basic code template. All you need to do is copy the function, adapt it, and tailor it to suit your specific requirements.
You can view the details of this function directly from the Access Help system. To do this, place the insertion point in the Row Source Type property of a List Box or Combo Box control and press F1.
When the Help window opens, look for the hyperlink titled User-defined Function and click it. This will display detailed information about the function’s parameters and how each one works.
We’ll now examine the second example provided in that Help document. The VBA code shown below demonstrates how the function can be used with a List Box, giving us a clearer understanding of its structure and behavior.
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 FunctionCopy 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.
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 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 a Specific Folder
You can modify the following line in the program to list Word or Excel files either from the default folder or from a specific folder of your choice.
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 section of the code can be customized and adapted for various purposes. After the initialization phase, the function is repeatedly called by the system to retrieve other values—such as the row count, column count, and more—that define the List Box’s property values, which are usually set manually during design time.
When the ColumnWidths property is assigned the value -1 in the program, it signals Access to retain the default settings specified manually in the Property Sheet. This feature is useful when displaying multiple columns of data with mixed column widths.
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 parameter holds the actual number of items loaded into the dbs() array, which was initially declared with 127 elements. This value is passed to the function through the Entries variable, as shown in the following segment of the code.
Case acLBGetRowCount ' Get the number of rows.
ReturnVal = Entries
The dbs variable is declared as a static, single-dimensioned array with 128 elements (indexed from 0 to 127) so that it retains its values during subsequent calls to the ListMDBs user-defined function. This persistence is important because MS Access repeatedly calls the function at different stages of the List Box population process. During each of these calls, the required parameter values are automatically passed by MS Access — you don’t need to provide them manually.
The first parameter represents the name of the List Box control. The second parameter, ID, helps the system distinguish this specific process from other similar ones that may be running. It is assigned a value based on the System Timer using the following statements:
Case acLBOpen ' Open.
' Generate a unique ID for control.
ReturnVal = Timer
The System Timer generates new values at millisecond intervals, ensuring that each user-defined function instance receives a unique identification value when multiple such functions are active simultaneously.
The Code parameter carries specific values used within the Select Case statements of the function. These values determine which property or action is being requested by MS Access, and the corresponding return values are then used to define or update the List Box property settings dynamically.
Using Table Record Field Values in a ListBox.
If you’ve understood, or at least have a general idea of, how this function defines the contents of a List Box or Combo Box, then we’re ready to move on to the interesting part — the trick 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 make a List Box that displays two columns — Employee Code and First Name — from the Employees table in the Northwind 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 FunctionOpen the Form in Design View with the List Box that we 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 to use the values set in the Column Widths property without change:
Case acLBGetColumnWidth ' Column width -1 forces the use of the default width.
ReturnVal = -1
If the Column Widths property contains only a single value (for example, 1"), then all columns in a multi-column list will automatically use that same width. This may not look visually appealing when the column values vary in length. You can experiment with different width settings to better understand how they affect the appearance of your list.
Create a Command Button on the Form.
Ensure that the Command Button is in the 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 the name of your own List Box.
Create another Command Button below the first one.
Repeat the Procedure in Steps 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 the name of your own List Box.
Save and Close the Form.
Open the Form in Normal View.
Since you have already inserted the ListMDBs User Defined Function in the Row Source Type Property earlier, the list of databases will appear in the List Box first.
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:
The Command Button click changes 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 value 1; otherwise, the Combo Box may not show anything in its Text Box area before you select an item from the list.
No comments:
Post a Comment
Comments subject to moderation before publishing.