HTML Template

I need to create some HTML Templates to export some reports from Access.  I have searched the net and Microsoft's site to find some pre-formed templates that I can take and customize myself but I have not been able to find any.  Where can I get some templates from?
LVL 1
scottspiveyAsked:
Who is Participating?
 
BigSiCommented:
Sorry for not getting back to you:

Say you have a table called SomeTable which contains a hyperlink field (but is set as text data type). You could run the following query to return the hyperlinks fields:

 Select someTextDataTypeURLField From SomeTable

Now, all you then all you need to do is create a new table called Hyperlinks or something like that which has a field called HyperlinkValue (datatype of hyperlink). Then, create a query in SQL view and use:-

INSERT INTO Hyperlinks ( HyperlinkValue )
SELECT SomeTable.TextDataTypeURLField
FROM SomeTable;

this way you are inserting whatevery your query returns to the new table you created, converting the datatype from text to hyperlink.

-Si.
0
 
BigSiCommented:
Hi Scott,

I am not aware of any sample templates you can download, but if you are fimiliar with coding basic web pages in HTML then it should be quite straight forward creating your own template.

I beleive an access html template just needs to contain the following line of code:
<!--AccessTemplate_Body-->
where you want the content of your query/report to go.

To export a query using a button, you can use the following code:-

------------------------------------------------------
Private Sub cmdSomeButton_Click()
DoCmd.OutputTo acOutputQuery, "[YOUR QUERY NAME]", acFormatHTML, "C:\SomePathToYourHTMLOutputFile.html", True, "C:\PathToYourTemplateDocument\Template.htm"
End Sub

------------------------------------------------------

Now, your Template document could contain code something like:-

<html>
<head>
<title>HTML PAGE TITLE HERE</title>
</head>
<body>
Query output within a table
<table border="1">
<tr>
<td>
<!--AccessTemplate_Body-->
</td>
</tr>
</table>
</body>
</html>

Another alternative is perhaps try and create a template in word and then edit the source code in notepad to include the:
<!--AccessTemplate_Body-->
..line of code.


Hope this helps.
-Si.
0
 
scottspiveyAuthor Commented:
but if i go this route then teh <!--Access Template_Body--> is always going to be the standard format that Access applies to the data when it is exported.  Right?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
BigSiCommented:
I'm assuming you wanted your reports saved as a .html file. Using the <!--Access Template_Body--> you can basically sandwich your query or report within a header and footer coded in HTML so you can basically 'brand up' your reports.

Are you just looking for general access report templates/samples then?

-Si.
0
 
scottspiveyAuthor Commented:
BigSi,

I think I want them saved as .html, but I don't know for sure.  The report I am exporting contains URLs that I need to be able to click on to analyze documents and data.  To me, exporting them to HTML was the most obvious choice to accomplish this task.  I can live with the format that Access exports the docs to but they are not user friendly at all.  I have tried reformatting the report in Access and then exporting it but a lot of the formatting is loss during export.  So that is why I was hoping to create an HTML template.  But if all I can add is a header and footer then that doesn't do me much good.
0
 
BigSiCommented:
Scott,

Could you provide some background to what this report contains - I'm not sure if your report is being produced for your use only in order to analyze data or if for example, you want to put data on a standard format (.html) that anyone can open  for emailing out or saving on a web server.

At the moment I am getting the impression that these reports were created using the standard report wizzard - is this the case and if so have you tried creating a report using the Design View?


-Si.
0
 
scottspiveyAuthor Commented:
BigSi,

I did create the report using the wizard.  I have not found Access to be real user friendly in the report creating department so the wizard is the quickest way for me to get a report created without pulling out the few remaining hairs I have.

My report is generated off of a number of queries that I run to extract URLs from various tables I have linked into Access.  Here is a sample of the data from one of the queries:

LocalFilePath
U:\NetworkFolder\OEPProjects\download\CommonwealthCore\www.alliancebernstein.com\aspnet_client\system_web\1_1_4322\SmartNav.js#U:\NetworkFolder\OEPProjects\download\CommonwealthCore\www.alliancebernstein.com\aspnet_client\system_web\1_1_4322\SmartNav.js#      

URL
http://www.alliancebernstein.com/aspnet_client/system_web/1_1_4322/SmartNav.js#http://www.alliancebernstein.com/aspnet_client/system_web/1_1_4322/SmartNav.js#

