Problems with edithyperlink macro action

[CK]

I use Access 2007. I have created a macro to enable users to hyperlink files
to their newly created records. The user clicks on the default link, which
contains the full path to the server. The macro open the edit hyperlink
window, with the default location, already selected, and then the user clicks
on browse for file to go and select his/her file.
I am aware that there are lots of discussions where people say that there
are problems with hyperlinks, but this works fine for me and for my
colleagues on the same site as me. However, in this application I have
created for another site, when they come back to the edit hyperlink window
after choosing their file, they get something like \..\..\file name.xxx; this
then leads to an error when they try to click on the created hyperlink.
We have all recently migrated to the same laptops, software, etc...
everything should be identical.
I thought I had a way round it, as you can actually select a file in file
manager and drag and drop it into the text box. On my laptop, the hyperlink
is sucessfully created with the full path, but not for my colleagues on this
other site. The same problems happens. The only way we have found to get this
to work is to map the drive. But everyone would then have to have the drive
mapped using the same letter.
Does anyone have any idea which setting could be different to cause this
difference in behavior?

Thanks for your help!

 

[Clifford Bass]

Hi CK,

Could try something like this; see if it does the trick. It will
require that you add the Microsoft Scripting Runtime reference (in the VBA
Editor, Tools menu, References item).

Dim fso As New FileSystemObject

DocumentName = fso.GetAbsolutePathName(strDocumentPath)

Clifford Bass

 

[Pete D.]

Another site is the hint. Mapped drives may be part of the problem. Read
about the make up of a hyper link such as mailto:\\ File:\\ Http:\\ you will
probally find that the way it is formated when the remote site selects the
hyperlink using the search/select box that it is returning an invalid format
for the hyperlink. Pete

 

[Pete D.]

One other thing I left out, hyperlinks have three parts.
You should read
http://allenbrowne.com/casu-09.html
HTH, Pete

 

[CK]

Thanks for your replies. I guess I should have added that I am not trained on
VBA; I have had some success in copying bits of code and adding to them, but
I'm afraid I didn't really understand how I could use your information
Clifford.

I agree with you Pete that a clue should be the different sites. I should
add that I placed the database on their server, so it should mimic what I am
doing on my site.

I saw a potential solution in another thread yesterday. There was some code
which gave a complete different way of opening up a search/explore window,
rather than use the edit hyperlink. I thought I would try that, but I can't
seem to find it now though! Any ideas?

 

[CK]

Clifford,

I found what I was referring to (http://www.mvps.org/access/api/api0001.htm)
and I am trying to use what you suggested as follows, however the whole path
is not pasted as I was hoping. Also what is pasted in does not seem to be a
working hyperlink?

Private Sub Combo8_Click()
On Error GoTo Err_combo8_Click

Dim strFilter As String
Dim strFIleName As String
Dim strInputFilePath As String
Dim strDefaultDir As String

'Set up default path and file
strDefaultDir = "\\server\location"

strFilter = ahtAddFilterItem(strFilter, "Word Files (*.DOC)", "*.DOC")
strInputFilePath = ahtCommonFileOpenSave( _
InitialDir:=strDefaultDir, _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)

strFIleName = fso.GetAbsolutePathName(strInputFilePath)
Me.Combo8 = strFIleName
--
CK

Thanks for your replies. I guess I should have added that I am not trained on
VBA; I have had some success in copying bits of code and adding to them, but
I'm afraid I didn't really understand how I could use your information
Clifford.

I agree with you Pete that a clue should be the different sites. I should
add that I placed the database on their server, so it should mimic what I am
doing on my site.

I saw a potential solution in another thread yesterday. There was some code
which gave a complete different way of opening up a search/explore window,
rather than use the edit hyperlink. I thought I would try that, but I can't
seem to find it now though! Any ideas?

 

[CK]

Hi Clifford,

I have managed to do it now as follows, with
http://www.mvps.org/access/api/api0001.htm saved as a module

Dim strFilter As String
Dim strFIleName As String
Dim strInputFilePath As String
Dim strDefaultDir As String
Dim fso As New FileSystemObject
Dim stDocName As String

stDocName = "selectHyperlink"
'Set up default path and file
strDefaultDir = "\\server\folder"

If Me.lngDocumentCode <> "" Then
DoCmd.RunMacro stDocName
Else
strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")
strInputFilePath = ahtCommonFileOpenSave( _
InitialDir:=strDefaultDir, _
Filter:=strFilter, OpenFile:=False, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)

strFIleName = fso.GetAbsolutePathName(strInputFilePath)
Me.Combo8 = strFIleName & "#" & strFIleName & "#"
End If

Thanks for your help!!

 

[Clifford Bass]

Hi CK,

Glad to be of help. I think I forgot to keep getting replies and did
not notice you had a further question. Oh well, at least you got it to
work--that is the important thing.

Clifford Bass

 

[Pete D.]

I went to bed, glad to see you got it. The last # could add note such as
"Open File" which will pop up when hyperlink is hovered over with the mouse.