Updating records based on file location

[michaelt196]

I have a database of information that lists the file name, but not location and then I have a physical folder structure on a network drive with the fax files in it. There are folders for done, awaiting approval, or processing. I’m looking for 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

I want my code to browse those folders, and update the status of each filename based on its folder. Since the file names are unique, could I use VBA to open the folder, create an array of the files and then set the status by creating two separate functions, one for in progress, one for completed and call them one after the other?


Does anyone know of anywhere that I could gather some information to get something like that started? I'll admit that VBA isn't my strongest skill.

[twinnyfo]

Michael,

Yes, what you are asking can certainly be done in VBA. Some of your challenges may have to do with making sure that people don't change any filenames.

However, if all you are trying to do is keep a list of which files are where, you could just create a report (or spreadsheet--however you want to do it) that lists the filenames in each folder.

It just depends on what you want to do, how you want to do it, and how you want the results displayed, stored or presented.

[michaelt196]

One thing that I should have mentioned previously is that the Finished folder gets cleared out every day. Once the record in the database is updated to completed, I am no longer worried about it. I just need to be able to list everything that is in the finished folder, and update the records in the database accordingly. So I would have the file name of the fax stored in the DB (such as RF1-25_0303) with the status of processing. If that file is in the completed folder, I'd like to be able to run a macro that can automatically update it's status in the database to completed. Do you think it could be an issue that the name of the file in the database does not include the .pdf extension?

Automating this process would save me a lot of time from manually having to update the status of the faxes in the database.

Right now I'm thinking that I could use a query that only pulls faxes in processing status and then possibly compare it to the array where the file names from the folder are stored. Keep in mind that this is with a limited knowledge of VBA so I could be talking in some gibberish...

Thanks for the response!

[twinnyfo]

VBA can work very easily with file folders and lists of files--I do it all the time. I think this can be done. You should also be able to hide the ".pdf" extension when you display the file, but either way it can work.

I would still need to know how you are going to "use" the data. having everything in a table will work, but keep in mind that you will then either have to delete records from your table constantly or mark them as complete.

Again, all this depends on how you want to use your data.

[michaelt196]

By use the data, do you mean how will I use the list of files in the folder? I am just attempting to remove the manual step of me looking at the folder, looking at a fax name, and then updating the status in the database table to "finished" rather than "processing."

Sorry if I'm just making it more confusing, I appreciate your patience.

[twinnyfo]

Actually, you are not making it more confusing. Just helping to flesh out what you are trying to do.

By "using the data" I mean, once you get your list of files and have them properly classified, what do you do with that information? Is it a daily report? Is it published to anyone other than yourself? Is it just "good to know data"? Once the faxes are finished, the files deleted? Do you also intend to delete the records from the table corresponding to those files?

The reason I am asking all these questions is because I think that much (if not all) of what you want to do can be done via VBA, in which you have a list of all the files and their current status (or even separate lists of those files), all on one "dashboard" that you could manage and change the status of the files from "New" to "In Progress", which would also move the file to the designated folder. Once complete, the file itself could be deleted (or archived) and fall off your list of active files.

So, you see, there is a purpose in me asking a lot of questions, because I want to guide you toward a solution that will suit your needs.

[michaelt196]

Great, that makes much more sense.

