MeadInKent
| Feedback | Report templates | Drill down web pages |

A program to generate a web page from an Access query

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.

Access 2010 VBA Programming

 
UK more ...   USA more ...
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