DekGenius.com
[ Team LiB ] Previous Section Next Section

Recipe 7.8 Fill a List Box with a List of Files

7.8.1 Problem

You need to present your users with a sorted list of files with a specific filename extension in a particular directory. You found the Dir function, but you can't find a way to get this information into a list box. Is there a way to do this?

7.8.2 Solution

This problem provides the perfect opportunity to use the past three solutions. It involves creating a list-filling callback function, passing arrays as parameters, and sorting an array. In addition, you'll fill that array with a list of files matching a particular criterion, using the Dir function.

Load the form frmTestFillDirList from 07-08.MDB. Enter a file specification into the text box (for example, c:\*.exe). Once you leave the text box (by pressing either Tab or Return), the code attached to the AfterUpdate event will force the list box to requery. When that happens, the list box will fill in with the matching filenames. Figure 7-12 shows the results of a search for c:\\*.*.

Figure 7-12. frmTestFillDirList, searching for *.* in the C:\ folder
figs/acb2_0712.gif

To include this functionality in your own applications, follow these steps:

  1. On a form, create a text box and a list box, with properties set as shown in Table 7-6.

Table 7-6. Property settings for the controls on the directory list form

Control

Property

Setting

Text box

Name

txtFileSpec

 

AfterUpdate

[Event Procedure]

List box

Name

lstDirList

 

RowSourceType

FillList

 

AfterUpdate

[Event Procedure]

  1. Enter the following code in the text box's AfterUpdate event procedure. (See the Preface for more information on creating event procedures.) This code forces the list box to requery itself when you enter a value in the text box, and then move to some other control:

    Sub txtFileSpec_AfterUpdate ( )
       Me.lstDirList.Requery
    End Sub
  2. Enter the following code in the list box's AfterUpdate event. This is sample code that pops up a message box indicating which file you chose:

    Sub lstDirList_AfterUpdate ( )
       MsgBox "You chose: " & Me.lstDirList.Value
    End Sub
  3. Enter the following code into a global module so that it can be called from any form. Though this code would work fine in a form's module, it's general enough that it will serve you best as part of a global module that can be copied from one database to another. This is the function that fills the array of files:

    Public Function FillDirList(ByVal strFileSpec As String, _
     astrFiles( ) As String) As Integer
    
        ' Given the file specification in strFileSpec, fill in the
        ' dynamic array passed in avarFiles( ).
        
        Dim intNumFiles As Integer
        Dim strTemp As String
    
        On Error GoTo HandleErr
        intNumFiles = 0
    
        ' Set the filespec for the dir( ) and get the first filename.
        strTemp = Dir(strFileSpec)
        Do While Len(strTemp) > 0
            intNumFiles = intNumFiles + 1
            astrFiles(intNumFiles - 1) = strTemp
            strTemp = Dir
        Loop
    
    ExitHere:
        If intNumFiles > 0 Then
            ReDim Preserve astrFiles(intNumFiles - 1)
            acbSortArray astrFiles( )
        End If
        FillDirList = intNumFiles
        Exit Function
        
    HandleErr:
        Select Case Err.Number
            Case 9
                ' The array needs to be resized
                ' Just add room for 100 more files.
                ReDim Preserve astrFiles(intNumFiles + 100)
                Resume
            Case Else
                FillDirList = intNumFiles
                Resume ExitHere
        End Select
    End Function

Rather than resizing the array for each matching file name, the FillDirList function traps the error that occurs when the array is full, and resizes it 100 slots at a time. Using the Redim Preserve statement is quite expensive in VBA, and you should consider looking for ways to call it as seldom as possible. In this example, the code resizes the array to the correct size once it's done filling in all the file names.


  1. Import basSortArray from 07-08.MDB. This is the same sorting code that we used in the Solution in Recipe 7.7.

7.8.3 Discussion

The list box in this example uses a list-filling callback function, FillList, to supply its data. (See the Solution in Recipe 7.5 for information on callback functions.) Here's the code:

Private Function FillList(ctl As Control, _
 varID As Variant, lngRow As Long, lngCol As Long, _
 intCode As Integer)
    Static astrFiles( ) As String
    Static intFileCount As Integer

    Select Case intCode
        Case acLBInitialize
            If Not IsNull(Me.txtFileSpec) Then
                intFileCount = FillDirList(Me.txtFileSpec, astrFiles( ))
            End If
            FillList = True

        Case acLBOpen
            FillList = Timer

        Case acLBGetRowCount
            FillList = intFileCount

        Case acLBGetValue
            FillList = astrFiles(lngRow)

        Case acLBEnd
            Erase astrFiles
    End Select
End Function

In FillList's acLBInitialize case, it calls the FillDirList function to fill in the astrFiles array, based on the value in the txtFileSpec text box. FillDirList fills in the array, calling acbSortArray along the way to sort the list of files, and returns the number of files it found. Given that completed array, FillList can return the value from the array that it needs when requested in the acLBGetValue case. It uses the return value from FillDirList, the number of files found, in response to the acLBGetRowCount case.

There's also an interesting situation you should note in the FillList and FillDirList routines. FillList declares a dynamic array, astrFiles, but doesn't give a size because it doesn't yet know the number of files that will be found. FillList passes the array off to FillDirList, which adds filenames to the array based on the file specification until it doesn't find any more matches. FillDirList returns the number of matching filenames, but it also has the side effect of having set the array's size and filled it in. Here's the code that does the work. This code fragment uses the ReDim Preserve keywords to resize the array every time it finds a matching filename:

' Set the filespec for the dir( ) and get the first filename.
strTemp = Dir(strFileSpec)
Do While Len(strTemp) > 0
    intNumFiles = intNumFiles + 1
    astrFiles(intNumFiles - 1) = strTemp
    strTemp = Dir
Loop

FillDirList uses the Dir function to create the list of files. This function is unusual in that you call it multiple times. The first time you call it, you send it the file specification you're trying to match, and Dir returns the first matching filename. If it returns a nonempty value, you continue to call it, with no parameters, until it does return an empty value. Each time you call Dir, it returns the next matching filename.

Once FillDirList has finished retrieving the list of filenames, it sorts the names in the array. Its return value is the number of files it found. The following code shows how this works:

If intNumFiles > 0 Then
    ReDim Preserve astrFiles(intNumFiles - 1)
    acbSortArray astrFiles( )
End If
FillDirList = intNumFiles

Note that when Access calls the list-filling callback function, values for the lngRow and lngCol parameters are always zero-based. Therefore, when you use arrays within callback functions, you should always consider using zero-based arrays to hold the data you'll display in the control. If you don't, you'll always be dealing with "off by one" errors. Using a zero-based array will mean that the row values (sent to your code in lngRow) will match your array indices.

    [ Team LiB ] Previous Section Next Section