populate a list box with the files in a folder - filepath is in memo field on form

[katia]

Hello;
I am stuck, and would like to resolve my problem.

Am using Access 2003

• I have a form that has the file path in a memo field.
• I have a list box for the list of files in the folder for the filepath
• When I move to the record, I would like the listbox to populate with the most recent files in the folder from the file path that is on the the form
• Sometimes there is no file path, or an invalid one, so need to ignore if there is none, message if there is invalid file list text in the field

I have the following, but the DLookup won't work - maybe not good for VBA?

Need help .............


Dim FILEPATH As String
FILEPATH = DLookup("File_P ath_on_Network" , " Records_Investm ents_Group = Form![Records_Investm ents_Group_ALL_ Fields")
lstFilesInDirec tory.Path = FILEPATH


Dim i As Integer
Dim TEXTCONT As String
For i = 0 To lstFilesInDirec tory.ListCount - 1
TEXTCONT = TEXTCONT & lstListofRecord s.List(i)
Next

[ADezii]

here is the general idea for a ListBox named lstFilesInDirec tory and a Text Box containing the File Path named txtFilePath:

Dim lst As ListBox
Dim txt As TextBox
Dim strFileName As String
Dim strPath As String

Set lst = Me![lstFilesInDirectory]
Set txt = Me![txtFilePath]

'Make sure there is something in txtFilePath
If IsNull(txt) Then Exit Sub

'Make sure that the PATH exists, and it is in fact a Folder
If Dir$(txt, vbDirectory) = "" Then
  MsgBox "The Folder " & txt & " does not exist!", vbExclamation, "Folder Not Found"
    Exit Sub
End If

strPath = txt

'If we get here, we have a valid Folder. Add a '\' if necessary
If Right$(strPath, 1) <> "\" Then strPath = strPath & "\"

lst.RowSource = ""      'Clear the ListBox

strFileName = Dir$(strPath, vbNormal)    ' Retrieve the first entry.

lst.AddItem strFileName

Do While strFileName <> ""    ' Start the loop.
   strFileName = Dir    ' Get next entry.
     lst.AddItem strFileName
Loop

[katia]

It is so weird, I have tried many approaches and it does not work. Do I have to do something special with the list box properties? or is the Private Sub incorrect. The list box works if i reference a stored query in the RowSource, but does not get the info from the code.

Private Sub Form_Activate()
Dim lst As ListBox
Dim txt As TextBox
Dim strFileName As String
Dim strPath As String

Set lst = Me![lstFilesInDirec tory]
Set txt = Me![txtFilePath]

'Make sure there is something in txtFilePath
If IsNull(txt) Then Exit Sub

'Make sure that the PATH exists, and it is in fact a Folder
If dir$(txt, vbDirectory) = "" Then
MsgBox "The Folder " & txt & " does not exist!", vbExclamation, "Folder Not Found"
Exit Sub
End If

strPath = txt

'If we get here, we have a valid Folder. Add a '\' if necessary
If Right$(strPath, 1) <> "\" Then strPath = strPath & "\"

lst.RowSource = "" 'Clear the ListBox

strFileName = dir$(strPath, vbNormal) ' Retrieve the first entry.

lst.AddItem strFileName

Do While strFileName <> "" ' Start the loop.
strFileName = dir ' Get next entry.
lst.AddItem strFileName
Loop

End Sub

[ADezii]

@katia - The Row Source Type Property of the List Box must be set to Value List.

[katia]

I had the RowSource.... I put the code on a button, and it sort of works (which means user must click button to see file list) but doesn't clear when going to next record, or give a message if no files etc. Do you know what is wrong?

[ADezii]

@katia - The Code to Clear/populate must reside in the Current() Event of the Form. I'll create a simple Demo for you that will illustrate this point. Back again, katia:

1. Create a Directory named Test and dump some Test Files into it.
   

   C:\Test

2. Create a Directory named Stuff and dump some Test Files into it.
   

   C:\Stuff

3. Download the Attachment, Open the Database, Cycle through the Records, Add a New Record, etc.
4. Look closely at the Code in the Form's Current() Event.

[katia]

can this also list files from sub folders?

[ADezii]

can this also list files from sub folders?

This specific Code cannot list Files in Sub-Folders, but it can be done, although the Code will be more complicated. This is something you definitely should have mentioned up front, katia, but if you still desire this functionality, just let me know and I'll see what I can do.

[katia]

Hey ADezii, not to worry, I didn't realize it would be helpful, but can work around it. Also, took a quick look at the zip file on Thursday, and will look at it more carefully today and tomorrow. Thank you.

k

[ADezii]

If you want to include Sub-Folders, it would not be that much of a problem to implement, just let me know. The actualy Files, along with Folders should be listed to pinpoint the location of each File. You also can have the best of both worlds, namely an Option to Include, or Exclude, Sub-Folders.

[katia]

ADezii!I would like to have the option, because I don't have control of the folders after the filepath in the db and 'stuff' gets put in there (which I realized as I was running the code) - so yes, that would be great. The best of both worlds is as good as it gets! Thank you.

[ADezii]

@katia - I've adapted Allen Browne's code using Recursion, Collections, and the Dir() Function to find all Files in any Folder/Sub-Folder combination, that matches a specific File Specification (FileSpec). The Default is for the List Box to display Folders only, but selecting the Include Sub-Folders Check Box Option on the Form will display all Files in Folders and Sub-Folders for the specified PATH for all subsequent Records until deselected again. Add your own Folders/Sub-Folders PATH to the [FilePath] Field in tblFilePaths, then Navigate through the Records. Select/Deselect the Check Box to see how Sub-Folders are/are not displayed. Any questions as far as the Coding, please feel free to ask. I feel as though displaying all the Code would only confuse matters, so kindly download the Attachment, add Records to tblFilePaths, and have fun.