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?
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:windows*.ini
). 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:winnt*.exe
.
To include this functionality in your own applications, follow these steps:
On a form, create a text box and a list box, with properties set as shown in Table 7-6.
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
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
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 list-filling function for the list box:
Public Function FillDirList(ByVal strFileSpec As String, avarFiles( ) _ As Variant) As Integer ' Given the file specification in strFileSpec, fill in the ' dynamic array passed in avarFiles( ). Dim intNumFiles As Integer Dim varTemp As Variant On Error GoTo HandleErr intNumFiles = 0 ' Set the filespec for the dir( ) and get the first filename. varTemp = Dir(strFileSpec) Do While Len(varTemp) > 0 intNumFiles = intNumFiles + 1 ReDim Preserve avarFiles(intNumFiles - 1) avarFiles(intNumFiles - 1) = varTemp varTemp = Dir Loop ExitHere: If intNumFiles > 0 Then acbSortArray avarFiles( ) End If FillDirList = intNumFiles Exit Function HandleErr: FillDirList = intNumFiles Resume ExitHere Resume End Function
Import basSortArray from 07-08.MDB
. This is the
same sorting code that we used in Section 7.7.2
.
The list box in this example uses a list-filling callback function, FillList, to supply its data. (See Section 7.5.2 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 avarFiles( ) As Variant Static intFileCount As Integer Select Case intCode Case acLBInitialize If Not IsNull(Me.txtFileSpec) Then intFileCount = FillDirList(Me.txtFileSpec, avarFiles( )) End If FillList = True Case acLBOpen FillList = Timer Case acLBGetRowCount FillList = intFileCount Case acLBGetValue FillList = avarFiles(lngRow) Case acLBEnd Erase avarFiles End Select End Function
In FillList’s
acLBInitialize
case, it calls the
FillDirList function to fill in the avarFiles
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, avarFiles,
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. varTemp = Dir(strFileSpec) Do While Len(varTemp) > 0 intNumFiles = intNumFiles + 1 ReDim Preserve avarFiles(intNumFiles - 1) avarFiles(intNumFiles - 1) = varTemp varTemp = 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 any 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 acbSortArray avarFiles( ) End If FillDirList = intNumFiles
FillDirList
declares its first parameter,
strFileSpec
, by value, using the
ByVal
keyword. Procedures normally declare their
parameters ByVal
if they intend to use the
parameter internally and may change its value, but they don’t
want the calling procedure to know that they’ve changed the
value. In this case, and in many procedures like this one, the
parameter might be sent directly from the value of a control. Because
Access can pass control values only by value, and never by reference,
adding the ByVal
keyword here makes it possible to
use an expression like this:
intFileCount = FillDirList(Me!txtFileSpec, avarFiles( ))
If you try this without the ByVal
keyword, Access
will complain with a “Parameter Type Mismatch” error.
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.