insert hyperlink into form pointing to file

[accessliz]

Hi

i have a table of calls, with a field called "link to file".
i have a form where staff fill in their calls and when they have completed the call there is an option to insert a hyperlink to where the file is stored.

i previosuly had an attachemnt there but the database would just grow and grow so now its prefered there is a link to where the files are kept.

i just dont know how to set it up, i have tried adding in a text field and setting it to hyperlink which does work but on the form the user has to right click the button and edit hyperlink to place it in - is there an easier way of just clicking the button and it brings up a folder location where you select the file and it saves it as a hyperlink into the table under linked to file?

thanks -i am loving the world of databases but its so frustrating not being able to do what you think is simple, well it probably is and i just dont know it
:banghead: thanks for any help
liz x

 

[pr2-eugin]

Hello accessliz, Welcome to AWF

I have this code which does exactly what you are after. Just rename LinkA to whatever you have named the Control as..

Private Sub LinkA_Click()
[COLOR=Green]'``````````````````````````````````````````````````
'Code Courtesy of                                 '
'  Paul Eugin                                     '
'This function is to Open or Save hyperlink files.'
'``````````````````````````````````````````````````[/COLOR]
    If Len(Me.LinkA & "") = 0 Then
        [COLOR=Green]'If there is a NO HYPERLINK FILES stored, then a prompt to select the file is provided.[/COLOR]
        Dim fileDump As FileDialog
        Set fileDump = Application.FileDialog(msoFileDialogOpen)
        If fileDump.Show Then
            Dim filePath As String, fileName As String
            
            filePath = fileDump.SelectedItems(1) [COLOR=Green]'here goes the route to your document[/COLOR]
            fileName = StrReverse(filePath)
            fileName = StrReverse(Mid(fileName, 1, InStr(fileName, "\") - 1))
            Me.LinkA = fileName & " # " & filePath [COLOR=Green]'the # sign is very important[/COLOR]
        End If
    End If
End Sub

The above needs reference to Microsoft Office XX.X Object Library. Where XX.X represents version number I have 14.0 installed.

 

[accessliz]

thanks very quick
and i doubt i would have figured that out me thinks!

its not working though, when i click in the box it pops up with private sub in yellow, and filedump as filedialog in blue ?

thanks

 

[pr2-eugin]

I don't know what you exactly mean by this..

and i doubt i would have figured that out me thinks!

I love playing this game ! You miss out some important words, and I have to guess.. Lets start..

its not working though, some error pops up when i click in the box it pops up with "Compiler Error: User Defined Type Not Defined" highlighting private sub in yellow, and filedump as filedialog in blue ?

The solution was already given in post

The above needs reference to Microsoft Office XX.X Object Library. Where XX.X represents version number I have 14.0 installed.

 

[accessliz]

does the reference bit get pasted in too?

Oh sorry - i dont understand​

thanks

and i doubt i would have figured that out me thinks! -
meant im glad i asked the question becuase i would have never figured all what was needed to insert a hyperlink

 

[pr2-eugin]

You need to add the Reference. In the Menu Bar under Tools Menu -> References...

When you click, you will be getting a Pop up window with all the available reference (with some already ticked). Now alphbetically you can scroll through the list to find something like "Microsoft Office 14.0 Object library", tick the checkbox, Press Ok. Compile, it should now compile properly.

NOTE: You might not 100% have the "Microsoft Office 14.0 Object library" it might be "Microsoft Office 12.0 Object library", the version number might vary from machine to machine..

 

[accessliz]

I could kiss you!
it works perfect - thank you very much

ps sorry for being a bit slow - you learn something everyday

 

[pr2-eugin]

No problem ! Good Luck !

 

[440025544]

Does this solution only apply to Access 2007 or higher ?
I tried with Access 2003 without success

Thierry