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-17T17:46:37+00:00

    SKUNum as a String is correct but you must tell the Function what SKUNum is.  So you are saying Me.txtStockCode errors.  Are you sure that is the name of the control on your Form?  Because you have to tell it SKUNum = Me.txtStockCode or you will get nothing.

    And why are you declaring the Function as a Variant?  It's a String.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2020-06-17T17:39:48+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?

    I assumed it was simple, but I don't know how to do it!

    I have tried a few different things and most do not return anything, some return everything and a few give error messages.

    This is all new to me so I apologize for not 'speaking the language' correctly.

    In my Form Open Event Sub I created a variable, and defined it:

    Private Sub Form_Open(Cancel As Integer)

    Dim aFiles()              As String

    Dim sFile                 As Variant

        Dim SKUNum                As Variant    'added this to try to get the SKU from the form

    Dim i                     As Long

    Const sPath = "\FOLDERS\Eng_Dept\All_Staff\Engineering\Database Requirements\ExportReports\Archive BOM Copies"       'Path to extract a list of files from

    On Error GoTo Error_Handler

    Me.Lst_Files.RowSource = ""

    SKUNum = Me.txtStockCode        

    I think this is right as when in error/debugger mode if I hover over SKUNum = Me.txtStockCodeit tells me it is seeing what is in the textbox:

    I assume that since the path (sPath ) is being defined in in the Sub that the textbox variable (SKUNum) does also.

    So where/how do I pass this to the Function?

    I read something about number or arguments in a Function, so I tried to change this:

        Function FF_ListFilesInDir(sPath As String, Optional sFilter As String = "*") As Variant

    To this:

    Function FF_ListFilesInDir(sPath As String, SKUNum As String, Optional sFilter As String = "*") As Variant

    And this:

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

    To this:

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

    It doesn't error, but it doesn't return anything either.

    In the immediate window it does add three rows of nothing though, compared to hundreds before I modify it.

    If you could give some clues about where/how to pass this to the Function, I would be very appreciative!

    We can talk about using the code to create a table (temporary, or overwritten every time the form/report is opened?) later, once I figure out how to get the code to work for the listbox!

    Was this answer helpful?

    0 comments
  3. Anonymous
    2020-06-17T17:00:51+00:00

    Well, with no imaged you need to give the exact verbiage of the compile error.  And just in case, we are talking about 32 bit Access right?

    Sorry, I pasted it in the message, but when I submitted it wasn't there?

    Access 2016 32-bit.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2020-06-17T00:49:36+00:00

    Well, with no imaged you need to give the exact verbiage of the compile error.  And just in case, we are talking about 32 bit Access right?

    Was this answer helpful?

    0 comments