List Files in Folder, Open from List

[Tezcatlipoca]

Hi everyone,

Hokay, so I'm starting work on a brand new project. I've written a lot of databases in the past, although the last one was years ago and I'm a little rusty now. However, the last database made extensive use of VB, so don't have any worries about firing terminology at me!

Right, my first issue is more a "can't remember how to do this" rather than a "no idea if you can", and I'd really appreciate some help.
I should say right from the start that we're looking at a Windows 2003 domain network, with every client on XP SP3 and running Office 2003.

Basically, I need to create a form within the database that contains a listbox. This list box is pointing at a certain folder in a shared location on the network which contains a huge amount of Word and Excel files.
Each one of these files is preceeded by a code, of the type XX000 which relates to the form number and department (so Marketing have files list MK001, MK002, etc.). There are about four dozen of these classifications.

Now the list box on the form should simply list all files within this folder, and allow the user to double click any of them to open them in the relevant program. And before you say "Er...why don't you just-" yes, I know I can embed a standard Windows Explorer style box in to handle it, but there are a number of issues with this, not least that the list that's generated must not only look like part of the database program, but it's population must be totally under the control of the database...

...which is where the second bit comes in. The user should be able to click a series of buttons along the bottom of the form that automatically filter the list according to department. So, they click Marketing for example, and the listbox shows only files that start with the MK code.

Does anyone know of a good place to start with this, please?

Not only that, the user should be able to

 

[DCrake]

Here is demo of what you are looking for. You may have to show hidden objects to view the form. Was created in 2003.

 

[Tezcatlipoca]

Fabulous, thank you. That's exactly what I was hunting for.

 

[Tezcatlipoca]

Ok, have taken apart the database and been adapting it for my needs and everything seems to chug along beautifully. However, three minor issues have cropped up that I've been unable to solve.
Since these minor issues both exist in the original database as well as my modified version, there's something in the original code I need to change, but don't see what.

Issue 1
Load up the example database in a folder with a few various types of file so the search function can work.
Now, it is set to TXT extensions by default. Clear this and click Search. A list of all your files appears. Great.
Now pick a filetype not in the folder (say a PPT). Click Search again. You see the problem?
When you have a populated list in the search box, then perform a search on a file extension that doesn't exist, the original list stays. What should happen is that the list clears, or, even better, returns a null value of "No Results".

Issue 2
The results list works great and shows the complete filename, including the extension. What I'd really like to do is hide the extension, so just the filename is shown (but you should, of course, still be able to filter by the extension if need be).

Issue 3
The Filter box only seems to search on the first few letters. You see, I'd really like for my users to be able to enter a keyword and the list will filter any file in the target folder that contains that word in its filename.
The problem from testing is that I only seem to be able to filter the first few letters.
For example, generate a list in a folder on your own PC and pick a lengthy filename from it at random. Now try to filter files using the first few letters in the Filter box. Works great. But now try to filter using a keyword later in the filename.

Can anyone advise please?

 

[DCrake]

This little demo was in response from a requirement from client and suited his needs exactly. Feel free to make the changes to suit your needs but in doing so if it improves the demo please resubmit to the forum for other members to take advantage of the improvements.

 

[Tezcatlipoca]

This little demo was in response from a requirement from client and suited his needs exactly. Feel free to make the changes to suit your needs but in doing so if it improves the demo please resubmit to the forum for other members to take advantage of the improvements.

Absolutely. Once I've managed to solve my three issues, I'll happily repost the edited version of the database for anyone else to take a look at

 

[DCrake]

Thanks for that, when received I will update the sample database post with acknowledgements to you.

 

[Tezcatlipoca]

I've been wrestling with some of my issues for a little while now, but can't get things to work.

Firstly, I'm trying to remove the extension being returned as part of the filename in the search box. I know this is the relevant line:

fName = Mid(varItem, FolderLength + 1)

But I can't get a Right() statement working properly in there to chop off the last four characters (the dot and the three letter extension) in the table of returned results. Removing the Mid shows the folder path too, which shouldn't be there, whislt adding a second Right() statement throws errors.
Can someone please show me where I'm going wrong?

Secondly, I'd like to be able to set the engine to search in two different folders if need be; Folder1 and Folder2. At the moment, I can make a static declaration in the find button code of where the folder should be, but how do I tweak it so I have the option of the engine search and returning results from two folders?

