Examine directory & use results in label (97)

Topic

New Reply

I

Reply | Quote

Replies

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.

Reply | Quote

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

ID	Distributor	Folder
1	Aardvark Shippers	Server1Share1Folder1
2	Le Corsaire	Server2Share2Folder2

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.

Reply | Quote

A very interesting snippet of code that will be most useful, thanks Hans !!
Pat

Reply | Quote