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-17T23:48:03+00:00

    I really don't know why you are trying to re-write Daniels's code for this. A few minor modifications are are that is required. If you want to change the names used in the code, get it working first with the names it has, change them at the end if you really have to

    So it is working exactly as expected.

    In the Function If I change this:

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

    To this:

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

    I get this:

    It is now only listing files that are named "98500178*".

    Doing what I expect it to.

    How can I replace literal 98500178 with referential [txtStockCode] on my form?

    I would think it is something simple, but I am unsuccessful.

    How would you do that?

    PS; not asking you to write the code for me, but throw me a crumb trail!

    Was this answer helpful?

    0 comments
  2. Anonymous
    2020-06-17T23:39:16+00:00

    At this point I just am not seeing why this is not working.  Can you strip the file of any personal\private information and send it to me?  If you can go here to send...

    https://www.access-diva.com/contact.php

    And please put MS Forums - KevinRXZ in the body of the eMail so I don't delete it as spam.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2020-06-17T23:35:08+00:00

    I put in DP's code as written (changing to my drive of course).

    Listbox contains numerous file names (too many to count*); works GREAT!

    Does at it is supposed to do!

    What I would like to do now, is to modify it to only files that match a textbox on the form that houses the listbox.

    For example, if the textbox has 98500178, I want the list box to only contain files named "98500178*".

    PLEASE tell me what the minor modifications are and I am forever in your debt!

    *it is lots of files, but the drive has ~2300 in one folder and growing daily files, and the list is not showing ALL files.

    I don't know if a listbox has a limit to how many rows it can display, a limit on total amount of characters, or why it doesn't show them all?

    You can see in the image that I am at the bottom of the list, and there are many more files than this in the folder.

    I don't want to get hung up on that now though, as that is something else, and maybe a non-issue as if it will do what I want, the listbox will only ever contain a few hundred at most rows, usually less than 100.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2020-06-17T22:51:03+00:00

    I really don't know why you are trying to re-write Daniels's code for this. A few minor modifications are are that is required. If you want to change the names used in the code, get it working first with the names it has, change them at the end if you really have to

    If I were you I'd start at the beginning.

    First, go to windows explorer, choose the folder you want to search in, then search on the term 123456*.htm or whatever it is you require. Does it give the required results there?

    If not, it won't work in Daniel's either.

    If it does give the required results then download a fresh copy of Daniel's demo (do not attempt to modify), select the folder, add the same search term.

    Does it list the desired files in the list box as it did in explorer?

    Was this answer helpful?

    0 comments