Every day, I send out two reports to management: one containing all the faxes received from the day, and one containing all faxes that are still in processing. This keeps them updated with our volumes as well as giving them the opportunity to intervene if we are taking too long to process something. Records are never deleted from the database (we're trying to create a solid audit trail as well).

By finding an automated way to update records based on the fax being located in the finished folders will save me time on my end of day recon, as well as allow me to keep the tables updated throughout the day, perhaps to even include when a fax is awaiting authorization so I can keep the authorizers notified.

Once again, thanks for your help!

[twinnyfo]

A couple questions first, then an "approach" to this problem.

First, do your employees ever "do" anything with the faxes other than open them up, read them and close them?

Second, do they have access to your MS Access DB? The reason I ask is that there may be some utility in creating an interface for your people to be able to see the faxes, click on one, open it, do what they need to do and then move on. In theory, by opening a fax, it could automatically place it into the "In Progress" folder (thus, also updating your DB table with "who" opened it and when).

If you are the only person looking at the numbers of faxes and just creating a report, then there are a couple approaches you could have. I would rather discuss "approaches" first, then once you decide on an approach, work toward that end...

A recommended "approach":

When you open the DB, you "should" have some data already, such as the status of the faxes that are currently being worked on. The DB refreshes the data by searching the folders, finding the files, verifying that you have the same information in the DB.

Your "dashboard" has two sets of data: One for new faxes, the other for those in progress. In the New Faxes list, when you select one (or click a button corresponding to one, it moves it to the In Progress list. In the In Progress List, you can likewise classify it as Complete (which drops it from the list, but you can always access it for historical purposes).

There's a lot going on behind the scenes with this, but "in general" it is a very straightforward approach. If you have other ideas on how you want to interact with the files and folder structure, let me know and we can work through those details......

[michaelt196]

I'll go a little bit further into it just to make sure we're both on the same page:

We receive faxes through a fax program, and we have a dedicated employee that looks at these faxes, enters the record into the database (the first status they go in is "Processing") and then export the fax as a PDF to save into the processor's individual folder. Each processor has a list of tasks that they are responsible for so that’s how we decide who should receive which faxes. Once the processor has completed the fax, they stamp it (pretty much like a signature) and move it to the NEAREND folder. It may be important to note that when they stamp the PDF, they flatten it afterwards preventing the stamp from being removed which requires them to save over the PDF, almost like hitting save as and overwriting it with the same file name. At the end of the day, I go through all the faxes in the NEAREND folder, updating the corresponding records in the database to NEAREND and, when I'm done, move the faxes from NEAREND to the day’s completed folder i.e 3-4 completed faxes. Through all of this the status in the database would just change from Processing -> NEAREND.

What I'm trying to get at, is that when you mentioned new faxes vs. in progress, as soon as they are entered into the database they are already in progress from our point of view.

I have been considering splitting the database and giving each user a front end on their local computer so they can access it, but as of now the only people that use it are other managers and the people inputting faxes.

As far as the recommended approach, I have a table that contains all of the faxes we have received in total. Maybe I could have a query that lists only the ones in processing so that the DB would be able to refresh a smaller number of faxes by searching through folders?

[twinnyfo]

If possible, I would recommend giving your processors (and your dedicated employees who looks at the faxes and assigns them). The reason for this is (conceptually):

Your employee see the fax (it is a .tiff file?)--I would recommend finding a way to receive them in PDF or batch converting them to PDF (select them, right click, convert to Adobe PDF). Now, you have a bunch of "PDF Files" in the "New" folder.

1. Employee opens DB
2. Employee sees list of PDF Faxes
3. Employee clicks on file to open it, determines who should work it
4. Employee assigns the fax to an authorizer; this does several things:

   • Fax is changed to "In Progress Status" in the DB
   • Fax is physically moved to Authorizer's folder
   • Authorizer receives e-mail saying, "Get to work Joe! You have a fax!"
   • Clears the fax from the "New Fax" pane of the Employee
   • Gives the fax record (in the DB a Date/time stamp) to indicate when work was begun
   • Indicates with authorizer is working the fax

Now, your Authorizer opens the DB:

1. Sees a list of faxes ready for work (we can even have this specific to each authorizer)
2. Clicks on fax, which opens it, works it, stamps it, saves it
3. Clicks button to send fax to "Complete" section; this does several things:

   • Indicates Who authorized it and when
   • Changes Fax Status in DB to Complete
   • Clears that fax from that Authorizer's List

All this makes YOUR job really easy. Just create a report that consolidates the list of each day's faxes, their status. You could even generate statistics about how long it takes to complete the average fax, yadda, yadda, yadda.

You get to just sit back and watch every one else do their work!

;-)

[michaelt196]

You’ve actually listed quite a few things that we’ve attempted to put in place already. Right now, the employee views the fax in the fax program (The program used is RightFax which saves by .tiff as default), uses a PDF creator program that allows us to print the fax as a PDF, and saves it in the processor’s folder under the correct file name. As they’re filling out the pertinent information in the database they are given the option to alert the processor via email of the fax that has just been assigned to them. We also record what time the fax came in and what time it was assigned to someone and have another column that calculates the time taken between receiving and assigning.

The way we have it set up right now actually requires fairly minimal work of me. Our batcher (the person who assigns faxes) saves the faxes as PDFs and enters records in the database without my help. The main things I have to do are convince my authorizers to put their name in either the 1st auth/2nd auth column in the table that contains all the faxes and then figure out a way to automatically update the statuses when they are complete.

I've included the columns in the table that contains all of the faxes and also the form we use to input faxes to help visualize some of what we're talking about:

http://pbrd.co/1M76lRc
http://pbrd.co/1M76pQY

(links updated)

[twinnyfo]

I can't access your image links (I'm at work). If you put the images in a Word Doc, I can open that....

Concerning your second paragraph above, twisting peoples arms can be difficult. That is why you take away their physical access to the network folder structure and the only way they can access the faxes is via the DB. once they are stamped and resaved, the only they the fax can be "complete" is for them to forward it to the complete list. This gives them a "vested interest" in doing things the right way, as it will affect their timeliness. This also prevents them from "just moving" a product without your knowledge.....

[michaelt196]

I agree with a lot of what you're saying as far as forcing people to use the database but there is some concern coming from higher up (I'm not even a manager, just heading this project) regarding having processors mark their own faxes as completed. They seem to want management to verify that faxes have been completed/change the status in the database. That's why I have been looking for a way to automate it as that would most likely be more reliable than having it done manually, particularly if this project is going to balloon up as much as I think it will.

I've attached the doc file with the pictures, they'll probably need to be resized a bit but at least you can see them!

I should add that I received a response from a different forum regarding the automation of changing the statuses:


"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.

Code:

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"
 

Does that look like an acceptable way to accomplish what I'm looking to do?

[twinnyfo]

