| | Feedback | HTML pages with hidden sections | | ||
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 |
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'> </td>"
Print #1, "<td
colspan='" & T1FldCount & "'> <table width='95%'>"
Print #1, "<tr><td
width='5%'> </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> </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 |  |
Last updated May14 |