Updating records based on file location

[michaelt196]

Hello,


I have created a database that tracks the locations of all the faxes we receive in a day. We have a dedicated employee that enters them into the database as well as who they are assigned to and other pertinent information. At the end of the day, I monitor the network folders where the faxes are saved to change the status of the fax in the database to done, still needs processing, or awaiting approval. Is there any way that I could set up some VBA that would automatically update the status column for the record based on the location of the file? The file name is recorded in the database so then if it sees that that file is in the end folder it could automatically update the status to finished.


The response I got on another forum is as follows:



The impression I'm getting is that you've got a database of information over here.
And it lists the file name, but not location.
And then you have a physical folder structure with the fax files in it.
And there are folders for done, awaiting approval, or processing.
So what you're looking for is something to read those folders and update the status column based on which folder it is in?
So at the start of the day we have:
New
-Fax1.tiff
-Fax2.tiff
-Fax3.tiff
-Fax4.tiff
Finished
-
In progress
-
And at the end of the day the folders look like this:
New
-Fax1.tiff
Finished
-Fax3.tiff
-Fax4.tiff
In progress
-Fax2.tiff
And you want your code to browse those folders, and update the status of each filename based on it's folder?
If the filenames are unique, you can just use VBA to open the folder, create an array of the files and then set the status.
Create two separate functions, one for in progress, one for completed and call them one after the other.
There'd be no need to check the current status, but you could store the wrong status if a file is duplicated, or a filename repeated.


Does anyone know of anywhere that I could gather some information to get that started? I'll admit that VBA isn't my strongest skill. All of the file names of the faxes are unique so I assume I could just have a query that pulls everything that is still in processing, then I could run the VBA code to update those records?


Thanks

 

[Mile-O]

to done, still needs processing, or awaiting approval

First, what are your rules on how this is determined?

I see someone's understanding being that you have three folders. However, no confirmation from yourself as to whether this person's understanding is correct or not.

Either way, you're going to need the FileSystemObject. (That's a googleable term.

 

[michaelt196]

Sorry for the confusion with that, the files are manually moved into a finished folder once they are authorized or completed. Items that are still in processing remain in the processors folder and items that are awaiting authorization are in the authorization folder. When the record for each fax is initially input, the file is saved to the processor's folder and put in processing status. So at the end of the day I should only need to update which files have moved to the finished/waiting for authorization folders.

 

[Solo712]

Sorry for the confusion with that, the files are manually moved into a finished folder once they are authorized or completed. Items that are still in processing remain in the processors folder and items that are awaiting authorization are in the authorization folder. When the record for each fax is initially input, the file is saved to the processor's folder and put in processing status. So at the end of the day I should only need to update which files have moved to the finished/waiting for authorization folders.

You need a routine that checks each record (you can further restrict the search to the day's records if you want ) in the fax table and then test if the fax descrpitor is part of a filename in a particular folder. If it is, the routine changes the status of the record. You can make this an On_click event for a button. Change names of paths, table and fields as necessary to match your db.

Private Sub Status()
  Dim dbs As DAO.Database, rst As DAO.Recordset, fstatus As String
 
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("Faxes", dbOpenDynaset)
 
  If Not rst.EOF Then
     rst.MoveFirst
     Do While Not rst.EOF
         fstatus = ""
         If Dir("N:\mypath\InProgress\" & rst!FaxDesc & ".tiff") <> 0 Then
             fstatus = "In Progress"
         ElseIf Dir("N:\mypath\Finished\" & rst!FaxDesc & ".tiff") <> 0 Then
             fstatus = "Finished"
         End If
 
         If fstatus <> "" Then
            rst.Edit
            rst!Status = fstatus
            rst.Update
         End If
         rst.MoveNext
     Loop
  End If
 
   rst.Close
   Set rst = Nothing
   dbs.Close
   Set dbs = Nothing
End Sub

Should do the trick. Good luck.

Best,
Jiri