|
|
|||
| | Feedback | Report templates | Drill down web pages | | |||
This routine anticipates a basic knowledge of HTML code used for creating tables (i.e. using <tr> and <td> markers for rows and columns). It also requires knowledge of reading from and printing data to text files.
Access can create web pages but sometimes a home made routine is preferable if you (i) want flexibility (ii) don't like the large extravagant HTML files generated by Office applications (iii) didn't understand the Access help instructions.
The example shown below includes sub totals for each specified grouping of data and some limited formatting of the tables using CSS styles. It would need to be adapted for your own query, field, grouping and total variable names. Because the output file is completely self contained and has its own formatting instructions, it can be shared with anyone who has a web browser.
Would you like to learn more?
Access 2003 Professional Results
Create web page tables with expanding or drill down sections
| Sub MakeHTMvacancies() '* Create an HTML page displaying the query: Vacancies Dim MyDB As Database, MySet As Recordset Dim MyFileName As String, MyCurrentGroup As Stringe ' declare variables for running totals of numeric fields Dim Var As Single, Bud As Single, Wkd As Single, Con As Single, Vac As Single DoCmd.Hourglass True Set MyDB = CurrentDb() Set MySet = MyDB.OpenRecordset("vacancies", dbOpenDynaset) MyFileName = "R:\vacancies.htm" Open MyFileName For Output As #1 ' define the formats and colours for your web page Print #1, "<html><head>" Print #1, "<title>Database Utility from www.MeadInKent.co.uk</title>" Print #1, "<style type='text/css'>" Print #1, "body {font-family: Arial, Helvetica; margin-left: 40; margin-right: 40}" Print #1, "h1 {color: 'green'; font-size: 12pt; font-weight: bold; margin-top: 8; margin-bottom: 4}" Print #1, "td {padding: 1pt 3pt 2pt 3pt; border-style: solid; border-width: 1}" Print #1, "table {border-collapse: collapse; font-size: 10pt; border: 2 solid white; width: 100%}" Print #1, ".MyFooter {font-size: 8pt; font-variant: small-caps; text-transform: uppercase; color: #0F5BB9; margin-top: -2}" Print #1, "</style></head><body>" Print #1, "<div style='float: right;'>Confidential Information</div>" Print #1, "<h1>Vacancy report<h1>" MySet.MoveFirst MyCurrentGroup = "x" ' on each change of the field MySet!WholeDescAbbrev a ' total row is generated and a new table is started Do Until MySet.EOF ' start of loop for each record If MyCurrentGroup <> MySet!WholeDescAbbrev Then ' if a new grouping then ... If MyCurrentGroup <> "x" Then ' add totals and finish table Print #1, "<tr><td>=</td>" Print #1, "<td><b>Total</b></td>" Print #1, "<td>" & Format(Bud, "#,##0.00") & "</td>" Print #1, "<td>" & Format(Wkd, "#,##0.00") & "</td>" Print #1, "<td>" & Format(Con, "#,##0.00") & "</td>" Print #1, "<td>" & Format(Vac, "#,##0.00") & "</td>" Print #1, "<td><b>" & Format(Var, "£#,##0") & "</b></td></tr>" Print #1, "</table><br>" Bud = 0 ' reset the temporary running total variables Wkd = 0 Con = 0 Vac = 0 Var = 0 End If MyCurrentGroup = MySet!WholeDescAbbrev ' define a new table Print #1, "<h1>" & MySet!WholeDescAbbrev & " (" & MySet!RegionDesc & ")</h1>" Print #1, "<table><colgroup>" ' apply formats to each of the columns Print #1, "<col style='background-color: #FFFFCC'></col> <col></col>" Print #1, "<col align='right'></col> <col align='right'></col>" Print #1, "<col align='right'></col> <col align='right'></col>" Print #1, "<col style='background-color: #E7E7E7' align='right'></col></colgroup>" Print #1, "<tr><td>code</td> <td>code descr</td>" ' table column headings Print #1, "<td>Budget</td> <td>Worked</td> <td>Contracted</td> <td>Vacant</td>" Print #1, "<td>Variance</td> </tr>" End If Print #1, "<tr><td>" & MySet!GLCode & "</td>" ' add data to a table row Print #1, "<td>" & MySet!Descr & "</td>" Print #1, "<td>" & Format(MySet!BudWTE, "#,##0.00") & "</td>" Print #1, "<td>" & Format(MySet!ActWTE, "#,##0.00") & "</td>" Print #1, "<td>" & Format(MySet!ContWTE, "#,##0.00") & "</td>" Print #1, "<td>" & Format(MySet!Vacant, "#,##0.00") & "</td>" Print #1, "<td>" & Format(MySet!CumVar, "£#,##0") & "</td></tr>" Bud = Bud + MySet!BudWTE ' update the variables containing running totals Wkd = Wkd + MySet!ActWTE Con = Con + MySet!ContWTE Vac = Vac + MySet!Vacant Var = Var + MySet!CumVar MySet.MoveNext Loop Print #1, "<tr><td>=</td>" ' finish the final table after all records reported Print #1, "<td><b>Total</b></td>" Print #1, "<td>" & Format(Bud, "#,##0.00") & "</td>" Print #1, "<td>" & Format(Wkd, "#,##0.00") & "</td>" Print #1, "<td>" & Format(Con, "#,##0.00") & "</td>" Print #1, "<td>" & Format(Vac, "#,##0.00") & "</td>" Print #1, "<td><b>" & Format(Var, "£#,##0") & "</b></td></tr>" Print #1, "</table><br><hr>" Print #1, "<p class='myfooter'>[End] Report created " & Format(Date, "dd mmm yy") & ". Page created by 'MakeHTMvacancies' program 10/04 CM</p>" Print #1, "</body></html>" Close #1 MySet.Close DoCmd.Hourglass False MsgBox "An HTML file has been saved as " & MyFileName, vbOKOnly, "MakeHTMvacancies()" End Sub |
This program creates a report like that shown below.
|
Confidential Information
VACANCY REPORTR & D MANAGEMENT (Clinical governance)
POST GRADUATE CENTRE (Med Education)
|
The example shown above creates a page of data. Access can also be used to generate pages containing links to numbers of report files. This can save a lot of time if you use Access to generate large numbers of (monthly) reports and then create an index or contents web page containing hyperlinks to each report. The program would need to be adapted so that instead of adding values inside <td> table cells </td>, it provides the values for the <a> hyperlink file names </a>.
| file: awebpage.htm | © MeadInKent.co.uk 2012 | Page last updated Oct 06 |