Update Table Based on External File

[2tMonte]

I am trying to track whether individuals have a form on file and the numbers are astronomical. I want to create databases to store the info but not hold the actual form on file. This is due to me not wanting to remove/replace the forms when they update on an annual basis. Here is what I am looking for Access to do:
1. Open an external source


2. Search for a file name that matches a record within my table
3. If a file matches, then update a check yes/no box to yes and ignore it if nothing matches

I am placing this here as a very basic example. I am certain if we can figure out how to do this on a small scale then I can grow this into what I need.




The file would be saved as a .pdf by name in a shared location or MyDocuments. For this example let's say C:\Users\Owner\Documents\Grantham\PL301 so that PL301 is filled with many Name.pdf's.

So, I want Access to open the folder in my documents and search for a match to the Name Field in my table. If it finds "Chamberlain.pdf" then update the 577 box to yes. If "Chamberlain.pdf" is not found, then leave it as no.

I know a Macro is going to have to open the data source, search for files that match text in the Name Field, update query on the 577 Field, and keep searching for every Name that has a record. Unfortunately, I am an absolute novice at writing code and queries and am totally lost in the query builder, macro builder, and the modules.

Any help would be tremendously helpful and a real leap from the labor intensive nonsense I am doing now.

Thank you.

[June7]

Not a macro, VBA code.

Review http://allenbrowne.com/func-11.html

[2tMonte]

I will have to revisit this after finishing my VBA book. I understand the concept behind what you posted but the intricacies escape me. For instance, one of the strings is FileExists("C:\Data\MyFile.mdb") but what I really need it to do is FileExists("C:\User\My Documents\MemberID.pdf")with MemberID being my primary key. Then it would have to continue to search each primary key until it reached the end of the list. I need to find a way to learn VBA because right now the modules simply confuse me.

[June7]

Concatenate the MemberID variable. This could be reference to field on form:

FileExists("C:\User\My Documents\" & Me!MemberID & ".pdf")

Then you are correct about opening recordset and using a looping structure that reads each record to get the ID's and edit the 577 field. This is getting into a bit more advanced code.

What is the external source?

[2tMonte]

If by external source you mean where the files are actually stored, it will be a shared network drive eventually. I am trying to learn the schematics with a very simplified version first and using a folder in my documents to test. In the end it would actually be \\xxxxx-xx-xx\CPTF\FMZ\03 - System Accesses\DTS\DD577s\ (x's used for security purposes).

[June7]

Then you don't really need to 'open an external source' - just read folders on shared network drive?

[2tMonte]

Yes, that's it. Why, does that simplify it?