MeadInKent
| Feedback | HTML pages with hidden sections |

Create web pages with hidden sections from an Access query

For information about the HTML code needed to build these web pages click here

Other pages on this site show examples of creating HTML web pages to display the contents of a query. The pages can be built using a VBA program or using Access Export options.

This chapter describes the creation of special HTML pages which use a short JavaScript code to either hide or display sections of the data. The user can toggle the display of sections within the web page to hide or reveal some of the query rows.


Balance sheet code structure

Finance Systems
MeadInKent Healthcare

Click on toggle button to expand detail section.

Toggle bs2 bs2desc bs1 bs1desc
050 FIXED TOTAL FIXED ASSETS 012 BLDG PFI *BUILDINGS PFI
050 FIXED TOTAL FIXED ASSETS 015 DWEL PUR *DWELLINGS PURCHASED
050 FIXED TOTAL FIXED ASSETS 020 EQUIP PUR *EQUIPMENT PURCHASED


The VBA program requires a source query which contains a mix of summarised (or header) records and detailed records. The following Select Query called ExpendGrpData contains the fields [MySort], [PNPCat], [MainDesc] and [MainGrpSum] which can be grouped to display a summarised view. The [AltExpCat], [SubDescr] and [Amount] fields provide a secondary level of detail within each category.

mysort PNPCat MainDesc AltExpCat SubDescr amount maingrpsum
D03 OPINC Other Operating Income ALL-OTH-INC All Other Income -725,580.00 -2,403,488
D03 OPINC Other Operating Income COMM_CATER Commercial - Catering -48,113.00 -2,403,488
D03 OPINC Other Operating Income COMM-ACCOM Commercial - Accommodation -28,081.00 -2,403,488
D03 OPINC Other Operating Income EDUCATION Education Training & Research -714,093.00 -2,403,488
D05 PAY Pay ADMIN-SUBST A&C/Sen Man Substantive 2,377,739.00 14,617,216
D05 PAY Pay CONSULT Consultants 1,930,710.00 14,617,216

This link opens the generated web page containing the full query contents, the detail of which is hidden in the and the expanding sections.

The following program builds a web page which can contain a maximum of 6 fields in the main table and 6 in the sub (expanding) section. Each field must be defined and given a format code for defining the appearance of numbers and dates. A variable (called JoinFld) must be defined to contain the name of the field which triggers a new detailed section to be generated, whenever it changes.

Sub HTM_exp()
' Create an HTML web page for an Access query
' Records in a second sub section can be expanded or hidden

Dim MyDb As Database, MySet As Recordset, QryName As String
Dim T1FldName(6) As String, T2FldName(6) As String
Dim T1FldForm(6) As String, T2FldForm(6) As String
Dim T1FldCount As Integer, T2FldCount As Integer
Dim Section1Row As Integer, Section2Row As Integer, TempClass As String
Dim ExpandTop As Integer, ExportFileName As String, TblWidth As String
Dim PgTitle As String, CurrentElement As String, JoinFld As String
Dim Hex1 As String, Hex2 As String, Hex3 As String, ProgName As String

ExportFileName = "c:\mydata\ExpGrpDataExpand.htm"
ProgName = "HTM_exp"
PgTitle = "Expenditure Data"
QryName = "ExpendGrpData"

Hex1 = "#F5F5F0" ' set the colours for alternate table rows
Hex2 = "#D8E4BC" ' #F5F5F0 pale grey, #FFFFFF white, #D8E4BC green
TblWidth = "800px"

' these Field Names must all exist in the specified query
' table 1 is the main table and table 2 contains the detailed rows (up to 6 flds in each)
T1FldName(1) = "mysort"
T1FldName(2) = "mainelemcode"
T1FldName(3) = "maindescr"
T1FldName(4) = "maingrpsum"
T1FldName(5) = ""
T1FldName(6) = ""
T2FldName(1) = "subelem"
T2FldName(2) = "subdescr"
T2FldName(3) = "amount"
T2FldName(4) = ""
T2FldName(5) = ""
T2FldName(6) = ""
JoinFld = "mysort" ' a hidden table 2 will be created for each change in value of JoinFld

' Field format - t=Text, otherwise use format codes
' for numbers and dates e.g. #,##0.00 OR dd mmm yy
T1FldForm(1) = "t"
T1FldForm(2) = "t"
T1FldForm(3) = "t"
T1FldForm(4) = "#,##0"
T1FldForm(5) = "t"
T1FldForm(6) = "t"
T2FldForm(1) = "t"
T2FldForm(2) = "t"
T2FldForm(3) = "#,##0"
T2FldForm(4) = "t"
T2FldForm(5) = "t"
T2FldForm(6) = "t"
' <<< e n d o f v a r i a b l e d e f i n i t i o n s >>>

Set MyDb = CurrentDb()
Set MySet = MyDb.OpenRecordset(QryName, dbOpenDynaset)
Debug.Print Time(), ProgName & "() Query: [" & QryName & "] as " & ExportFileName

For n = 1 To 6
If T1FldName(n) = "" Then
T1FldCount = n - 1
Exit For
End If
Next n
For n = 1 To 6
If T2FldName(n) = "" Then
T2FldCount = n - 1
Exit For
End If
Next n

For n = 6 To 1 Step -1
If T1FldName(n) = "" Then
T1FldCount = n - 1
End If
If T2FldName(n) = "" Then
T2FldCount = n - 1
End If
Next n

