MS Access hypelink on deleted file

I MS Access 2013 I have a table tblContracts .In table I have a hyperlink  field “FileLocation” where I store hyperlink to  my pdf files.
On form I have a text box field where hyperlink is show in, hyperlink text is blue color and underlined .

If user click on hyperlink in text box  it takes him  to folder where file is located.
On click event of text box txtMyHyperlink I have:
Application.FollowHyperlink Me.txtMyHyperlink
It works fine if file exist, however if file is deleted I got error.
It would be good first to check if file exist in that location. However to check if file exists I have to use hyperlink path string that has  # on both side something like: #\\somefoldername\somesubfolderName\filename#
It is interesting how hyprlink text looks like, at the end of path is file name however  that file name do not have extension .pdf, just file name.

 I can see all path if I use debug.print to  check path variable.

Problem is if file is deleted and user click on hyperlink in form field then access throw error .
How to avoid this error that is long and confusing to user,  instead  gave user msg “File do not exist or file is moved to another location.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can check for the existence of a file like this:

If Dir(Me.txtMyHyperlink) <> "" Then
  '/ the file does exists
  Application.FollowHyperlink Me.txtMyHyperlink
End If
TarasAuthor Commented:
That hyperlink in "Me.txtMyHyperlink" that is coming from table will always exist. It value is from table field and  from time when it is was  entered in table.
However real file that hyperlink is referring to  is meanwhile removed or deleted.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hi Taras,
remove the hyperlink target
set Display as Hyperlink -- always
to avoid the extra stuff that also gets saved, store as TEXT not a hyperlink

put this on the click event of a command button next to the file to open it, and maybe also on the control double-click event:
   Dim sPathFile As String
   With Me.Me.txtMyHyperlink
      If Not IsNull(.Value) Then
         sPathFile = Me.txtMyHyperlink '"c:\path\filename.ext"
      End If
   End With
   Call FollowHyperlink_File(sPathFile)

Open in new window

'here is a procedure you can put into a general module to call from anywhere

Public Sub FollowHyperlink_File(psPathFile As String)
'180202 strive4peace
    On Error Resume Next
    If Len(Dir(psPathFile)) > 0 Then
      Application.FollowHyperlink (psPathFile)
      If Err.Number <> 0 Then
          MsgBox "ERROR opening file: " & psPathFile & vbCrLf & Err.Description _
            , , "Error opening file: " & Err.Number
      End If
      On Error GoTo 0
    End If
End Sub

Open in new window

have an awesome day,
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
However real file that hyperlink is referring to  is meanwhile removed or deleted.
The Dir command determines if the file exists. If your textbox control named "txtMyHyperlink" contains the file path, then using DIR with that value will tell you if the file exists at that path.

Have you tried the sample to see if it works?
TarasAuthor Commented:
I mentioned that hyperlink Path string has #.......# in it.
How dir command will recognized any Dir with #....# against real dir that do not have ## in its name.
That Dir with #....# in its name never  will be found??
TarasAuthor Commented:
Scott I tried the sample it is not working
I Just added else part to your sample and put message "File do not exist"  then I tried it on hyperlink to file that exist. It gave me that message "File do not exist" event file exists.
Since the vast majority of time, the file will still exist, I would catch the error rather than trying to prevent it.  This is something I learned when I was creating CICS transactions that were used by thousands of users where every second counted and data reads are the most expensive operation we do.

In your error procedure:

Select err.Number
   Case ????   'whatever the error number you get for a missing file
        MsgBox "File Not found.",vbOKOnly
        Resume Next
    Case Elxe
        Msgbox Err.Number & "--" & Err.Description
            Resume Exit_Proc
    End Select
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I see - you actually have a Hyperlink datatype field.

As Crystal suggests, you should convert that to Text, and then fixup the data to hold ONLY the full path to the item. You can then use DIR to determine if that file exists.

Alternatively you could parse the hyperlink:

If Dir(Split(Me.txtMyHyperlink, "#")(1)) <> "" Then
  '/ the file exists
End If

the Split function retrieves the second "element" of the array, which is the file name.
TarasAuthor Commented:
hyperlink path looks like : #\\SomeFolderName\SomeSubfolderName\filename#
I assume that split will keep second # in path string what is not good.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You assume wrong.

The split function take a string and "splits" it at the delimiters you indicate. A string like this:

display text # file name # any reference within the file

which is passed into this function:

Msgbox Split("display text # file name # any reference within the file", "#")(1)

Would return a value of "file name" (without the quotes)

Try it in your application and let us know the results.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TarasAuthor Commented:
I got this error:

7971MicrosoftAccess cannot follow the hyperlink to

It interesting that number "1" a the end of path  after FileName. I do not know where it comes from.

I mentioned earlier that path do not have file extension .pdf just file name I do not know why it was saved in database on that way.
When hyperlink was working it was opening folder where file was seating it was not opening file.
@Taras,  I see that you are all wrapped up in prevention and not interested in catching so I'll go away.
TarasAuthor Commented:
Scott I tried it and get this error

Error Opening File:7971
Error opening file:#\\SomeFolderName\SomeSubFoldername\FileName#

7971MicrosoftAccess cannot folloow the hyperlink to
TarasAuthor Commented:
Pat I will try it on Monday when I am back, if preventing to happen is not working I will try to catch error number and avoid it.
TarasAuthor Commented:
Thank you a lot McDaniel it worked finally as you suggested first time server was down and I got that strange error msg but later on it worked.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.