Listing names of files in folder on form

Anonymous
2020-06-11T01:02:31+00:00

I am having troubles trying to list on a form all files in a folder based on criteria.

I have tried both Allen Browne's solution and Daniel Pineault's solution, and they both seem* to work for ALL files in the folder.

What I need is the list to only show file names that start with a SKU # displayed in a text field ( [txtStockCode] ) on the form the listbox is on.

So something like - \folder\Archive BOM Copies\ & Me.[txtStockCode] & " *.htm "

For starters, would there be a better way to do this other than a listbox, like a sub-form?

If not, any ideas how I can filter the listbox based on what is in [txtStockCode] ?

Ultimately I want a clickable link to be able to open these files.

*I say seem to work as the file names that are displayed only show the name until there is a comma or parentheses in the file name. The files are saved to the folder as a concatenation of SKU+Description+Date(), and description commonly has comma's and occasionally parentheses. 

Also, it doesn't show all files in the list, there are ~2500 files in the folder and growing daily and it only scrolls down a few hundred. Maybe a non-issue as I only want it to be able to show the ones that match the text field.

Any help is appreciated, thanks!

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

68 additional answers

Sort by: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2020-06-11T23:47:25+00:00

    In the Function there is this:

         sFile = Dir(sPath & "*." & sFilter)

    I replaced it with this:

        sFile = Dir(sPath & "94900745" & "*." & sFilter)

    This works, and my listbox now has (8) rows, same as the amount of files that start with "94900745" in that directory.

    But I can't get it to return anything with:

    sFile = Dir(sPath & "Me.txtStockCode" & "*." & sFilter)

    sFile = Dir(sPath & "Me.[txtStockCode]" & "*." & sFilter)

    sFile = Dir(sPath & "Me.txtStockCode" & "*." & sFilter)

    sFile = Dir(sPath & "[txtStockCode]" & "*." & sFilter)

    Sometimes it errors, sometimes the list is empty.

    I think this is where the magic happens, but my 'hocus, pocus' is not formatted correctly?!?

    Was this answer helpful?

    0 comments
  2. Anonymous
    2020-06-12T00:17:44+00:00

    Not sure what you mean by *hocus pocus*.

    You siad that is the folder where the files are located but I think you misunderstood my question.  The first name after a double slash is the SERVER name, not the FOLDER name.  So unless the SERVER is named FOLDERS your line will not work.  Make sense now?

    Was this answer helpful?

    0 comments
  3. Anonymous
    2020-06-12T00:54:21+00:00

    Yes, understood.

    And yes, the name of the server is "FOLDERS'.

    Seems an odd name, but has been in place for the 20+ years I have been aware of it?

    Trying to be funny with the Hocus-pocus.

    So it works with this as part of the Function:

         sFile = Dir(sPath & "94900745" & "*." & sFilter)

    But not if I try to replace "94900745" with something.

    I was just reading about Excel VBA (most things I found for VBA are related to Excel?), and maybe I need to do something like:

         Dim SKUNum  As String

    Then:

         sFile = Dir(sPath & SKUNum & "*." & sFilter)

    Going to give it a try...

    Was this answer helpful?

    0 comments
  4. Anonymous
    2020-06-12T01:40:12+00:00

    What is SKUNum?  And yes, you Dim if you are using a variable.  That said, under the for sFile please put...

    Debug.Print sFile

    ...and then copy and paste what shows in the Immediate Window.

    Was this answer helpful?

    0 comments