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: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-06-16T12:00:49+00:00

    Not my question Al but if it were, I'd be using either your app or Daniels, both work well and seems to me do just what the op requires.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2020-06-16T08:16:41+00:00

    There are several ways to skin a cat, and sometimes combining information from several sources can be nice.

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

    The file that you can download uses a listbox in a popup form.

    The listbox uses as a recordsource the WordDocuments table which has a column based on a "hyperlink" data type. That's what activates all the file URLs.

    • "...Yes, that is our server name, where the files are located....

    The default folder where your files are located is seen at the bottom of the popup form with the label- "URL Address for Barcodes QR Codes".

    If you click on the clickbutton "Get New Folder", activating the Windows-like File Dialog Folder Picker, the new folder that you choose will change the default folder location at the bottom and refresh your file listings.

    Once you change the default folder, the location is noted in the SUP table, in the Handouts column.

    That way, you can close the app and when it's reopened, presto- the default folder is still the one that you last selected.

    • I want to return these filenames:
    • 94900745 TORCH, HEAD TEMP EL 20K NG 24V AC v2 BOM 01-24-2020
    • 94900745 TORCH, HEAD TEMP EL 20K NG 24V AC v2 BOM 04-20-2020
    • 94900745 TORCH, HEAD TEMP EL 20K NG 24V AC v2 BOM 05-01-2019

    Just type in something like "- 949" in the Search textbox and click on the filter- all of these files will be presented.

    • In Daniels code this is where I am putting it:

    Const sPath = "\FOLDERS\Eng_Dept\All_Staff\Engineering\Database...

    In this applet, instead of declaring a public module-level constant, the active folder is stored in the SUP table mentioned above. Unlike a constant declaration, I've used this type of table to hold many similar type of end-user changeable local and network data information in my projects.

    • You mentioned that you wanted to see only the file names...

    You can eventually list the name only and use the Application.FollowHyperlink method to activate the underlying hyperlink. We can help you get through that if you wish.

    There are several naming conventions that I'll one day change in this applet:

    1. I came up with the "WordDocuments" table name some 23 years ago. Today it usually holds pdfs, graphic images, video files, etc- not just Word documents. A better name would be tblDocuments.
    2. The "SUP" table- I don't remember what "SUP" stands for! In my office electronic medical record system I have an "SUP" and an "SUP2" for local and network data, respectively. I should one day rename them tblLocal and tblNetwork.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2020-06-16T05:49:36+00:00

    What is wrong with using Daniel's or Al's? what doesn't work for you?

    Was this answer helpful?

    0 comments
  4. Anonymous
    2020-06-16T05:12:46+00:00

    You can have a constant Path but I'm still trying to figure out why it's not returning your desired results.  Seems like it's not finding the entry in your Text Box.  Hmm, maybe you should define it as a String instead of a Variant.

    Was this answer helpful?

    0 comments