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-17T00:18:46+00:00

    To use Daniels, all you need do is pass the search term and location/path variables to the function, a simple modification. The file dialog doesn't need to be used.

    You can also use the returned file list any way you want such as into a table as the record source for a report?

    Was this answer helpful?

    0 comments
  2. Anonymous
    2020-06-16T23:31:30+00:00

    So the below is not working?

    Dir(sPath & Me.txtSockCode & "*." & sFilter)

    It just seems to be missing the Me.txtStockCode.    Where are you calling this code from the Form's On_Open event?

    Just feel like something is missing because I know this works.

    When I put that in the code I get this:

    Edit: I thought I had attached the image, it was a compile error...

    Was this answer helpful?

    0 comments
  3. Anonymous
    2020-06-16T19:40:04+00:00

    So the below is not working?

    Dir(sPath & Me.txtSockCode & "*." & sFilter)

    It just seems to be missing the Me.txtStockCode.    Where are you calling this code from the Form's On_Open event?

    Just feel like something is missing because I know this works.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2020-06-16T18:58:39+00:00

    That is because those are not the parameters for Dir().  You can't include the additional fields.  (Missed that earlier.  See...

    https://www.techonthenet.com/access/functions/file/dir.php

    If the syntax is ' Dir [( path [, attributes ] ) ] ', then in my code, the path is ' sPath & "*." & sFilter', and I am not using an optional attribute?

    The path is a concatenation, right?

    How do I change the part ' & "*." & ' to be equivalent to' & "[txtStockCode]*." & ' ?

    Was this answer helpful?

    0 comments