Avatar of Sandra Smith
Sandra Smith
Flag for United States of America asked on

Let user select directory for list of files to be displayed in ListBox on Access 2003 form

This is the first step in creating a process for the user to select a file, have ACCESS 2003 copy the selected file to a specific directory and then create the hyperlink to the copied file.  The user can have the original file in any directory, but the selected file is copied to a specific directory and then the hyperlink will be created to the copied file in the specific directory.  So, my first step is, how to I allow the user to naviage to any directory and then list all the files in this directory in a listbox?
Microsoft DevelopmentMicrosoft ApplicationsMicrosoft Access

Avatar of undefined
Last Comment
Sandra Smith

8/22/2022 - Mon
pteranodon72

<how to I allow the user to naviage to any directory and then list all the files in this directory in a listbox?>

For Office 2003, the best code for choosing a directory is listed at: http://access.mvps.org/access/api/api0002.htm

Paste the listed code into a new standard module and save it. Within the form that contains the listbox, perhaps on a button click, you can then use:

Dim strDir As String
Dim strFiles As String
Dim strTemp as String

'ask user for directory
strDir = BrowseFolder("Which folder do you want to use?")

ChDir strDir

strTemp = Dir("*.*")

While strTemp <> ""
    strFiles = strFiles & ";"
    strTemp = Dir()
Wend

'Trim extra semicolon
If Right(strFiles,1) = ";" Then
   strFiles = Left(strFiles, Len(strFiles) - 1)
End If

Me.YourListBox.RowSource = strFiles

Open in new window


Make sure the listbox's RowSourceType is set to Value list.

HTH,

Pt72
Sandra Smith

ASKER
Let me try as the only one that works right now needs to have Scripting Runtime reference set and I know the users do not have this.
Sandra Smith

ASKER
Ok, it lets me select the folder, but does not populate the listbox with the files.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Sandra Smith

ASKER
Seems I should be using AddItem?  But not sure how.
pteranodon72

The strategy of this code is to create the whole list of files and then set it as the rowsource of the listbox, rather than one-at-a-time style of .additem

Can you set a breakpoint at line 22 of the code as listed? If you hit Ctrl-G (immediate window) and type:

? strFiles


Do you get a semicolon delimited list of files?
Did you change "YourListBox" to the actual name of the listbox in your form?
(and check that RowSourceType is set to value list -- I think the default is Table/Query.

pT72

Sandra Smith

ASKER
I did set a debug.print to see what was being returned and all it gave me was ;  and I did change the name to my listbox name on the form and rowsource to value list.  Let me check it again, I am getting very tired and it could very well be that I missed something.  Be back in a bit.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
pteranodon72

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Sandra Smith

ASKER
I have attached teh code with my changes to the list box.  I also copied over the code from the link you gave me.  It just won't return the listing to hte list box.
Public Sub ListFiles()
Dim strDir As String
Dim strFiles As String
Dim strTemp As String
'Ask user for directory
strDir = BrowseFolder("Which folder do you want to use?")
ChDir strDir
strTemp = Dir("*.*")

While strTemp <> ""
    strFiles = strFiles & ";"
    strTemp = Dir()
Wend

'Trim extra semicolon
If Right(strFiles, 1) = ";" Then
   strFiles = Left(strFiles, Len(strFiles) - 1)
End If
Me.lstFiles.RowSource = strFiles
End Sub

Private Sub cmdGetDirectory_Click()
    Call ListFiles
End Sub

Open in new window

Sandra Smith

ASKER
Ok, seems to now return the files names.  But when I re-run the code by hitting the Get Files button, it does not always update the list box.  that is, it does not clear the old data and update the listbox with the new listing of files for a different directory,
Sandra Smith

ASKER
Actually, it does seem to be working so this change, plus your original version and link suggestion seems to be doing the trick.  Thank you very much.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Sandra Smith

ASKER
One last thing, it still does not always clear the previous data.  I am thinking I have teh Me.lstFiles.rowsource = "" in the wrong place, where do you suggest I put the clearing previous data code?
Sandra Smith

ASKER
I have posted another question relative to this as it does not return the correct data all the time.  Say I run the code, close teh form and reopen and run, selecting another directory does not return teh correct files, it gives me the files from the previous run of the data.  It isl ike it simply is not resetting itself but keeping values from previous runs of the code.