My app was done in Access 2000 and the file is still in that format but the problem I am about to describe appeared when I started using Access 2007. I get the same behavior when I rebuild enough of the app in a native 2007 file to test.

I have two list boxes. On the left is one that I populate with strings with the names of all the subfolders in a main folder specified in a text box. On the right is one that I populate with all the files in the subfolder specified by the selection in the list box on the right.

My problem occurs intermittently when the number of subfolders is large. If I open the form and specify a main folder containing few subfolders it takes an instant to populate. It takes about 2.5 seconds when I chose a main folder with over 600 subfolders. When I simply go back and forth and chose different main folders to populate the list box on the left—without selecting anything in either list box—this behavior stays the same, meaning the left list box is rapidly populated when the folder contains few subfolders and takes about 2.5 sec when there is a large number of subfolders.

But if I click on one of the subfolder names in the left list box—thus causing the right list box to get populated with the file names in that subfolder—things change. The next time I chose a main folder in the text box and cause the left list box to get populated with its subfolders, if the main folder is small there is no problem. But if that main folder is the large on with over 600 subfolders, the routine that populates the left list box—specifically the loop below just bogs down and takes minutes to finish.

lstFolders.RowSource = vbNullString
For Each folder In Subfolders
    FolderList.AddItem (folder.Name)
Next

This does not happen if I close the form and reopen it.

Can you take a snapshot of the folder and files in the background when the form is opened, and then store that data either in memory or in a local table, and then build the list box off the local table?

The downside is that the data might be out of date by a few minutes if people continue to store files after the form is opened. The upside is that there is one load, and then the list box should perform much more quickly.

I think you can also break the right hand list box into 50 records at a time, and build the next 50 as the user scrolls. I would have to look into that if you want to do that.

Does any of this help?

mmcdonal

Look it up at: http://wrox.books24x7.com

I want to try your first idea. Can you elaborate on it. I am an experienced VB6 programmer and have been programming Access a little bit, but I do not understand the solution you suggested. If you explain it more I will probably understand and I will then try it. I do not have multiple users and the list will not change so the concern you mentioned is not a worry.

Thanks!

Here is the pseudo code:

Turn off Warnings
Delete all data in a local table that holds folder and file names (Delete Query)
Turn warnings back on.

Use existing code to parse folders and file names (all in range) and write them into the local table.
   rsLocal.AddNew
   rsLocal("Folder") = code.MyFolder
   rsLocal("FileName") = code.MyFile
   rsLocal.Update

sSQL = "SELECT DISTINCT [Folder] FROM MyLocalTable"
Me.FolderList.RowSource = sSQL

Then on the FolderList On Click event:

Dim sValue As String
sValue = Me.FolderList.Value

sSQL = "SELECT [FileName] FROM MyLocalTable WHERE [Folder] = '" & sValue & "'"

Me.FileList.RowSource = sSQL

Did that help?

mmcdonal

Look it up at: http://wrox.books24x7.com

Thanks. What I got out of your directions was the idea to change the loop that adds names to the list box. Previously, my loop used the AddItem method to add each name to the list box. You gave me the idea to just put on the names in a long string and set the RowSource property to that long string. This resulted in performance very much the same as before I updated to Office 2007 and the strange problem I described appeared.

My conclusion is the AddItem method has some flaw under the hood, or I have some strange problem. But I had reinstalled Windows XP and Office 2007 and the problem did not go away.

I did not fully grasp your idea, but your directions gave me an idea that allowed me to solve the problem. Thanks!