The LocalFilePath points to a location on my server where we have downloaded a specific document.  The URL is the web address that the document was downloaded.  The links that are produces by this query are links that have no match in a master db table that we are comparing everything to.  So I have set the query up to take the link that is in the linked table, display it in its original form and then put it inside # # so that the report and the ensuing HTML export will recongnie the link as a valid URL.

What I then use the exported HTML file for is to manually click through each of the links to see what the document is and then determine why it is not contained in our master db table.  So my statement that we analyze data and documents is probabaly a little misleading.  We are checking the links that are displayed in the report to determine why they are not part of our master db table.

Maybe this helps explain a little better what I am trying to do.  And maybe there is a system I can use other than an HTML export to accomplish this goal.
0
 
BigSiCommented:
Thanks for the background info Scott.

The html template that access optionally requests when exporting a report is just something so you can add a constant look to your exported report e.g by adding your company logo at the top of the page and usign specific fonts.

With regards to displaying hyperlinks on exported access queries (I'm assuming this is what you are looking to achieve now), I had a look at my version of access (2003) and noticed that you can define a hyperlink data type for a field. I'm not to sure if the hyperlink data type is available in older versions of Access and if this is the version that you are using, but hopefully this may be of some use. Basically try creating a new table with the field
ID (autonumber)
TextURL (text)
HyperlinkURL (hyperlink)

Then populate this table with sample data such as:-
ID  TextURL                                             HyperlinkURL
1   http://www.experts-exchange.com       http://www.experts-exchange.com
2   http://www.someURL.com                   http://www.someURL.com


Then create a report that selects these field and shows this information in the report.

Then export the report as a html file (skipping the html template option)

What you should notice is that the TextURL field in not hyperlinked in the exported file and that the HyperlinkURL field is.

Also, if you look at your data in the table, you should see the HyperlinkURL field is automatically hyperlinked (perhaps you may prefer to check the hyperlinks directly from the access table?)

Now, what you could also do, is modify your existing query so that it outputs your current text based  hyperlinks into a field that is uses the Hyperlink data type.

SI.

(sorry if i've gone off on one now, but this answer is now assuming your question is in trying to create clickable hyperlinks in an exported report or table - I have a feeling I have the wrong end of the stick though here)

 


0
 
scottspiveyAuthor Commented:
BigSi,

you are correct that I am trying to create clickable hyperlinks in an exported report.  i will go through the steps you outlined above and post the results when i get finished.

scott
0
 
BigSiCommented:
Great - with any luck the solution above should help you then, the only thing I'm not sure about is if versions of Access prior to 2003 have the hyperlink data type.

-Si.
0
 
scottspiveyAuthor Commented:
i am using 2003 so i should be fine.
0
 
BigSiCommented:
on another note - if you are considering converting your existing URL fields from text to Hyperlink then it's probably a good idea to back up the datbase just in case it causes any unforeseen consequences (it should do, but worth doing just in case!).
0
 
BigSiCommented:
sorry - that was a typo, supposed to have read:-
(it should'NT do....)
0
 
scottspiveyAuthor Commented:
i will do that b4 i get started.  it is probably going take me a while to build this report in design view since i have always avoided desiging reports like the plaque.
0
 
scottspiveyAuthor Commented:
BigSi,

ok i just reread your earlier post and realized what you are saying.  you are saying to rebuild the "table" with the fields you have above and then export that into a report.  the issue here is that for the types of queries i am running, with LEFT JOIN, the data type cannot by a hyperlink.  if it is then the LEFT JOIN query will not work.  so i had to convert my data to text in the advanced properties for my table links so that my queries would work the way i need them to.  that is why my query has the url in text and the exact same url between the "#" signs so when i export the report to HTML, the URL is recognized as an actualy URL.

scott
0
 
BigSiCommented:
I think that problem could be overcome by updating the query to insert the newly created hyperlinks into a temporary table of which to output your text based URL into a field defined as a hyperlink.

I will try and post an example query that does this tommorow.

-Si.
0
 
scottspiveyAuthor Commented:
ok i hadn't thought of that.  thx.
0
 
scottspiveyAuthor Commented:
BigSi,

I will give this a try and let you know how it turns out.  It may be a week or more before I get to it though.  I have a lot of projects going right now and this one has gotten pushed to the side.

Thx.
0
 
BigSiCommented:
That's ok - I know what that's like!

Hope it works out for you when/if you get the time to do it.

-Si.
0
 
jjafferrCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
    Accept: BigSi {http:#16698451}

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

jjafferr
EE Cleanup Volunteer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.