The main form has a list box on it called LboxFolders. I have three settings in this box; "All", "Folder1" and "Folder2".
I have rescripted simple If statements into the code which change the target directory assigned to the Folder = "<whatever>" statement depending on the listbox selection, but can't get the "All" option to work.

I know I can't - obviously - give a single variable two separate results, so I'm guessing I would have to maybe replicate the who search function and concatenate the results?

 

[DCrake]

Send me what you have so far to look at.

To drop the extension

StrNoExtension = Left(NameIncludingExt,Len(NameIncludingExt)-4)

 

[Tezcatlipoca]

Send me what you have so far to look at.

To drop the extension

StrNoExtension = Left(NameIncludingExt,Len(NameIncludingExt)-4)

Thanks for your continued help; it really is hugely appreciated.

I've attached the current build. Note that this ia very rough and ready version (it will eventually be a lot more user friendly). Also, Folder1 and Folder2 are simply test folders on my hard drive that contain a variety of filetypes. Eventually, these will be shared locations on my network. Setting any of my dropdowns works perfectly, with the exception of the folders one, which works for Folder1 or Folder2, but I can't get working for the All option.

I've also included, but commented out, your above code, as I tried to implement it, but the debug console kept throwing errors. It seems to me that the Folder + 1 section is what's throwing the error when embedded into the Left() code, but it's entirely possible I'm doing something silly!

Fortunately, I have managed to solve Issue 1 in my post above by implementing a simple Requery command that refreshes a returned null list. Now when you perform a search and there are no results you get a blank table, instead of a table populated by the results from the last search.

 

[DCrake]

Alot of improvement should be made on the combo boxes. firstly you would be better using a select case statement rarther than the if's as each if statement is examined even if the first if statement was correct.

also it may be even better to store the options in a table and use the combobox columns to pick up the options tested for in the after update event.

that's as far as I have read so far.

 

[Tezcatlipoca]

Sorry to be a further bother, but did you get the chance to look at this? I'm still struggling to make the engine able to search both folders as an option, and still can't get rid of the extension from the search results.

*** Update *** I've tried playing around with the VB code and managed to get the list to populate without the extension by using:

Sub FindAllFilesInFolder()

Dim varItem             As Variant
Dim Folder              As String
Dim objDB               As Database
Dim i                   As Integer
Dim bFlag               As Boolean
Dim StrListItems        As String
Dim FolderLength        As Integer
Dim fName               As String
Dim StrNoExtension      As String


DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * from tblFoundFiles"
DoCmd.SetWarnings True
Set objDB = CurrentDb
dblocation = Me.TxtInitDir
Me.LstFoundFiles.Requery

If Me.TxtFolders.Value = "QMS" Then
Folder = "FOLDER1"
End If
If Me.TxtFolders.Value = "RCK" Then
Folder = "FOLDER2"
End If
If Me.TxtFolders.Value = "ALL" Then
Folder = "??????????"
End If


StrListItems = "'"
With Application.FileSearch
    .NewSearch
    .FileName = Nz(Me.TxtFilter, "") & "*." & Nz(Me.TxtExtension, "") & "*"
    .LookIn = Folder
    .Execute
    DoCmd.SetWarnings False
    
    For Each varItem In .FoundFiles
        FolderLength = Len(Folder) + 1
        fName = Mid(varItem, FolderLength + 1)
        StrNoExtension = Left(fName, Len(fName) - 4)
        StrListItems = StrListItems & StrNoExtension & "','" & Folder & "','"
        DoCmd.RunSQL "INSERT INTO tblFoundFiles ( FilePath   , FileName ) SELECT '" & Folder & "\" & "' AS A, '" & StrNoExtension & "' AS B;"
        bFlag = True
    Next varItem
    DoCmd.SetWarnings True
End With
objDB.Close
Set objDB = Nothing
If bFlag = True Then
    Me.LstFoundFiles.RowSource = "QryFoundFiles"
    If Me.LstFoundFiles.ListCount > 0 Then
        Me.LstFoundFiles.Enabled = True
        Me.LstFoundFiles.Locked = False
    Else
        Me.LstFoundFiles.Enabled = False
        Me.LstFoundFiles.Locked = True
    End If
End If

End Sub

The problem is that whilst this works, I then can't double click on my list to open the file (I'm guessing because it reads the filename as the path for opening and since the filename is now missing the extension, Windows doesn't know what to do with it?).

I also still can't get it to search from both folders dictated in a dropdown (it works perfectly on one or the other, just not both).

 

[Tezcatlipoca]

Can anyone help with this?