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-11T23:05:55+00:00

    It is a wild card.

    The file names are a concatenation of SKU+Description+Date().

    The SKU can be found in  [txtStockCode] on the form, so I am trying return all files with names starting with [txtStockCode].

    For example, if [txtStockCode] has SKU '94900745'.

    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

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

    Full path = "\FOLDERS\Eng_Dept\All_Staff\Engineering\Database Requirements\ExportReports\Archive BOM Copies" 

    I am not understanding a place where I can call for criteria?

    Was this answer helpful?

    0 comments
  2. Anonymous
    2020-06-11T22:43:49+00:00

    So, a couple of things...

    1. What is the asterisk at the end?
    2. Is*\FOLDERS\* the Server name?  Because using the double backslash indicates you are entering the complete path using the Server name.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2020-06-11T19:47:44+00:00

    I have tried a few different variations, but keep getting ' Compile Error Type Mismatch '.

    Not sure I am putting it in the right place?

    When I try to go to Form View I get the error and it highlights in yellow this - ' Private Sub Form_Open(Cancel As Integer) '; highlights in blue this - ' sPath = '.

    In Daniels code this is where I am putting it:

    Const sPath = "\FOLDERS\Eng_Dept\All_Staff\Engineering\Database Requirements\ExportReports\Archive BOM Copies\ & [txtStockCode] & "*" 

    I am not versed in programming or VBA, and have been finding ways to make things work for me.

    My uneducated guess is since it is looking for a 'path', introducing the file name is the problem.

    Not sure where to put/ask for the criteria?

    Was this answer helpful?

    0 comments
  4. Anonymous
    2020-06-11T01:55:03+00:00

    Hmm, with Daniel's code did you try...

    FF_ListFilesInDir("\folder\Archive BOM Copies" & Me.txtStockCode & ","htm")

    Was this answer helpful?

    0 comments