Create a hyperlink field using two fields in a single table.

[aquabp]

I am creating a file cabinet db for project plans and project pictures. I found code from Allen Bowne that imports these files pretty well. Code is lengthy so here is the link to it if needed: http://allenbrowne.com/ser-59.html This code pulls in the FName and FPath as well as an ID and date Uploaded into a table called Files.

I have a table called FMaster which holds all of the file names pulled so far. I am appending the Files table to the FMaster Table.

In researching this question it sounds like I cannot create an append or an update query to add a hyperlink to the FMaster Table. I haven't been able to get the few examples out there to work. Most of the posts I read have been from a few years ago. I just want to check to see if it holds true today. I am using office 365 current version of Access. Nothing shows this has changed but it doesn't hurt to ask.

Also, not sure if I can create the hyperlink when importing the file name and path via VBA code.

Most of the posts refer to a form sourced by a query. I know how to do this but I do not understand how the form creates the hyperlink. If I cannot create the query can someone explain how this form works?

Thanks in advance!

[June7]

A hyperlink string is composed of 3 parts. Review http://www.allenbrowne.com/casu-09.html.

This string can be manipulated programmatically.

I just tested INSERT and UPDATE actions and the hyperlink field did get populated.

[aquabp]

Hi June7!
I read that link. I tried to run an update. I used this code below but could not get it to work. I normally use an update query so not real comfortable with this. What did I do wrong?

#UPDATE [FMaster] SET [FMaster].[Link] = [FMaster]![Path] & "\" & [FMaster]![FName] & "#" & [FMaster]![Path] & "\" & [02_Detail]![FName] & "#"
WHERE ((([FMaster].FID)=5));#

[June7]

Why do you show # characters at begin and end of the SQL statement?

That IS an UPDATE query. However, why bother with this? The hyperlink can be constructed whenever needed. An expression in SELECT query or textbox ControlSource can build the hyperlink string.

Why reference [02_Detail]![FName]? [FName] is already available in [FMaster].

If you want to pull data from another table, probably need to set a JOIN between them. What are the PK/FK linking fields?

First build SELECT query that properly relates records then switch to an UPDATE and set the fields to be updated and fields to update from.

Why the filter criteria?

[aquabp]

# was suppose to wrap code.
Habit. 02-Detail and FMaster are one in the same table. Forums have given me grief for using the numbers out front. Missed one of the 02-Detail when putting it in.
Not good with Select so I will get back to you when I get one to work.
Question: I usually don't write out the select query I use the query form. Why does the written select query work and the form does not?
The file is populated. Wanted to test a section to see if it worked.

[June7]

I don't understand that question. What do you mean by 'query form' - the query builder? Written query where - in SQL View?

[aquabp]

Yes, I think.
So this select statement in the query builder (?) creates a field named Link with the path and file name combined. I built the query right clicked the query tab and went to SQL view to get this.
SELECT [FID], [Path], [FName], [path] & [Fname] AS Link FROM [02_Detail];

An update statement would look like this.
UPDATE [02_Detail] SET [Link] = [path] & [Fname];

I cannot get either to run in VBA.

Dim strSQL As String

strSQL = ("SELECT [FID], [Path], [FName], [path] & [FName] AS Link FROM [02_Detail];")
DoCmd.RunSQL (strSQL)

I get an error message that I need an SQL statement. Help says it does not have help for this so clearly I do not understand VBA.

[June7]

Well, that is not an UPDATE query, it is only a SELECT query and SELECT queries are not 'run' because they are not actions.

Link must be an actual natural field in the table, not a calculated field in query. So first create the field in the table design then run UPDATE.

Once the SELECT query object has the fields you want, click UPDATE from the ribbon to switch the query type, set fields as already described for updating, then RUN the query.

In VBA I would use:

CurrentDb.Execute "UPDATE [02_Detail] SET [Link] = [path] & [Fname]"

[aquabp]

Hi June7
Ok, I don't usually write out the SQL statements in VBA I use docmd.openquery command to run a query. Less typing and I know the query works already. I get confused sometimes about VBA code and SQL. I think that the code is the query when actually the code runs the query. The query gets you your answer.

