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.