0

how to use hyperlink to open files based on fields value? for example:

The file name is word 289.docx and located inside of z:/data/

this is Microsoft access table:

Field1 Filed2 hyperlink
word 289 ="z:/data/"&[Field1] &" "& [Filed2] & ".docx"

When I clicked on hyperlink get an error below:

Microsoft access can't follow the hyperlink

I try to use another way like below:

link#"z:/data/" & [Field1] &" "& [Filed2] & ".docx"#

but doesn't work again.

why does this hyperlink not work?

nik ravan
  • 1
  • 3
  • Is the field name really Filed2? Exactly how are you attempting to run the hyperlink? – June7 Nov 11 '22 at 00:14
  • Does this answer your question? [Access - Hyperlinks Aren't Linking](https://stackoverflow.com/questions/21581485/access-hyperlinks-arent-linking) – June7 Nov 11 '22 at 00:49
  • no, it's not real name of field. – nik ravan Nov 11 '22 at 06:43
  • unfortunately, it's not answer for my question. – nik ravan Nov 11 '22 at 06:44
  • And why not? It seems to explain why your hyperlink is not working and describes how to build a functional hyperlink. If you would answer my other question, might be able to offer more focused suggestion. – June7 Nov 11 '22 at 06:48
  • I don't use VBA code, just use a hyperlink field inside a table. but if you think your answer is correct, please explain more, thank you :) – nik ravan Nov 11 '22 at 06:54
  • 1
    Build the hyperlink string with proper structure as shown in the other posting: `#z:/data/" & [Field1] & " " & [Field2] & ".docx#"`. Can be done in table Calculated type field or query or textbox or VBA. Don't use = sign unless calculating in textbox. AFAIK, a Hyperlink type field cannot have an expression as you show. I do not use Hyperlink type field. – June7 Nov 11 '22 at 07:01
  • i used ```"z:/data/" & [Field1] & " " & [Field2] & ".docx"``` and its a string not a link. when use `#` I got data value error. – nik ravan Nov 11 '22 at 07:13
  • When you used it where - Calculated type field, query, textbox? What I suggested works for me. – June7 Nov 11 '22 at 07:20
  • I used it in calculated type field. – nik ravan Nov 11 '22 at 07:37
  • I forgot the initial `"` mark in my example. Did you use one in your expression? – June7 Nov 11 '22 at 07:45
  • yes, i used, exactly this ```#"z:/data/" & [Field1] & " " & [Field2] & ".docx"#``` and this ```#"z:/data/" & [Field1] & " " & [Field2] & ".docx#"``` and this ```#z:/data/" & [Field1] & " " & [Field2] & ".docx#"``` – nik ravan Nov 11 '22 at 07:53
  • No, the # characters must be within the quote marks. See the second # in my example. Add quote mark at beginning before first #. – June7 Nov 11 '22 at 07:53
  • i used this too ```"#z:/data/" & [Field1] & " " & [Field2] & ".docx#"``` but didn't work, its identified # as string and show `#z:/data/Field1 Field2.docx#` – nik ravan Nov 11 '22 at 07:55
  • Okay, that is correct and should not error. Exactly when does "data value error" trigger? Bind textbox to that calculated field and set IsHyperlink property to yes. – June7 Nov 11 '22 at 07:58
  • Oh, and Mazoula makes a good point. Should use \ instead of / for files on computer or LAN. – June7 Nov 11 '22 at 08:05
  • This way worked on reports and forms not inside tables. – nik ravan Nov 11 '22 at 08:05
  • Correct. That would require a Hyperlink type field. Users should not work directly with tables anyway. – June7 Nov 11 '22 at 08:07

1 Answers1

0

Here is my solution based on June7's comments. I found this link helpful: https://www.devhut.net/microsoft-access-hyperlinks/

I couldn't find a way to reference functions inside the hyperlink, but I could write a vba routine to automate updating the hyperlink in the table from the fields

Public Sub updateHyperlinks()
Dim rs As Recordset
Dim db As Database
Set db = CurrentDb
Set rs = db.OpenRecordset("Mytablename")
Do While Not rs.EOF
rs.Edit
rs!Hyperlink = "myvisiblelinktext#" & rs!Field1 & rs!Field2
rs.Update
rs.MoveNext
Loop
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub

One caveat Field1 includes the file extension for more generality

edit:

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|         ID         |             hyperlink             |             filename              |                       filelocation                       |         hyperlinkaddress          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|                  2 |                                   | board.xlsx                        | C:\Users\bubblegum\Desktop\                              |                                   |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|                  3 |                                   | app.docx                 | C:\Users\bubblegum\Desktop\                              |                                   |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

table after updating hyperlinks with address inside hyperlink manually added. the table itself shows the hyperlink in the hyperlink column.

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|      ID       |        hyperlink         |           filename           |               filelocation                |                    hyperlinkaddress                     |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|             2 | myvisiblelinktext#C:\Use | board.xlsx                   | C:\Users\bubblegum\Desktop\               | C:\Users\bubblegum\Desktop\board.xlsx                   |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|             3 | myvisiblelinktext#C:\Use | app.docx                     | C:\Users\bubblegum\Desktop\               | C:\Users\bubblegum\Desktop\app.docx                     |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
mazoula
  • 1,221
  • 2
  • 11
  • 20
  • Not sure OP is using Hyperlink type field. Your expression doesn't have second `#`. – June7 Nov 11 '22 at 07:47
  • good catch. Since in the end this solution is just concatenating strings into a url I add the \ onto the end of Field1 in the data like C:\Users\username\Desktop\ – mazoula Nov 11 '22 at 08:02
  • Good point, I should have noticed wrong slash used. However, the fields do not have folder path, that is hard coded. – June7 Nov 11 '22 at 08:03