I'm a complete novice... help with gohyperlink?

[joeshaw123]

Hi all,

Found this forums on my search to uncover what I am doing wrong.
I am a complete beginner at using access and VBA, but have been thrust into this position and need to get my head round some basic things!

I'll explain what I need to achieve first:
I have a inventory/stock/sales/purchasing system running on an SQL back end, kindly developed by a former colleague of a colleague. The system itself is running fine, but I have been tasked with tweaking it to add in additional functionality that doesn't affect the running of the existing system.

In short, I have a form named MRP_Inventory, in which you can go through all products that we manufacture/order/sell. I have added in a field called MI_Hyplink, to which a text box named Hyplink is attached, which contains the network path to the PDF file of the technical drawing of the selected component; and obviously the file path changes for each record.

I have used Allen Browne's GoHyperlink (allenbrowne.com/func-GoHyperlink.html[/url]) on the click of a button to treat the file path as a hyperlink.
The code on the button is simply

Private Sub Command57_Click()

Call GoHyperlink(Me.Hyplink)

End Sub

And it has been working! I had only used it on one record and it opened the PDF file up in Adobe Reader as it should. I have since, however, attempted to just fill in other records with their corresponding link, and all I get is error 87: an unexpected error has occurred
Meanwhile, the original one was working fine. I copied the link from the original record into a new one, and it still wouldn't work on the new record. I deleted the link on the original record and replaced it with EXACTLY the same file path and now nothing works at all!

Any ideas? Is there a better way of doing this? I would like to store 2 or 3 different file paths per record as I need to be able to open up a technical drawing, a test method and a manufacturing procedure at the click of an appropriately named button. It has been suggested that I use a shell command, but I haven't the foggiest how to do that.

Any and all help MUCH appreciated, thanks in advance!
Joe

 

[Trevor G]

Welcome Joe,

A shell command will open up the executable file so if it was notepad it would look for notepad.exe if calculator it would be calc.exe when you want it to also open a file then in the shell command you include the path and file name which could be a combination of the field names. Sample below

Sub shell2()
Dim retval As String
retval = Shell("notepad.exe", vbNormalFocus)

End Sub

Then to open a file

Sub shell1()
Dim retval As String
retval = Shell("notepad.exe Full path and document name.pdf", vbNormalFocus)

End Sub

 

[joeshaw123]

Thanks for the response.
I have been able to get shell to open basic .exe's but the network that I am using is on a variety of operating systems, each with their own applications installed. Therefore rather than having a full application path, which I need for AcroRd32.exe as the shell won't open that on its own, I need the shell to open a file in its default program.

I have found a piece of code that goes in the declarations section of the form which forces the shell to use the default program to open a file.
Here is the code I have used:

Private Sub Command57_Click()

Dim oPath As String
Dim strFile As String
Dim PathEnd As String
Dim nDT As String
Dim nApp As String

oPath = "T:\TSP Rail Drgs"
PathEnd = Forms!MRP_Inventory!fpath
strFile = oPath & "\" & PathEnd

nDT = GetDesktopWindow()
nApp = ShellExecute(nDT, "Open", strFile, "", "C:\", SW_SHOWNORMAL)

End Sub

If my fpath text box is empty, it says invalid use of nulls, if the text box has the correct drawing name ie Fibt01.PDF then the button does nothing.
What do I need to do to make the shell see what is in the text box?

 

[Trevor G]

You might need to requery the record before it fires the button, when you have moved off a record and then come back to it when you click does it fire off then?

 

[joeshaw123]

Right then, I have got it to work in a roundabout way! I have gone back to using a hyperlink because I couldn't get the shell to work whatsoever. I believe it may be because of a similar problem though. To diagnose what I was doing wrong, I placed a msgbox in there to see what access was seeing. It appears that the link it was trying to follow had <div></div> tags around it, which it was trying to use in the link and obviously not following it at all!
The data is not stored as a hyperlink as it is in an SQL back end; it is stored as text data as it could be any length at all, I don't know if that is correct or whether I could use nvarchar or whatever other data types SQL gives you.
Anyway, to solve the problem, I made the gohyperlink command ignore the first 5 and the last 6 characters using the following code:

Call GoHyperlink(Mid(Left(fpath, Len(fpath) - 6), 6, 999))

Which made the hyperlink work as normal and therefore open in the default program.
Is there any way I can make access/sql not put these <div> tags on the hyperlink?
Thanks