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

    Examine directory & use results in label (97)

    I

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    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.
    Charlotte

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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

    ExitHandler:
    Set fdr = Nothing
    Set fso = Nothing
    Exit Function

    ErrHandler:
    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
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 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
  •