| Feedback | Report templates | Drill down web pages | |
This routine anticipates a basic knowledge of VBA and the HTML code used for creating tables (i.e. using <tr> and <td> tags for rows and columns).
Access can create web pages but sometimes a home made program is preferable if you want flexibility and to incorporate it in automated processes. A program can read records from a query and output them into a text file with HTML coding.
The HTML code for the example shown below was created in Access using VBA and includes some limited formatting using CSS styles to give the appearance of an Access query in table view. It can be adapted to incorporate your own document formats.
PNPCat | Description | AltExpCat | Description | amount |
OPINC | Other Operating Income | ALL-OTH-INC | All Other Income | -725,580.00 |
OPINC | Other Operating Income | COMM_CATER | Commercial - Catering | -48,113.00 |
OPINC | Other Operating Income | COMM-ACCOM | Commercial - Accommodation | -28,081.00 |
OPINC | Other Operating Income | NONPAT-SERVS | Non Patient Services | -803,543.00 |
INVESTREV | Investment Revenue | INT-RECV | Interest Receivable | -727.00 |
PAY | Pay | ADMIN-AGY | A&C/Sen Man Agency | 13,600.00 |
PAY | Pay | ADMIN-BNK | A&C/Sen Man Bank | 91,004.00 |
PAY | Pay | ADMIN-SUBST | A&C/Sen Man Substantive | 2,377,739.00 |
PAY | Pay | CONSULT | Consultants | 1,930,710.00 |
The following VBA code has been created in order to be easily adpted for displaying a selected query. The series of variables in the first part of the program should enable the code to be easily re-used. It is currently defined to display up to 12 fields. The query field names, titles (or captions) and number formats are all stored in 3 arrays.
Sub MakeHTM() ' Create an HTML web page for a selected Access query Dim MyDb As Database, MySet As Recordset, ExportFilename As String, PgDesc As String Dim FldName(12) As String, FldTitle(12) As String, FldForm(12) As String Dim Hex1 As String, Hex2 As String, PgTitle As String, FldCount As Integer Dim Section1Row As Integer, FldStr As String, FldAlign As String, QryName As String Dim TDefLoop As QueryDef, x As Integer, FldFound As Byte ExportFilename = "c:\mydata\expgrpdata.htm" PgTitle = "Expenditure Data" QryName = "ExpendGrpData" Hex1 = "#F5F5F0" ' set the colours for alternate table rows Hex2 = "#FFFFFF" ' #F5F5F0 pale grey, #FFFFFF white, #D8E4BC green ' these Field Names must exist in the specified query ' the QFieldNames program can populate these field names FldName(1) = "" FldName(2) = "" FldName(3) = "" FldName(4) = "" FldName(5) = "" FldName(6) = "" FldName(7) = "" FldName(8) = "" FldName(9) = "" FldName(10) = "" FldName(11) = "" FldName(12) = "" ' titles (captions) are optional - if not defined, the Field names will be used FldTitle(1) = "" FldTitle(2) = "" FldTitle(3) = "" FldTitle(4) = "" FldTitle(5) = "" FldTitle(6) = "" FldTitle(7) = "" FldTitle(8) = "" FldTitle(9) = "" FldTitle(10) = "" FldTitle(11) = "" FldTitle(12) = "" ' Field format - t=Text, otherwise use format codes ' for numbers and dates e.g. #,##0.00 OR dd mmm yy FldForm(1) = "t" FldForm(2) = "t" FldForm(3) = "t" FldForm(4) = "t" FldForm(5) = "t" FldForm(6) = "t" FldForm(7) = "t" FldForm(8) = "t" FldForm(9) = "t" FldForm(10) = "t" FldForm(11) = "t" FldForm(12) = "t" Set MyDb = CurrentDb() Set MySet = MyDb.OpenRecordset(QryName, dbOpenDynaset) Set TDefLoop = MyDb.QueryDefs(QryName) PgDesc = "MakeHTM() Query: [" & QryName & "]" Debug.Print Time(), PgDesc; " as " & ExportFilename For n = 1 To 12 If FldName(n) = "" Then FldCount = n - 1 Exit For End If ' --- READ QUERY PROPERTIES AND VERIFY THE FIELD NAMES --- FldFound = 0 For x = 0 To TDefLoop.Fields.Count - 1 If FldName(n) = TDefLoop.Fields(x).Name Then FldFound = 1 Exit For End If Next x If FldFound = 0 Then MsgBox "The fieldname " & FldName(n) & " was not found in the query definition", vbOKOnly, "Program STOPPED" Debug.Print Time(), "ERROR FldName(" & Format(n, "0") & ")='" & FldName(n) & "' not found" MySet.Close GoTo MyEndBit End If If FldTitle(n) = "" Then FldTitle(n) = FldName(n) Next n Open ExportFilename For Output As #1 ' open a new file which will become the HTML page ' start writing the HTML code for the web page Print #1, "<html><head>" Print #1, "<title>" & PgDesc & "</title>" Print #1, "<style type='text/css'>" Print #1, "body { font-family: Arial, Helvetica; font-size: 11pt; margin-left: 40; margin-right: 40 }" Print #1, "p { margin-top: 4; margin-bottom: 4; font-size: 11pt;}" Print #1, "h1 { color: #FF0000;font-family: Arial; font-size: 18pt; font-weight: bold; margin-top: 8; margin-bottom: 8 }" Print #1, "td {padding: 1pt 3pt 2pt 3pt; border:1px solid white; font-size: 9pt}" Print #1, "table {border-collapse: collapse; border:1px solid white;}" Print #1, "td.edge {text-align:center; font-size:10pt; font-weight: bold; text-align: center; background-color:#EFEBDE; border:1px solid black; border-left-width: 0px; border-top-width: 0px;}" Print #1, "td.r1r {border:1px solid silver; border-left-width: 0px;border-top-width: 0px; padding: 4px; background-color:" & Hex1 & "}" Print #1, "td.r2r {border:1px solid silver; border-left-width: 0px;border-top-width: 0px; padding: 4px; background-color:" & Hex2 & "}" Print #1, ".MyFooter { font-family: Arial; font-size: 8pt; font-variant: small-caps; text-transform: uppercase; color: #0F5BB9; margin-top: -2 }" Print #1, "</style><META name='author' content='MeadInKent.co.uk'>" Print #1, "</head><body>" Print #1, "<table width='100%'><tr><td width='90%' rowspan='2'><h1>" & PgTitle & "</h1></td>" Print #1, "<td bgcolor='#0F5BB9' align='center'><font color='#FFFFFF'>Finance Systems</font></td></tr><tr>" Print #1, "<td align='center'><font color='#0F5BB9'><span style='white-space:nowrap'>Organisation Name</span></font></td>" Print #1, "</tr></table><br>" Print #1, "<table width='80%'><tr>" ' table header titles For n = 1 To FldCount Print #1, "<td class='edge'>" & FldTitle(n) & "</td>" Next Print #1, "</tr>" MySet.MoveFirst Do Until MySet.EOF ' loop through the query records ----------------------------------------- If Section1Row = 1 Then ' switch between 1 and 0 to alternate colours Section1Row = 0 TempClass = " class='r1r" Else Section1Row = 1 TempClass = " class='r2r" End If Print #1, "<tr>" For n = 1 To FldCount If FldForm(n) = "t" Then FldAlign = "" FldStr = MySet.Fields(FldName(n)) Else FldAlign = "' align='right" FldStr = Format(MySet.Fields(FldName(n)), FldForm(n)) End If Print #1, "<td " & TempClass & FldAlign & "'>" & FldStr & "</td>" Next n Print #1, "</tr>" MySet.MoveNext Loop ' ----------- end of record loop -------------------- Print #1, "</table><br><br>" ' footer message at end of page Print #1, "<p>Created in the '" & MyDb.Name & "' Access database showing query [<b>" & MySet.Name & "</b>].</p>" Print #1, "<p><small>Page name <b>" & ExportFilename & "</b>. Time created: " & Format(Now(), "hh:mm dd mmm yy") & "</small></p>" Print #1, "</body></html>" MsgBox "The page has been created", vbOKOnly + vbInformation, "HTM" Debug.Print Time(), "HTML file built and saved" MySet.Close MyEndBit: Close #1 End Sub Sub QFieldNames() ' list the fields in a query Dim MyDb As Database, TDefLoop As QueryDef, n As Integer Dim MyQname As String ' CHOOSE variable name hard coded or MsgBox prompt for name MyQname = "ExpendGrpData" ' MyQname = MsgBox("Query name", vbOKOnly, "QFieldNames()") Set MyDb = CurrentDb() Set TDefLoop = MyDb.QueryDefs(MyQname) Debug.Print "List of fields in '" & TDefLoop.Name & "'" For n = 0 To TDefLoop.Fields.Count - 1 ' CHOOSE simple list of names or FldName() variable defns ' Debug.Print TDefLoop.Fields(n).Name Debug.Print "FldName(" & Format(n + 1, "#") & ")=" & Chr(34) & TDefLoop.Fields(n).Name & Chr(34) Next n End Sub |
This program creates a complete standalone web page, displaying the query contents.
It may be useful to incorporate a query with a small number of records into an existing web page using iFrames.
file: awebpage.htm | © MeadInKent.co.uk 2014 | Page last updated Apr14 |