Hyperlink question

[Guest]

Surely Access has a way to detect if a file actually exists without having to
open each hyperlink. I'm using a continuous form and have a hyperlink to a
file for each record. It'd be real convenient to indicate on the form
whether or not the file is actually there. Is that possible to do?

Thanks

 

[Douglas J. Steele]

If you're trying to determine whether or not a given file exists, you can
use

If Len(Dir(FullpathToFile)) > 0 Then
' File exists
Else
' File doesn't exist
End If

 

[Guest]

Thanks. That's what I'm asking. Now, do I substitute my text box name for
"FullpathToFile" or do I have to test each link individually?

 

[Douglas J. Steele]

Substituting a text box name would mean that you'd be able to test whatever
file name is contained in that text box. You'd still have to loop in order
to test each link.

 

[Guest]

I put in the entire path and it worked. Expecting the same, I substituting
the text box name for "FullpathToFile" and it didn't.

 

[Douglas J. Steele]

Take a look at what's actually stored in the text box. Hyperlinks have 3
separate parts, separated by octothorpes:

displaytext#address#subaddress

Try using the HyperlinkPart function: instead of referring to Me.MyTextbox,
use HyperlinkPart(Me.MyTextbox, acAddress)

 

[Guest]

That did it. You were very informative and helpful. Thanks

 

[Vylent Fyre]

I know this is such an old post, but I am trying to do exactly this but I am
lost on where to implement this coding you provided. Please forgive my
ignorance; I am in the process of learning more about MS Access every day

Firstly, I have a table, tbl_Journal_Entries_Listings_Divisional, that
everyone types in the hyperlink in the Scanned JE Link field.

I've tried building a report and a form off of this to put your coding in
either as a button (On Click event) or even On Load / On Open and no luck.
I'm sure it's simple and I'm just not grasping the full concept of this. I
apologize for my ignorance.

What I am trying to do - I'd like to show on a report that these links ARE
valid links (file does exist/matches path name and file name) so that my
manager can see these links and know they are valid/do work. If I can't do
it on a report, I'll make do with a form if this is possible.

ANY help on this would be AWESOME.

Thank you so much!!! =)

 

[Douglas J. Steele]

Sounds to me as though you might need to put the code in Format event of the
report's Detail section.

That fires for each row of date being displayed on the report, so you can
modify an indicator to show valid or not.

 

[Vylent Fyre]

Thank you so much for your reply! I didn't think I would get a reply to this
since it's been a long time since this was posted =)

Okay - I did what you said, on the On Format event in the Details section of
the report, I put the coding in there - This is my coding and it's not quite
working... It keeps throwing up pop up boxes "File not Found" then it goes to
the debugging mode on the line If Len(Dir....

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Len(Dir(HyperlinkPart(Scanned_JE_Link, acAddress))) = 0 Then
Let Text83 = "File not found"
MsgBox ("File not found")
Else
Let Text83 = "File confirmed"
End If
End Sub



Text83 is a text box. Do I need change this? Any suggestions? Thanks a
MILLION for your post and excellent codings; I couldn't have gotten nearly as
far as I have without everyone here

 

[Vylent Fyre]

Ooohh - I made myself sound silly - I took out the MsgBox (that was there for
referencial purposes) and with the MsgBox out of the way, it just goes
straight to the coding line - So I'm sure I have that wrong somehow.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Len(Dir(HyperlinkPart(Scanned_JE_Link, acAddress))) = 0 Then
Let Text83 = "File not found"
Else
Let Text83 = "File confirmed"
End If

End Sub

 

[Douglas J. Steele]

Try

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Len(Dir(HyperlinkPart(Me.Scanned_JE_Link, acAddress))) = 0 Then
Me.Text83 = "File not found"
Else
Me.Text83 = "File confirmed"
End If

End Sub


If you still run into problems, try

If Len(Dir(HyperlinkAddress(Me.Scanned_JE_Link))) = 0 Then

 

[Vylent Fyre]

It works beautifully! I'm so happy about this =) This is FANTASTIC!

Now just a couple more questions (for anyone who has the time to respond!)

Sometimes we have JE's that are too large to be in one file so we have to
break them up into two files - When this occurs they're stored in a directory
and the link is to the directory, not a file because it's more than one file.
When it sees these, it's showing it, of course, as "File Not Found" which is
true because it isn't a file. Is there a way I could add another Iif
statement that would tell it if it is a Dir standalone, to state "Directory
Found"?

Also, is there a way I could have another report to only show the "File Not
Found"? This way I could pass these out accordingly and get these links
fixed.

MANY MANY MANY thanks to you, Douglas, a true genius, and to all you
geniuses out there!! =)

 

[Douglas J. Steele]

Try playing with

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Len(Dir(HyperlinkPart(Me.Scanned_JE_Link, acAddress))) = 0 Then

