[X]My Assistant

[HillChris7714]

I have an Update Query that holds a calculated field, which verifies that a file exists... and displays the file path if it does, and "Not Found" if it doesn't. Sounds confusing, but...

Essentially, the Update To value is as follows:
ConfirmFilePath("Z:\MyPath\" & [FirstName] & " " & [LastName] & ".pdf")

...and it calls this function:

CODE

Public Function ConfirmFilePath(ByVal strTestPath As String) As String
    ConfirmFilePath = "Not Found"
    On Error GoTo Err_Permissions
    If Dir(strTestPath) <> "" Then
        ConfirmFilePath = strTestPath
    End If
Exit_Me:
    Exit Function
Err_Permissions:
    MsgBox "You don't have permissions!"
End Function

So, essentially I want to check a particular folder on the network (Z:\ is a mapped drive for our users) for a .pdf that contains the guy's First and Last Names... if it exists, we'll return the file name. If not, we'll return "Not Found."

The snag I'm hitting is, if there is a problem with user permissions on the network, the update statement bombs on the "Dir(strTestPath)" statement above... and the "You don't have permissions" alert is thrown up for every single record the update statement is run against. Is there any way, if the function drops into the "Err_Permissions" trap, that it can just abort the entire process and stop trying to update?

This probably sounds confusing... let me know if this isn't clear.
This post has been edited by HillChris7714: Feb 16 2017, 12:14 PM

[theDBguy]

Hi,

Perhaps the simplest fix for right now is to not use the MsgBox line in the error handler; rather, just exit the function.

Just my 2 cents...

[HillChris7714]

That actually might not be a bad idea. After this update is run, the value is placed in a hyperlink field that the user can then use to manually select a file. If they don't have permissions, it would be at THAT point that Windows would just tell the user itself.

[theDBguy]

Let us know how it goes...