Concatenate two hyperlink fields in a report 1

LarryDavidow · Nov 10, 2002

I have two hyperlink fields in a table. I am trying to concatenate them in a report which is then output to html. The first field is the location of the ASP page on my website, "

http://www.mysite.com/results.asp?modcode=".

I then want to concatenate that value with the specific record which needs to query the online database, "jun0125". When clicking on the hyperlink, I want it to send the following string to the browser.

http://www.mysite.com/results.asp?modcode=jun0125.

When ever I try to concatenate the two fields, I get #error.

My blue-sky scenario would be to get rid of the first hyperlink field, "

http://www.mysite.com/results.asp?modcode="

and just have it set in the field control itself, instead of pulling it from the table. Then, concatenate that with the table value (jun0125).

Larry

danvlas · Nov 10, 2002

Sub YourTextBox_Click()
FollowHyperlink ""

http://www.mysite.com/results.asp?modcode="

& YourTextBox
End Sub

That should work...
[pipe]

Daniel Vlas
Systems Consultant
danvlas@yahoo.com

LarryDavidow · Nov 12, 2002

Hi Daniel

Thanks for the prompt reply.

As I can't set the event properties for a text box in a report, how should I use your code.

danvlas · Nov 14, 2002

Well, I think it's not possible to have the hyperlinks in the html output of the report, because:
1. Access converter does not add by default the necessary tags it the html file:
<a href="

http://www.whatever.com/page.html"></a>

and

2. (worse, IMO): if you try to add them yourself, the same Access html converter change < to &lt and > to &gt (wow, I'm having trouble doing so here)
Nice, isn't it?

You could however try to output the recordsource of the report and add the necessary tags to the output file. Here is a piece of code that takes a random table in your database and outputs it as a html file, then opens the file. You can modify it to get the query you need and output it with the hyperlinks. You'll probably have to modify the cases in bold letters to make it output the hyperlinks.
With a thorough analysis of your report and some HTML work, you could get a better result than the built-in converter.

Hope this helps a little...

Code:

Option Compare Database
Option Explicit

Sub HTMLOutput()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field
Dim filenumber As Integer
Dim PageName As String
Dim TableToOutput As Integer
Dim TableName As String

'get a random table from your database
Randomize
TableToOutput = (CurrentDb.TableDefs.Count + 1) * Rnd

'set the object variables
Set db = CurrentDb
TableName = CurrentDb.TableDefs(TableToOutput).Name
'define the recordset on the basis of the table
Set rst = db.OpenRecordset(&quot;Select * From [&quot; & TableName & &quot;]&quot;)

'get next available number to open a file
filenumber = FreeFile

'Let's define the page now
PageName = &quot;C:\My Documents\HTMLOutput.html&quot;

'open the file
Open PageName For Output As filenumber

'now let's generate the header code for the page
Print #filenumber, &quot;<html>&quot;
Print #filenumber, &quot;<head>&quot;
Print #filenumber, &quot;<title>&quot;
Print #filenumber, &quot;Danny - Table &quot; & TableName & &quot; automatic html output&quot;
Print #filenumber, &quot;</title>&quot;
Print #filenumber, &quot;</head>&quot;

'now the body (this will be a little more complicated)
Print #filenumber, &quot;<body>&quot;
Print #filenumber, &quot;<h1>Report data for table &quot; & TableName & &quot; for &quot; & Format(Date, &quot;dddd mmmm dd, yyyy&quot;) & &quot;</h1>&quot;
'let's get the field names
'create a table header
Print #filenumber, &quot;<table bgcolor=&quot; & &quot;&quot;&quot;&quot; & &quot;green&quot; & &quot;&quot;&quot;&quot; & &quot; border = 1>&quot;

'create a row
Print #filenumber, &quot;<tr>&quot;
'and populate the row with the field names
For Each fld In rst.Fields
Print #filenumber, &quot;<td>&quot; & fld.Name & &quot;</td>&quot;
Next
Print #filenumber, &quot;</tr>&quot;

'Now populate the table with data
While Not rst.EOF
Print #filenumber, &quot;<tr>&quot;
For Each fld In rst.Fields
Print #filenumber, &quot;<td>&quot;
    
'this is the section where you have to decide what to write to the table

Select Case fld.Name
'In my table, the field from has email addresses, so the protocol is mailto

Case "From"
Print #filenumber, "<a href=" & """" & "mailto:" & fld.Value & """" & ">Mail to " & fld.Value & "</a>"

'suppose you have a specific site address and the field only keeps the pages
Case "PageLink"
Print #filenumber, "<a href=" & """" & "

http://www.site.com/"

& fld.Value & """" & ">Jump to " & fld.Value & "</a>"

'suppose you have a list of sites you want to create hyperlinks to
Case "FullSite"
Print #filenumber, "<a href=" & """" & fld.Value & """" & ">Visit " & fld.Value & "</a>"

'in all other cases just write the value
'(you can extend this code for whatever you want to do)
Case Else

Code:

Print #filenumber, Nz(fld.Value, &quot;Nothing&quot;)
End Select
Print #filenumber, &quot;</td>&quot;
Next
Print #filenumber, &quot;</tr>&quot;
rst.MoveNext
Wend

'now close the html tags
Print #filenumber, &quot;</table>&quot;
Print #filenumber, &quot;<h2>That's all folks</h2><br>&quot;
Print #filenumber, &quot;Code written by Daniel Vlas:<a href=&quot; & &quot;&quot;&quot;&quot; & &quot;mailto:danvlas@IHATESPAMyahoo.com?Subject=HTML Output&quot; & &quot;&quot;&quot;&quot; & &quot;>Click to email me (remove IHATESPAM from the address)</a>&quot;
Print #filenumber, &quot;</body>&quot;
Print #filenumber, &quot;</html>&quot;
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
Close #filenumber
FollowHyperlink PageName
End Sub

Daniel Vlas
Systems Consultant
danvlas@yahoo.com

LarryDavidow · Nov 14, 2002

Thanks Dan

That looks workable.

I'll give it a try and let you know!!

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Concatenate two hyperlink fields in a report 1

LarryDavidow

Technical User

danvlas

Programmer

LarryDavidow

Technical User

danvlas

Programmer

LarryDavidow

Technical User

Similar threads

Part and Inventory Search

Sponsor