If file exists update boolean field

Hey all you Access geniuses - hope you can help me. I have a table with 12000 records. Each record has a hyperlink field with a path to a pdf file. But, although a path is set up for each record, there isn't actually a pdf file in existence for every single record. In the same table I have a yes/no field that I would like to indicate whether there is a pdf file in existence or not for that record. Can someone help me write the code in order to automate this update? I tried writing a query to do this, but I wasn't able to get the query to look to see if the pdf file exists before it updated the yes/no field.

The table where these fields are located is called: INVENTORY CERTS. The field that contains the hyperlinked paths is called: CERT PATH. The yes/no field is called: PDF AVAILABLE.

I would like the program to look to see if the file per the path in the hyperlink field CERT PATH exists, if it does exists, update the field PDF AVAILABLE to yes ("-1"). If it doesn't exists, update the field PDF AVAILABLE to no ("0"). I would want it to do this for each record.

I would like to be able to run this code whenever I need to -- when I know new pdf files have been added. Maybe even attach it to a button in my maintenance menu.

Thanks in advance for any help you can give me!

Look up the FileSystemObject it has a method FileExist which will return a boolean value of true or false.

Need help writing code

Thanks for the reply, but I have never used VB in Access and do not understand how to use FileSystemObject and FileExist. I was hoping to find someone that could help me write the code and tell me where to copy and paste it in Access. I have read a bunch of threads on the subject and tried to cut and paste code I found, but could never get it to work for me. Thus, I have provided the table and field names in my initial posting.

I am not sure how extensive of a code this would have to be -- hopefully someone can help me out -- I have been searching for a solution for over a week now and Access World is my last hope!!!

Here's a function that returns 'True' if the file 'filespec' exists.

lagbolt said:

Here's a function that returns 'True' if the file 'filespec' exists.

Code:

Function FileExists(filespec As String) As Boolean
   FileExists = CreateObject("Scripting.FileSystemObject").FileExists(filespec)
End Function

Click to expand...

Thanks just what I was looking.

I have a hyperlink open from a select query, in vba, but would like to have it check whether a .pdf file exists in the hyperlink first. I don't imagine the FileExists function will work on a hyperlink, right?

just this should do it, surely

file_exists = dir("full_path_to_some_file")<>""