Microsoft Access: VBA Programming Code

Provided by Allen Browne, updated June 2006

DirListBox() function

This article describes an old technique of filling a list box via a callback function.
In Access 2000 and later, there is a newer technique that is more efficient and flexible.

To use the callback function:

1. Create a new module, by clicking the Modules tab of the Database window, and clicking New.
2. Paste in the code below.
3. Check that Access understands the code by choosing Compile on the Debug menu.
4. Save the module with a name such as Module1.
5. Set the Row Source Type property of your list box to just:
       DirListBox
   Do not use the equal sign or function brackets, and leave the Row Source property blank.

The code

Function DirListBox (fld As Control, ID, row, col, code)
    ' Purpose:    To read the contents of a directory into a ListBox.
    ' Usage:      Create a ListBox. Set its RowSourceType to "DirListBox"
    ' Parameters: The arguments are provided by Access itself.
    ' Notes:      You could read a FileSpec from an underlying form.
    '             Error handling not shown. More than 512 files not handled.
    Dim StrFileName As String
    Static StrFiles(0 To 511) As String ' Array to hold File Names
    Static IntCount As Integer          ' Number of Files in list

    Select Case code
        Case 0                          ' Initialize
            DirListBox = True

        Case 1                          ' Open: load file names into array
            DirListBox = Timer
            StrFileName = Dir$("C:\")   ' Read filespec from a form here???
            Do While Len(StrFileName) > 0
                StrFiles(IntCount) = StrFileName
                StrFileName = Dir
                IntCount = IntCount + 1
            Loop

        Case 3                          ' Rows
            DirListBox = IntCount

        Case 4                          ' Columns
            DirListBox = 1

        Case 5                          ' Column width in twips
            DirListBox = 1440

        Case 6                          ' Supply data
            DirListBox = StrFiles(row)

    End Select
End Function