So like my update query the code below updates the hyperlink field in the 02_Detail table, but still have the original issue that the link still doesn't work.

Dim dbs As DAO.Database
Dim strSQL As String
Set dbs = CurrentDb
  
CurrentDb.Execute "UPDATE [02_Detail] SET [Link] = [Path] & [FName];"

So I looked at your original response and my response to it.
I ran this:
CurrentDb.Execute "UPDATE [02_Detail] SET [Link] = [FName] & "#" & [Path] & [FName] & "#";" Got an error message saying that code was expecting an end statement at the pound sign. Help said it was a parentheses issue. I tried several different placements but still got same error.

I then ran this:
CurrentDb.Execute "UPDATE [02_Detail] SET [Link] = [FName] & [Path] & [FName] & "#";" It updated the field, but link still did not work. When I manually added a # sign between the first FName and Path the link worked.

So how do I add the # sign in the statement?

[June7]

Concatenate same as you did at the end.

But I am surprised it worked because there is an extra " mark in the code. The semi-colon is not needed for action SQL in VBA although won't hurt.

CurrentDb.Execute "UPDATE [02_Detail] SET [Link] = [FName] & "#" & [Path] & [FName] & "#;"


It really is not necessary to have the Link field at all. This hyperlink string can be created by this same concatenation expression in a textbox ControlSource. So why do you want to save this hyperlink string? Every time you create a new record, this field will have to be updated. If the FName or Path are changed, the hyperlink string will have to be edited. Saving calculated data (data dependent on other data) is usually not a good idea and can just complicate db management.

[aquabp]

Good Question! I have added =[FName] & "#" & [Path] & [FName] & "#" to the control source of a text box and a hyperlink field. Neither provide an active hyperlink.

The line below will add a FName then a path followed by the FName. It will not link to the file unless I edit the field and add the # between the first FName and the Path.
CurrentDb.Execute "UPDATE [02_Detail] SET [Link] = [FName]&[Path] & [FName]"

The line below will error out. Compiler error expected: end of statement or I would get a syntax error both highlighting the first pound sign. In help and searching the net most said that it was an ill placed parenthesis or an issue with a call phrase.
CurrentDb.Execute "UPDATE [02_Detail] SET [Link] = [FName] & "#" & [Path] & [FName] & "#""

The line below when added to a control source of a hyperlink field or a text field adds the [FName]#[Path][FName]#
result example: file1.pdf#G:\path1\file1.pdf#
It did not connect to the file and did not show the file1.pdf as the link name as expected.

=[FName] & "#" & [Path] & [FName] & "#"

What did I miss?

[aquabp]

FileCabinetZ.zip

Attached is the database. Second Tab has the two VBA Code attempts. Third Tab is a raw view of the 02_Detail Table. Currently 4 test records are in the DB. The first tab will show the files by category or project. It will show the over all category info, the 02_Detail form seen in the third tab and a list of customers that we did the project for.

I really appreciate you taking the time to look at this.
Thanks in advance!

[June7]

Building this SQL string in VBA gets complicated because of the & and # characters. Would be easier to build Access query object:

UPDATE 02_Detail SET [02_Detail].[Link] = [FName] & "#" & [Path] & [FName] & "#";

Then in VBA:

DoCmd.OpenQuery "UpdateLink"


The textbox with the calculated hyperlink needs the IsHyperlink property set to Yes. I tested your form and expression by changing data in table to my path and file names. It works.


Path is probably a reserved word. Should not use reserved words as names.

[aquabp]

Great! setting the IsHyperlink property to Yes works great! Thanks!!!

I couldn't get the VBA code to work either. I have an example code somewhere. This program will help me find where I put it! Won't need it but it will bug me I am sure!!

Thanks again!!

[June7]

The query object and VBA suggested in previous post does work.

I finally got the VBA right:

CurrentDb.Execute "UPDATE [02_Detail] SET [Link] = [FName] & '#' & [Path] & [FName] & '#'"