Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Thanked 0 Times in 0 Posts

    Examine directory & use results in label (97)


  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Sacramento, California, USA
    Thanked 1 Time in 1 Post

    Re: Examine directory & use results in label (97)

    I'm sorry, but it isn't entirely clear what you want to do. Why would you do this with buttons in the first place? That means that if the number of distributors change, you have to redesign your form. You can certainly set the caption property of a button from code, but it doesn't appear that this is the best approach to your problem.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Examine directory & use results in label (97)

    As Charlotte mentioned, buttons are probably not the best way to do this - maintenance of the form will be a nightmare.

    I would suggest creating a table that holds the distributor names and corresponding folder names. Something like

    <table border=1><td>ID</td><td>Distributor</td><td>Folder</td><td>1</td><td>Aardvark Shippers</td><td>Server1Share1Folder1</td><td>2</td><td>Le Corsaire</td><td>Server2Share2Folder2</td></table>
    Adding, removing or modifying distributors is simple: edit the data in the table (or rather a form based on the table).

    You need to write code to get the count of files in a folder.
    Create a standard module.
    In Tools/References..., set a reference to the Windows Scripting Host.
    Put the following function in the module:

    Public Function FileCount(sFolder As String) As Long
    On Error GoTo ErrHandler
    Dim fso As New FileSystemObject
    Dim fdr As Folder
    Set fdr = fso.GetFolder(sFolder)
    FileCount = fdr.Files.Count

    Set fdr = Nothing
    Set fso = Nothing
    Exit Function

    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Function

    Note: as it stands, this function shows an error message if the folder doesn't exist. If you want to suppress this, handle Err = 76 separately; this is the error number that occurs when the path can't be found.

    Create a query based on your distributor table; add all fields you need plus the following calculated field:

    ShippingFiles: FileCount([Folder])

    Note: depending on the number of distributors and shipping files, this query may be slow.

    Now, you can put a list box or continuous subform on your main form with the query as row source/record source.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Melbourne, Australia
    Thanked 32 Times in 32 Posts

    Re: Examine directory & use results in label (97)

    A very interesting snippet of code that will be most useful, thanks Hans !!
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts