Output Text String in Correct HTML Format 3

[LarryDavidow]

I want to output a text string from a query into a report which, when exported to HTML, will format correctly as a link.

Here are my variables.

Website Link:

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

modcode is my query field.

I have tried many variables of the following.

field: &quot;<a href=&quot; & &quot;&quot;

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

[modcode]&quot;&quot; & &quot;>Click Here For Image</a>&quot;

Any help would be much appreciated.

 

[VBslammer]

See if this will run:

field: &quot;<a href='[URL unfurl="true"]http://www.mysite.com/results.asp?modcode=&quot;[/URL] & [modcode] & &quot;'>Click Here For Image</a>&quot;

VBSlammer

Unemployed in Houston, Texas

 

[LarryDavidow]

Nah

This was the output once the report was exported to HTML:

<a or when the source code is viewed, <A HREF=&quot;&quot;>&lt;a

The link referred to file:///c:/documents and settings/user/my documents (physical location of the database)

 

[VBslammer]

I wasn't sure what you were trying to do, but if you're trying to export an access report as .html you can't preserve HTML text in the conversion. However, Access will properly convert a valid hyperlink object (not text) in the report.

In order for a text field to export properly as a hyperlink, it has to have its control source property assigned to a valid hyperlink field, its 'IsHyperlink' property has to be 'Yes,' and you can't use any aggregate functions to manipulate it. That means the only way to alter it at runtime so it will still work is if you actually change the underlying record in the table at runtime (DAO?) to reflect the url you want, which surely isn't what you want to do.

If your query provides you with the base address and subaddress information, it would be easier to use a Label control for you link. Then you only have to set 2 properties for the Label at runtime to get the desired results:

RUNTIME
1. Set the Caption property which will be the link's display text.

2. Set the Hyperlink Address property.

DESIGN TIME
1. You have to add at least one character to the Label's 'Hyperlink Address' property at design-time to force Access into adding a hyperlink to the Label, otherwise when you try to alter it at runtime the hyperlink object for the Label will not exist.

For example, if your query returns a hyperlink field named [MyURL] that gives the base address of [ignore]'

http://www.mysite.com/'

[/ignore] and another field named [modCode] that gives 'AA9999' all you have to do is set the address during the report section's Detail_Format() event like this:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  Me.lblLink.Hyperlink.Address = HyperlinkPart([MyURL], acAddress) & _
                "results.asp?modcode=" & [modCode]
  Me.lblLink.Caption = HyperlinkPart([MyURL], acDisplayText)
  If Me.lblLink.Caption = "" Then
    Me.lblLink.Caption = "Click Me"
  End If
End Sub

If your query returns Text fields only, then you can just drop the HyperlinkPart() method:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  Me.lblLink.Hyperlink.Address = [MyURL] & "results.asp?modcode=" & [modCode]
  Me.lblLink.Caption = "Click Me"
End Sub

The best way to do this would be to use a database-driven website and use ASP to get at the data.
VBSlammer

Unemployed in Houston, Texas

 

[danvlas]

VBSlammer, have a star from me.
I guess I couldn't see the forest because of the trees.

Here's what I did after reading:
Placed a long label in the detail section
Caption: http://
Hyperlink property: http://

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Label24.Hyperlink.Address = [ignore]&quot;

http://www.whatever.net/&quot;

& pagelink[/ignore]
Label24.Caption = Label24.Hyperlink.Address
End Sub

And indeed it kept the hyperlink when exported to HTML.

Looks so simple now , much simpler than my idea to 'dynamically create a static html' from a query.

Cheers



Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[LarryDavidow]

You guys are awesome!!

Thank you thank you thank you!!

One more minor detail...

Is there anyway to format the hyperlink so that it shows &quot;Click Here For Image&quot; instead of the full link (

http://www.mysite.com/search.asp?modcode=AA9999)

 

[VBslammer]

The label's caption can be whatever you want:

Me.lblLink.Caption = "Click Here for Image"

VBSlammer

Unemployed in Houston, Texas

 

[LarryDavidow]

Thank You!!

Duh!! I tried to edit the value in the properties control instead of the VB script!

I noticed that when the report is output to HTML, it creates a table for every record on the report. This leads to pretty messy formatting.

Do you have any suggestions as to how I can have the results formatted a little better.

I played around with the template function, but couldn't quite get it to place the individual records into one table.

Let me know if you have any ideas...

Larry

 

[VBslammer]

Let's see your code... VBSlammer

Unemployed in Houston, Texas

 

[LarryDavidow]

Here it is.

The only values I set in the template file was <base target=&quot;_new&quot;> for hyperlinks to open in new window...

<HTML>
<HEAD>
<META HTTP-EQUIV=&quot;Content-Type&quot; CONTENT=&quot;text/html;charset=windows-1252&quot;>
<TITLE>OutputModels1</TITLE>
<base target=&quot;_new&quot;>
</HEAD>
<BODY><TABLE BORDER=0 CELLSPACING=0 CELLPADDING=0 >
<TR HEIGHT=40 >
<TD WIDTH=4 ALIGN=LEFT > <BR></TD><TD WIDTH=868 ALIGN=CENTER ><FONT style=FONT-SIZE:28pt FACE=&quot;Haettenschweiler&quot; COLOR=#000000>Compull</FONT></TD>
</TR>
</TABLE>
<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=0 >
<TR HEIGHT=21 >
<TD WIDTH=4 ALIGN=LEFT > <BR></TD><TD WIDTH=232 ALIGN=LEFT ><FONT style=FONT-SIZE:14pt FACE=&quot;Haettenschweiler&quot; COLOR=#000000>Name</FONT></TD>
<TD WIDTH=64 ALIGN=LEFT ><FONT style=FONT-SIZE:14pt FACE=&quot;Haettenschweiler&quot; COLOR=#000000>Height</FONT></TD>
<TD WIDTH=69 ALIGN=RIGHT ><FONT style=FONT-SIZE:14pt FACE=&quot;Haettenschweiler&quot; COLOR=#000000>Birthdate</FONT></TD>
<TD WIDTH=11 ALIGN=LEFT > <BR></TD><TD WIDTH=40 ALIGN=LEFT ><FONT style=FONT-SIZE:14pt FACE=&quot;Haettenschweiler&quot; COLOR=#000000>Sex</FONT></TD>
<TD WIDTH=68 ALIGN=LEFT ><FONT style=FONT-SIZE:14pt FACE=&quot;Haettenschweiler&quot; COLOR=#000000>Race</FONT></TD>
<TD WIDTH=88 ALIGN=LEFT ><FONT style=FONT-SIZE:14pt FACE=&quot;Haettenschweiler&quot; COLOR=#000000>HairColor</FONT></TD>
<TD WIDTH=88 ALIGN=LEFT ><FONT style=FONT-SIZE:14pt FACE=&quot;Haettenschweiler&quot; COLOR=#000000>EyeColor</FONT></TD>
<TD WIDTH=544 ALIGN=LEFT ><FONT style=FONT-SIZE:14pt FACE=&quot;Haettenschweiler&quot; COLOR=#000000>Click to view image</FONT></TD>
</TR>
</TABLE>
<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=0 >
<TR HEIGHT=14 >
<TD WIDTH=4 ALIGN=LEFT > <BR></TD><TD WIDTH=296 ALIGN=LEFT ><B><FONT style=FONT-SIZE:8pt FACE=&quot;Arial&quot; COLOR=#000000>Jarrett Hall</FONT></B></TD>
<TD WIDTH=80 ALIGN=LEFT ><FONT style=FONT-SIZE:8pt FACE=&quot;Arial&quot; COLOR=#000000>10/23/1999</FONT></TD>
<TD WIDTH=36 ALIGN=CENTER ><FONT style=FONT-SIZE:8pt FACE=&quot;Arial&quot; COLOR=#000000>M</FONT></TD>
<TD WIDTH=4 ALIGN=LEFT > <BR></TD><TD WIDTH=68 ALIGN=LEFT ><FONT style=FONT-SIZE:8pt FACE=&quot;Arial&quot; COLOR=#000000>White</FONT></TD>
<TD WIDTH=88 ALIGN=LEFT ><FONT style=FONT-SIZE:8pt FACE=&quot;Arial&quot; COLOR=#000000>Brown</FONT></TD>
<TD WIDTH=88 ALIGN=LEFT ><FONT style=FONT-SIZE:8pt FACE=&quot;Arial&quot; COLOR=#000000>Brown</FONT></TD>
<TD WIDTH=544 ALIGN=LEFT ><U><FONT style=FONT-SIZE:8pt FACE=&quot;Arial&quot; COLOR=#0000ff><A HREF=&quot;

http://www.jrtalent.net/emailresults.asp?ImageFront=JarrettHall.jpg&quot;>Click

Here</A></FONT></U></TD>
</TR>
</TABLE>
<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=0 >
<TR HEIGHT=14 >
<TD WIDTH=4 ALIGN=LEFT > <BR></TD><TD WIDTH=296 ALIGN=LEFT ><B><FONT style=FONT-SIZE:8pt FACE=&quot;Arial&quot; COLOR=#000000>Zach Hall</FONT></B></TD>
<TD WIDTH=80 ALIGN=LEFT ><FONT style=FONT-SIZE:8pt FACE=&quot;Arial&quot; COLOR=#000000>7/21/1997</FONT></TD>
<TD WIDTH=36 ALIGN=CENTER ><FONT style=FONT-SIZE:8pt FACE=&quot;Arial&quot; COLOR=#000000>M</FONT></TD>
<TD WIDTH=4 ALIGN=LEFT > <BR></TD><TD WIDTH=68 ALIGN=LEFT ><FONT style=FONT-SIZE:8pt FACE=&quot;Arial&quot; COLOR=#000000>White</FONT></TD>
<TD WIDTH=88 ALIGN=LEFT ><FONT style=FONT-SIZE:8pt FACE=&quot;Arial&quot; COLOR=#000000>Brown</FONT></TD>
<TD WIDTH=88 ALIGN=LEFT ><FONT style=FONT-SIZE:8pt FACE=&quot;Arial&quot; COLOR=#000000>Brown</FONT></TD>
<TD WIDTH=544 ALIGN=LEFT ><U><FONT style=FONT-SIZE:8pt FACE=&quot;Arial&quot; COLOR=#0000ff><A HREF=&quot;

http://www.jrtalent.net/emailresults.asp?ImageFront=ZacharyHall.jpg&quot;>Click

Here</A></FONT></U></TD>
</TR>
</TABLE>
<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=0 >
<TR HEIGHT=14 >
<TD WIDTH=4 ALIGN=LEFT > <BR></TD><TD WIDTH=232 ALIGN=LEFT ><B><FONT style=FONT-SIZE:8pt FACE=&quot;Arial&quot; COLOR=#000000>Thais Gonzalez</FONT></B></TD>
<TD WIDTH=64 ALIGN=LEFT ><FONT style=FONT-SIZE:8pt FACE=&quot;Arial&quot; COLOR=#000000>29.5</FONT></TD>
<TD WIDTH=80 ALIGN=LEFT ><FONT style=FONT-SIZE:8pt FACE=&quot;Arial&quot; COLOR=#000000>4/24/1998</FONT></TD>
<TD WIDTH=36 ALIGN=CENTER ><FONT style=FONT-SIZE:8pt FACE=&quot;Arial&quot; COLOR=#000000>F</FONT></TD>
<TD WIDTH=4 ALIGN=LEFT > <BR></TD><TD WIDTH=68 ALIGN=LEFT ><FONT style=FONT-SIZE:8pt FACE=&quot;Arial&quot; COLOR=#000000>White</FONT></TD>
<TD WIDTH=88 ALIGN=LEFT ><FONT style=FONT-SIZE:8pt FACE=&quot;Arial&quot; COLOR=#000000>Dk. Blond</FONT></TD>
<TD WIDTH=88 ALIGN=LEFT ><FONT style=FONT-SIZE:8pt FACE=&quot;Arial&quot; COLOR=#000000>Grey</FONT></TD>
<TD WIDTH=544 ALIGN=LEFT ><U><FONT style=FONT-SIZE:8pt FACE=&quot;Arial&quot; COLOR=#0000ff><A HREF=&quot;

http://www.jrtalent.net/emailresults.asp?ImageFront=&quot;>Click

Here</A></FONT></U></TD>
</TR>
</TABLE>
<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=0 >
<TR HEIGHT=14 >
<TD WIDTH=4 ALIGN=LEFT > <BR> <BR></TD><TD WIDTH=1204 ALIGN=LEFT ><FONT style=FONT-SIZE:8pt FACE=&quot;Arial&quot; COLOR=#000000>Sunday, November 17, 2002</FONT></TD>
</TR>
</TABLE>
</BODY>
</HTML>

 

[VBslammer]

Unfortunately, that's how Access formats report output. Normally, though, the results are fairly close to the Access report. In your case, I see two problems.

1. You need to make sure the controls on your report are accurately sized to the fields they contain. (Select a control and click 'Format -> Size -> Fit' to see how the control looks when auto-sized). The hyperlink control was formatted with a width of 544 which was way too large and caused the other cells to shift left. Also make sure none of the controls overlap on your report.

2. Handle null values by adding 'N/A' or similar text so those fields get generated in the HTML. Your null fields were skipped in the table definitions, making the formatting look even worse.


That's about all you can do as far as I know. The export conversion in Access offers little control to a developer, and the 'Template' features are very basic.

VBSlammer

Unemployed in Houston, Texas