Counting records in folders. Please Help!!!

Jacobingram

Registered User.
Local time
Today, 09:58
Joined
Sep 15, 2004
Messages
13
I am a beginner to VBA and relatively inexperienced in access. I have spent the last month creating a quite large and intricate database with an asp frontend. I have two more things I need to do to complete it.

1) Add a field to one of my tables which tells the user how many files are in a folder (the folder being specified in another field). This must be dynamic.

2) Add a field to one of my tables which tells the user how many of the files in the folder have been added to the database. This also must keep up to date

A vague idea of the structure is this:

'Prpspect' table - contains a prospect name, the location (link) to the prospect folder and will contain these two new fields.

'Document' table - contains the document names and locations (links) along with other info.

I have been playing arund with Dcount and filecount things but I don't really know what I'n doing. If anyone could help me out in layman's terms I would be very grateful. I have a copy of Access VBA for Dummies but havent found it that useful.

Cheers
Ed
 
Ed,

I'm just winging it here (I'm on hold on the phone).

Code:
Dim FileName As String
Dim TotalFiles As Long
Dim GoodFiles As Long

Set dbs = CurrentDb

TotalFiles = 0
GoodFiles = 0

FileName = Dir("C:\SomeDir\*.*")
While FileName <> ""
   TotalFiles = TotalFiles + 1
   If Not IsNull(DLookUp("[FileName]", "YourTable", "FileName = '" & FileName & "'") Then
      GoodFiles = GoodFiles + 1
   End If
   FileName = Dir()
   Wend
MsgBox("Total Files: " & TotalFiles & ", Good Files: " & GoodFiles)

That should be a reasonably good start. Post back if you have specifics.

Wayne
 

Users who are viewing this thread

Back
Top Bottom