If Len(Dir(HyperlinkPart(Me.Scanned_JE_Link, acAddress)), vbDirectory)
= 0 Then
Me.Text83 = "File/Directory not found"
Else
Me.Text83 = "Directory confirmed"
End If
Else
Me.Text83 = "File confirmed"
End If

End Sub

 

[Vylent Fyre]

BTW - I added a new column with an expression to determine if it's a
Directory or File - This new field is called "File or Dir". I'm trying to
add this into the coding now but not having much luck:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If(Me.File_or_Dir)=â€Dirâ€,
If Len(Dir(HyperlinkPart(Me.Scanned_JE_Link, acDir))) = 0 Then
Me.Text83 = "Directory not found"
Else
Me.Text83 = "Directory confirmed"
End If

If Len(Dir(HyperlinkPart(Me.Scanned_JE_Link, acAddress))) = 0 Then
Me.Text83 = "File not found"
Else
Me.Text83 = "File confirmed"
End If

End Sub

 

[Vylent Fyre]

Douglas,

That exact coding produced errors but I did do the following ways and I
still get the "File/Directory Not Found" on the ones that have valid links to
the directories (The links to files work perfectly! I am still doing
backflips over this!)

Here are the three different ways I've tried:

Your exact coding kept producing errors -

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Len(Dir(HyperlinkPart(Me.Scanned_JE_Link, acAddress))) = 0 Then

If Len(Dir(HyperlinkPart(Me.Scanned_JE_Link, acAddress), acDirectory))
= 0 Then

Me.Text83 = "File/Directory not found"
Else
Me.Text83 = "Directory confirmed"
End If
Else
Me.Text83 = "File confirmed"
End If

End Sub


So I fixed it to do this (No errors in the codings and the report ran) -

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Len(Dir(HyperlinkPart(Me.Scanned_JE_Link, acAddress))) = 0 Then

If Len(Dir(HyperlinkPart(Me.Scanned_JE_Link, acAddress, acDirectory)))
= 0 Then

Me.Text83 = "File/Directory not found"
Else
Me.Text83 = "Directory confirmed"
End If
Else
Me.Text83 = "File confirmed"
End If

End Sub



And this way (No errors and report ran) -

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Len(Dir(HyperlinkPart(Me.Scanned_JE_Link, acAddress))) = 0 Then

If Len(Dir(HyperlinkPart(Me.Scanned_JE_Link, acDirectory))) = 0 Then

Me.Text83 = "File/Directory not found"
Else
Me.Text83 = "Directory confirmed"
End If
Else
Me.Text83 = "File confirmed"
End If

End Sub



Then lastly, out of desperation, broke them up into two separate If
Statements thinking maybe that was why it wouldn't work originally....


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Len(Dir(HyperlinkPart(Me.Scanned_JE_Link, acAddress))) = 0 Then
Me.Text83 = "file not found"
Else
Me.Text83 = "File confirmed"
End If

If Len(Dir(HyperlinkPart(Me.Scanned_JE_Link, acAddress, vbDirectory)))
= 0 Then
Me.Text83 = "Directory not found"
Else
Me.Text83 = "Directory confirmed"
End If

End Sub


I am so sorry to keep pestering you - But I'm so close I can taste it so...
I'm like a dog with a bone; I won't let go until it's done =)


VF

 

[Douglas J. Steele]

Sorry, I was going by memory. Where I have acDirectory, it should be
vbDirectory. (Hopefully you realize that the = 0 Then part should be on the
same line)

 

[Vylent Fyre]

Not a problem, Douglas!

I had changed it to vbDirectory and same results. And yes the = 0 is on one
line =) The code won't work without that on one line

Here's a copy of the coding - It's working great except it's still showing
"File/Directory not found" for the scanned links that are linking to a
directory. I click on the link in the query that feeds this report and the
link opens up the directory in Windows Explorer which is correct. I'm
totally stumped on this because it should be working! The code look 100%
right to my eyes...



Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Len(Dir(HyperlinkPart(Me.Scanned_JE_Link, acAddress))) = 0 Then

If Len(Dir(HyperlinkPart(Me.Scanned_JE_Link, vbDir))) = 0 Then

Me.Text83 = "File/Directory not found"
Else
Me.Text83 = "Directory confirmed"
End If
Else
Me.Text83 = "File confirmed"
End If

End Sub

 

[Douglas J. Steele]

That's supposed to be

If Len(Dir(HyperlinkPart(Me.Scanned_JE_Link), vbDir)) = 0 Then

 

[Douglas J. Steele]

Make that

If Len(Dir(HyperlinkAddress(Me.Scanned_JE_Link), vbDirectory)) = 0 Then

or

If Len(Dir(HyperlinkPart(Me.Scanned_JE_Link, acAddress), vbDirectory)) = 0
Then


If it still doesn't work, does HyperlinkAddress (or HyperlinkPart) return a
terminating slash?