Getting file names in a directory

Getting file names in a directory

Normally, when you want the user to select a file name to be used by the database, you use the Windows File Open/Save dialog box. However, if you are storing this file name in the database (e.g. for images), and you already have several hundred file names stored, it can be difficult to see the wheat from the chaff. In this sort of situation, I prefer to use a combo box.

Create a temporary table ("ztblFile" in this instance), and create one field, "TempFileName" of datatype text. In the form that is bound to the main table, tblFile , add a combo box, cboFileName, and set the ControlSource to FileName from tblFile.

Then, in the GotFocus event for this combo box, you will need some code to read in all the files of a certain type, in this case bitmaps (*.bmp), and only display those that aren't already present in tblFile. Note that you must end the path to the folder in a "\", i.e. as in "c:\" or "c:\samples\test\images\" for this to work properly.

Private Sub cboNewFileNames_GotFocus()
    Dim db As Database, rs As Recordset
    Dim strPath As String, strName As String, strSQL As String
    Set db = DBEngine(0)(0)
'   Delete existing file names
    db.Execute "DELETE * FROM [ztblFile];"
    Set rs = db.OpenRecordset("ztblFile")
    strPath = "c:\"
    strName = Dir(strPath, vbNormal)
'   Loop through the directory, and add all files with the right extenstion to the recordset
    Do While strName <> ""
        If Right(strName, 4) =".bmp" Then
            With rs
                .AddNew
                !TempFileName = strName
                .Update
            End With
        End If
        strName = Dir
    Loop
    rs.Close
    Set rs = Nothing
    Set db = Nothing
'   Now select file names that only appear in the temporary list
    strSQL = "SELECT DISTINCTROW [TempFileName]" _
    & " FROM ztblFile LEFT JOIN tblFile ON [ztblFile].[TempFileName] = [tblFile].[FilePath]" _
    & " WHERE ([tblFile].[FilePath] Is Null);"
'   Set the RowSource of the ComboBox, and Requery
    Me!cboNewFileNames.RowSource = strSQL
    Me!cboNewFileNames.Requery
End Sub

Now, whenever you come to select a file, you will only see those files that haven't already been used.

Top

Last modified at 06/06/2006 13:54:29