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-16T17:58:31+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

    Was this answer helpful?

    0 comments
  2. Anonymous
    2020-06-16T17:09:53+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.

    In my Sub I changed:

        Dim SKUNum                As Variant

    To:

        Dim SKUNum                As String

    In my Function I changed:

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

    To:

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

    The listbox is still all files in the folder.

    When changing , this tip appears.

    Am I trying to add SKUNum in the wrong place?

    Was this answer helpful?

    0 comments
  3. Anonymous
    2020-06-16T16:44:18+00:00

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

    They both work as designed, but I prefer Daniels version as it only displays file names.

    My files are always in the same folder, so I don't need to see the path.

    Here is what I am trying to do.

    The listbox is on a form that when opened is for a specific SKU for a 'Finished Goods' product.

    I have a folder with .htm files that are archive copies of Finished Goods BOM's.

    The files are saved to this folder as a concatenation of the 'SKU & Description & Date() & .htm'.

    I want the form to open with a list of all files in the archive folder, that match the SKU #.

    It is a listbox as that is the only thing I could find when searching, but I think I would prefer just a listing (via a sub-form?).

    But whatever works!

    It is currently a form, but it is only to give links to files, so I could change it to a report (with sub-report?).

    Was this answer helpful?

    0 comments
  4. Anonymous
    2020-06-16T15:43:39+00:00

    Daniel's website is incredible! It goes back at least 15 years...

    Was this answer helpful?

    0 comments