They seem to want management to verify that faxes have been completed/change the status in the database

I say, "No Problem!" Add them to the DB, when an authorizer has completed their work it goes to the Management who verify. There are lots of ways to slice this piece of pizza.... And, all would be automated.

Some more questions....

Is the thin skinny pic the actual table and Field Names? I ask, because from a DB Programmer's perspective, I would recommend a few slight changes:

1. I'm not sure what "Right Fax Line" means....
2. All Field Names with Spaces: Spaces in field names can cause problems if you are not careful.
3. "Batch #" should be merely "Batch" as inclusion of some special characters in Field Names can cause problems down the road.
4. Date Received and Time Received (these appear to be hand jammed?). Hand-jamming dates and times is a process that is ripe for errors. Do you want the clock to start based on when the fax was received or when the fax was entered into the system? Critical question, because let's say a fax was received on Friday after 5:00 PM, for example and everyone has gone home. It is now Monday at 9:03 AM. The fax has been sitting dormant for two and a half days, at no fault of your company or employees....
5. "Total Trades in Batch" - try to keep Field Names short, if possible--Yes, they say a field name can be 64 characters long, but there is nothing that says it has to be! If you are righting code and have to refer 75 times to a Field name that is 47 characters long, you will understand why this is good advice. "Trades" should suffice. You can label it however you want.....
6. "Time taken to batch" - again keep it short. How is this calculated? Typically, there is no need to calculate such things if you have accurate start and stop times. This can save data space.

Concerning the other Forum's response:

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.

I'm not exactly sure what they mean.

However, if you have an "automated process" there is little need to keep looking for files, as the DB will always "know" where a file is, because it put it there.

I am actually finding your project very intriguing, and an interesting challenge.

[michaelt196]

The thin skinny pic is the actual table/field names. A lot of those ideas seem great, I came into this project with very limited knowledge of Access. In reality, no one actually asked me to make a database, I just saw that they were already doing it in Excel and it seemed like a good project to take on for myself and to make it more functional and easier to use. My only concern with changing field names now is how it will affect any queries/forms/reports I have set up. Does Access automatically update those when you change a field name in a table?

As far as Date Received and Time Received go, Date Received is automatically populated by Access as today’s date on the input form and Time Received is the actual time we received the fax. Your concern with the fax sitting dormant is something that we have run into but honestly haven’t utilized the feature enough yet for it to be a big issue. It is something that I’d like to take into account, though.

The time taken to batch field works by taking the time that the record was created in access and subtracting it from the time we received the fax.

As for the other forum’s response, your guess may be as good as mine or better :P

I’ll admit that I still don’t quite understand what you mean when you say the DB will always “know” where a file is, because it put it there. Can you elaborate on that any further?

Once again, I can’t thank you enough for all of your help. It’s always great to get another set of eyes on something, particularly someone so knowledgeable.

[twinnyfo]

I’ll admit that I still don’t quite understand what you mean when you say the DB will always “know” where a file is, because it put it there. Can you elaborate on that any further?

You have files in a folder. The DB finds them, processes them, keeps track of them and moves them to where they should belong. So....

Your Front Line Employee converts all the files to PDF.

Open the DB.

DB Finds files.

Employee Opens files, determines Daffy gets this one.

Closes file, assigns to Daffy.... KA-POW! The file disappears because it has been moved to Daffy's Folder.

Daffy Opens DB.

Daffy sees he has a file.

Daffy opens file and stamps it complete.

Daffy forwards file to Mickey for further processing.... KA-POW! The file disappears because it has been moved to Mickey's folder.

Mickey opens DB.

Mickey has a file.

Mickey finalizes and saves file.

Mickey forwards to Goofy for validation... KA-POW! The file disappears, because it has been moved to Goofy's folder.

Goofy opens DB.

Goofy has a file ("Gawrsh Mickey! You sure are busy today!")

Goofy validates Daffy and Mickey's work. KA-POW! The file disappears, because it has been moved to the Archival Folder.

Make sense? All the while, the DB is keeping track of WHO has done WHAT, WHEN and WHERE the files oughta be.

Really not terribly challenging. Your challenges will be creating a User-Specific environment.... Fortunately, I just so happen to have a nice thread for you here: MS Access and User Permissions

We can work through the details. It will just take some good elbow grease on your part to make it nice and pretty.....

[michaelt196]

That actually makes perfect sense when you lay it out like that! Sounds like a good deal of work but certainly something worth doing. Any thoughts on where best to start?

[twinnyfo]

Honestly, I get a piece of paper and start sketching (very roughly) what I want things to look like. For example, what do you want the forms to look like. How do you want to "launch" the files? What is a rough idea of the layout? Stuff like that.

I also flowchart the process. At each step, make notes of what should happen (both to your files and the records in the DB). Your flowchart would start and then branch of in several directions (Front Line User, Authorizer, Validator, Administrator, etc.). What does each person do at each step? The Link to the user Permissions DB will be helpful here in the long run.

Then, you convert the pictures and flow chart into a DB Project. One step at a time will eventually get you there--as long as the boss allows you the time!