UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Cancelling A Query That Calls A VBA Function, Access 2016    
 
   
HillChris7714
post Feb 16 2017, 12:12 PM
Post#1



Posts: 16
Joined: 30-September 16



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
Go to the top of the page
 
theDBguy
post Feb 16 2017, 12:14 PM
Post#2


Access Wiki and Forums Moderator
Posts: 69,964
Joined: 19-June 07
From: SunnySandyEggo


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

--------------------
Go to the top of the page
 
HillChris7714
post Feb 16 2017, 12:16 PM
Post#3



Posts: 16
Joined: 30-September 16



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.
Go to the top of the page
 
theDBguy
post Feb 16 2017, 12:19 PM
Post#4


Access Wiki and Forums Moderator
Posts: 69,964
Joined: 19-June 07
From: SunnySandyEggo


Let us know how it goes...

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    26th June 2017 - 10:41 PM