469,338 Members | 8,179 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,338 developers. It's quick & easy.

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

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_Path_on_Network", " Records_Investments_Group = Form![Records_Investments_Group_ALL_Fields")
lstFilesInDirectory.Path = FILEPATH

Dim i As Integer
Dim TEXTCONT As String
For i = 0 To lstFilesInDirectory.ListCount - 1
TEXTCONT = TEXTCONT & lstListofRecords.List(i)
Feb 24 '11 #1
11 9657
8,800 Expert 8TB
here is the general idea for a ListBox named lstFilesInDirectory and a Text Box containing the File Path named txtFilePath:
Expand|Select|Wrap|Line Numbers
  1. Dim lst As ListBox
  2. Dim txt As TextBox
  3. Dim strFileName As String
  4. Dim strPath As String
  6. Set lst = Me![lstFilesInDirectory]
  7. Set txt = Me![txtFilePath]
  9. 'Make sure there is something in txtFilePath
  10. If IsNull(txt) Then Exit Sub
  12. 'Make sure that the PATH exists, and it is in fact a Folder
  13. If Dir$(txt, vbDirectory) = "" Then
  14.   MsgBox "The Folder " & txt & " does not exist!", vbExclamation, "Folder Not Found"
  15.     Exit Sub
  16. End If
  18. strPath = txt
  20. 'If we get here, we have a valid Folder. Add a '\' if necessary
  21. If Right$(strPath, 1) <> "\" Then strPath = strPath & "\"
  23. lst.RowSource = ""      'Clear the ListBox
  25. strFileName = Dir$(strPath, vbNormal)    ' Retrieve the first entry.
  27. lst.AddItem strFileName
  29. Do While strFileName <> ""    ' Start the loop.
  30.    strFileName = Dir    ' Get next entry.
  31.      lst.AddItem strFileName
  32. Loop
Feb 25 '11 #2
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![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

End Sub
Feb 25 '11 #3
8,800 Expert 8TB
@katia - The Row Source Type Property of the List Box must be set to Value List.
Feb 25 '11 #4
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?
Feb 25 '11 #5
8,800 Expert 8TB
@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.
    Expand|Select|Wrap|Line Numbers
    1. C:\Test
  2. Create a Directory named Stuff and dump some Test Files into it.
    Expand|Select|Wrap|Line Numbers
    1. 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.
Attached Files
File Type: zip Populate ListBox.zip (15.0 KB, 511 views)
Feb 25 '11 #6
can this also list files from sub folders?
Mar 1 '11 #7
8,800 Expert 8TB
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.
Mar 1 '11 #8
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.

Mar 1 '11 #9
8,800 Expert 8TB
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.
Mar 1 '11 #10
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.
Mar 1 '11 #11
8,800 Expert 8TB
@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.
Attached Files
File Type: zip Populate ListBox_2.zip (22.6 KB, 469 views)
Mar 1 '11 #12

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Tim Pollard | last post: by
7 posts views Thread by FP | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Purva khokhar | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.