Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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

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.

2. 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

3. 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

4. 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.

5. 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.