hyperlink data not transferring to destination table

Question

I'm inserting records that includes a hyperlink field from 1 table into another that include a hyperlink field of the same name. In the source table, the hyperlink behaves correctly; however, when inserted into the destination table, it does not. The field displays the correct "hyperlink" (path) in the destination; however, it is not functional - clicking it does nothing. Editing the hyperlink in the destination table shows that the link was copied, but with no text and no address (!?)

Dim sSQL As String
sSQL = "INSERT INTO tbeAdditionalPages (type, printCatalogSheet, BaseCatalogSheet, CatalogSheetLink, PrintOrder, IsMountingDetail) " & _
"SELECT '" & Forms![frmSpec].[Type] & "', true, true, '#' & CatalogSheetLink, printOrder, false " & _
"FROM FixtureCatalogsPages " & _
"WHERE Manufacturer = '" & Forms![frmSpec].Manufacturer.Value & _
"' and CatalogNumber = '" & Forms![frmSpec].CatalogNo.Value & "';"
CurrentDb().Execute sSQL, dbFailOnError 

I did some research and changed the code to read:

sSQL = "INSERT INTO ... 'text#' & CatalogSheetLink & '#' ... 
CurrentDb().Execute sSQL, dbFailOnError

The result was: now the destination field has text, but still no address. I then revised it to:

'text#' & CatalogSheetLink.address & '#'

this resulted in an error.

any thoughts whatsoever would be greatly appreciated, mark

Answer 1

You are updating from a working hyperlink address, so simply:

"SELECT '" & Forms![frmSpec].[Type] & "', true, true, CatalogSheetLink,  <...>

If you wish to change the description, you need to get the substring, like so:

 INSERT INTO MyTable (MyHyperlink)
 SELECT 'New Description' & Mid(MyHyperlink, InStr(MyHyperlink, '#')) 
 FROM SomeTable