Open ExportFileName For Output As #1 ' open a new text file - the HTML page
Print #1, "<html><head>" ' *** HTML code for the page - including the CSS to define appearance ***
Print #1, "<title>Access Query: [" & QryName & "]</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:#FFFFFF}"
Print #1, "td.r3r {border:1px solid silver; border-left-width: 0px;border-top-width: 0px; padding: 4px; background-color:" & Hex2 & "}"
Print #1, "</style></head><!-- original code created by MeadInKent.co.uk -->"

Print #1, "<body><script type='text/javascript'>" ' a Javascript to hide or expand a page section
Print #1, "function toggleMe(a){"
Print #1, "var e=document.getElementById(a);"
Print #1, "if(!e)return true;"
Print #1, "if(e.style.display=='none'){"
Print #1, "e.style.display=''}" ' NOTE display=block does not work in Chrome
Print #1, "else{e.style.display='none'}"
Print #1, "return true;}"
Print #1, "</script>"

Print #1, "<table width='100%'>" ' *** page header ***
Print #1, "<tr><td width='90%' rowspan='2'><h1>" & PgTitle & "</h1></td>"
Print #1, "<td bgcolor='#0F5BB9' align='center'><font color='#FFFFFF'>Finance Systems</font></td>"
Print #1, "</tr><tr>"
Print #1, "<td align='center'><font color='#0F5BB9'><nobr>MeadInKent Healthcare</nobr></font></td>"
Print #1, "</tr></table><p>Click on toggle button to expand detail section.</p>"


Print #1, "<table width='" & TblWidth & "'><tr><td width='60px'>Toggle</td>"
For n = 1 To T1FldCount ' *** titles of first table ***
Print #1, "<td class='edge'>" & T1FldName(n) & "</td>"
Next
Print #1, "</tr>"

CurrentElement = "x"
ExpandTop = 0

MySet.MoveFirst
Do Until MySet.EOF ' ----------- loop through the query records --------------

' *** a new sub/2nd table is created on each change in [JoinFld] ***
If MySet.Fields(JoinFld) <> CurrentElement Then
CurrentElement = MySet.Fields(JoinFld)

If ExpandTop = 0 Then ' end the 2nd table unless it is the first line in table 1
ExpandTop = 1
Else
Print #1, "</table></td></tr>"
End If

If Section1Row = 1 Then ' switch between 1 and 0 to alternate row colours on table 1
Section1Row = 0
TempClass = " class='r1r"
Else
Section1Row = 1
TempClass = " class='r2r"
End If

' print a row of table 1 (specifying the Toggle Javascript code)
Print #1, "<tr><td width='60px'><input type='button' onclick='return toggleMe(" & Chr(34) & CurrentElement & Chr(34) & ")' value='Toggle'></td>"
For n = 1 To T1FldCount
If T1FldForm(n) = "t" Then
Print #1, "<td " & TempClass & "'>" & MySet.Fields(T1FldName(n)) & "</td>"
Else
Print #1, "<td " & TempClass & "' align='right'>" & Format(MySet.Fields(T1FldName(n)), T1FldForm(n)) & "</td>"
End If
Next
Print #1, "</tr>"

' *** a new table 2 header ***
Print #1, "<tr id='" & CurrentElement & "' style='display:none'><td width='60px'>&nbsp;</td>"
Print #1, "<td colspan='" & T1FldCount & "'> <table width='95%'>"
Print #1, "<tr><td width='5%'>&nbsp;</td>"
For n = 1 To T2FldCount
Print #1, "<td class='edge'>" & T2FldName(n) & "</td>"
Next
Print #1, "</tr>"

End If ' *** end of section for table 1 row and table 2 header ***

If Section2Row = 1 Then ' switch between 1 and 0 to alternate colours on 2nd table
Section2Row = 0
TempClass = " class='r2r"
Else
Section2Row = 1
TempClass = " class='r3r"
End If

Print #1, "<tr><td>&nbsp;</td>"
For n = 1 To T2FldCount ' *** print a row of the second table ***
If T2FldForm(n) = "t" Then
Print #1, "<td " & TempClass & "'>" & MySet.Fields(T2FldName(n)) & "</td>"
Else
Print #1, "<td " & TempClass & "' align='right'>" & Format(MySet.Fields(T2FldName(n)), T2FldForm(n)) & "</td>"
End If
Next n
Print #1, "</tr>"

MySet.MoveNext
Loop ' ------------- end of loop through query records ----------

Print #1, "</table></td></tr></table><br>"
Print #1, "<p><small>Produced in the '" & CurrentDb.Name & "' Access database using query '" & QryName & "'. VBA program name '" & ProgName & "()'.</small></p>"
Print #1, "<p><small>Page name <b>" & ExportFileName & "</b>. Created at " & Format(Now(), "hh:mm dd mmm yy") & "</small></p>"
Print #1, "</body></html>"

MsgBox "The page has been created" & vbCrLf & ExportFileName, vbOKOnly + vbInformation, ProgName
MySet.Close
Close #1
End Sub

The detailed records will be hidden by default and will only show when the user clicks on the [Toggle] buttons.

Once the query has been built and a program saved, the process can quickly be called upon to generate an updated page whenever the data changes.


file: expandp1.htm MeadInKent.co.uk 2014 View a selection of recommended books on Access Last updated May14