Reports second page is blank when writing to hml (cfm)

**wdrescher** · 10-15-2013, 06:10 AM

At work we have an old Access database that we open up and put employee training inside of it and hit "update intranet" and it sends it to our intranet as a .cfm file which we can then navigate to using selector boxes and find the information on the employee. Everything works as it should except when we view the html pages, if the employee has training that exceeds one page it gives us a blank page instead of showing the data on them employee. If I look at the reports inside Access I can click next on the page arrow and it splits the longer training in to multiple pages. I am no expert by far on Access and have been racking my brains on this project for far too long. Any help is much appreciated!

Here is the code that the update button has, maybe this will help:

Code:

Private Sub Command34_Click()Dim stDocName As String
Dim dbs As Database
Dim rst As Recordset
Dim mgrdbs As Database
Dim mgrrst As Recordset
Dim strSQL As String
Dim clocknum As String
Dim fullname As String
Dim manager As String
Dim TrainingDescription As String
Dim mgrdbstrn As Database
Dim mgrrsttrn As Recordset
Dim TrainingDescription2 As String




Dim qdf As QueryDef






stDocName = "Employee_Training"


On Error GoTo Main_GENTEMP_Err


    ' Qry_temptrain Clear
    DoCmd.OpenQuery "Qry_temptrain01", acNormal, acEdit
    DoCmd.OpenQuery "Qry_temptrain02", acNormal, acEdit
    DoCmd.OpenQuery "Qry_temptrain03", acNormal, acEdit
    DoCmd.OpenQuery "Qry_temptrain04", acNormal, acEdit
    DoCmd.OpenQuery "Qry_temptrain05", acNormal, acEdit
    DoCmd.OpenQuery "Qry_temptrain06", acNormal, acEdit


   '  setup the index.html file
   '
   '
Close #1
Open "\\bciappsrv1\trainingrecords\index.html" For Output As #1
Print #1, "<html>"
Print #1, ""
Print #1, "<head>"
Print #1, "<title>Training Records</title>"
Print #1, "</head>"
Print #1, ""
Print #1, "<body bgcolor=" & Chr(34) & "#FFFFFF" & Chr(34) & ">"
Print #1, "<script language=" & Chr(34) & "JavaScript" & Chr(34) & " type=" & Chr(34) & "text/javascript" & Chr(34) & ">"
Print #1, "<!--"
Print #1, "// original code by Bill Trefzger 12/12/96"
Print #1, "function go1(){"
Print #1, "if (document.selecter1.select1.options[document.selecter1.select1.selectedIndex].value != " & Chr(34) & "none" & Chr(34) & ") {"
Print #1, "location = document.selecter1.select1.options[document.selecter1.select1.selectedIndex].value"
Print #1, "     }"
Print #1, " }"
Print #1, "//-->"
Print #1, "<!--"
Print #1, "function go2(){"
Print #1, "if (document.selecter2.select1.options[document.selecter2.select1.selectedIndex].value != " & Chr(34) & "none" & Chr(34) & ") {"
Print #1, "location = document.selecter2.select1.options[document.selecter2.select1.selectedIndex].value"
Print #1, "     }"
Print #1, " }"
Print #1, "//-->"
Print #1, "<!--"
Print #1, "function go3(){"
Print #1, "if (document.selecter3.select1.options[document.selecter3.select1.selectedIndex].value != " & Chr(34) & "none" & Chr(34) & ") {"
Print #1, "location = document.selecter3.select1.options[document.selecter3.select1.selectedIndex].value"
Print #1, "     }"
Print #1, " }"
Print #1, "//-->"
Print #1, "function go4(){"
Print #1, "if (document.selecter4.select1.options[document.selecter4.select1.selectedIndex].value != " & Chr(34) & "none" & Chr(34) & ") {"
Print #1, "location = document.selecter4.select1.options[document.selecter4.select1.selectedIndex].value"
Print #1, "     }"
Print #1, " }"
Print #1, "//-->"
Print #1, "</script>"
Print #1, "<BR>"
Print #1, "<table border=" & Chr(34) & "0" & Chr(34) & ">"
Print #1, "    <tr>"
Print #1, "        <td>Select Employee</td>"
Print #1, "    </tr>"




Print #1, "    <tr><td>"
Print #1, "<script language=" & Chr(34) & "JavaScript" & Chr(34) & " type=" & Chr(34) & "text/javascript" & Chr(34) & ">"
Print #1, "<!-- "
Print #1, "document.write('<form name=" & Chr(34) & "selecter1" & Chr(34) & "><select name=" & Chr(34) & "select1" & Chr(34) & "onchange=" & Chr(34) & "go1()" & Chr(34) & ">');"
Print #1, "document.write('<option value=none>Training by Employee');"
Print #1, "document.write('<option value=none>--------------------');"
    ' Return reference to current database.
    Set dbs = CurrentDb
    strSQL = " SELECT Employee.[Clock Number], Employee.FName, Employee.Lname, Employee.Archive"
    strSQL = strSQL & " FROM Employee"
    strSQL = strSQL & " WHERE (((Employee.Archive)=No))"
    strSQL = strSQL & " ORDER BY Employee.Lname;"
    
    Set rst = dbs.OpenRecordset(strSQL)
' Start of Loop
   With rst
        Do While Not .EOF
         clocknum = ![Clock Number]
         fullname = !Lname & ", " & !FName
        ' fullname = StrReplace(fullname, "'", "&quot;")
        fullname = UCase(StrReplace(fullname, "'", " "))
Print #1, "document.write('<option value=" & Chr(34) & "http://oldintranet.net/BCIntranet/hr/trainingrecords/employees/" & clocknum & ".cfm" & Chr(34) & ">" & fullname & "');"
        'Call waitasec(1)
        'DoCmd.OpenReport stDocName, acPreview, , "[Clock Number] = " & clocknum
        'Call waitasec(1)
        'Reports![Employee_Training].Filter = "[Clock Number] = " & clocknum
        
        
'================================================
'
'  create query to output report in html format
'
'
'
    On Error Resume Next
    DoCmd.DeleteObject acQuery, "Qry_Employee_Training"
    On Error GoTo Main_GENTEMP_Err
    strSQL = "SELECT  Temptrain.LName, Temptrain.Overdue, Temptrain.Name, Temptrain.Title, Temptrain.JobTitleid, Departments.Department, Temptrain.[Training Description], Temptrain.[Clock Number], Managers.Manager, Temptrain.Date, Temptrain.IDisotraining, Temptrain.id"
    strSQL = strSQL & " FROM (Temptrain INNER JOIN Departments ON Temptrain.Departmentid = Departments.Departmentid) INNER JOIN Managers ON Temptrain.Managerid = Managers.Managerid"
    strSQL = strSQL & " WHERE (((Temptrain.[Clock Number])=" & clocknum & "));"
    ' Create new query.
    Set qdf = dbs.CreateQueryDef("Qry_Employee_Training", strSQL)
        
        
        DoCmd.OutputTo acOutputReport, stDocName, acFormatHTML, "\\bciappsrv1\trainingrecords\employees\" & clocknum & ".cfm", False, "\\ted\training\employees\employeetemplate.cfm"
        'Call waitasec(1)
        'DoCmd.close acReport, stDocName
        'DoCmd.OpenForm "Main"
        'Form.Refresh
        'Call waitasec(1)
        .MoveNext
        Loop
    End With
    rst.Close
    Set dbs = Nothing




'End of loop
Print #1, "document.write('</select>');"
'Print #1, "document.write('<input type=" & Chr(34) & "button" & Chr(34) & " value=" & Chr(34) & "go1" & Chr(34) & " onclick=" & Chr(34) & "go1()" & Chr(34) & ">');"
Print #1, "document.write('</form>');"
Print #1, "// end hiding contents -->"
Print #1, "</script>"
Print #1, "    </tr></td>"
'-------- end of writing employee files and the first employee drop down list -------




' start of 2nd employee number drop down list.
' start of 2nd employee number drop down list.
' start of 2nd employee number drop down list.
' start of 2nd employee number drop down list.
' start of 2nd employee number drop down list.


Print #1, "    <tr>"
Print #1, "        <td>Select clock number</td>"
Print #1, "    </tr>"




Print #1, "    <tr><td>"
Print #1, "<script language=" & Chr(34) & "JavaScript" & Chr(34) & " type=" & Chr(34) & "text/javascript" & Chr(34) & ">"
Print #1, "<!-- "
Print #1, "document.write('<form name=" & Chr(34) & "selecter3" & Chr(34) & "><select name=" & Chr(34) & "select1" & Chr(34) & "onchange=" & Chr(34) & "go3()" & Chr(34) & ">');"
Print #1, "document.write('<option value=none>Training by Clock Number');"
Print #1, "document.write('<option value=none>--------------------');"
    ' Return reference to current database.
    Set dbs = CurrentDb
    strSQL = " SELECT Employee.[Clock Number], Employee.FName, Employee.Lname, Employee.Archive"
    strSQL = strSQL & " FROM Employee"
    strSQL = strSQL & " WHERE (((Employee.Archive)=No))"
    strSQL = strSQL & " ORDER BY Employee.[Clock Number];"
    
    Set rst = dbs.OpenRecordset(strSQL)
   With rst
        Do While Not .EOF
         clocknum = ![Clock Number]
         fullname = !Lname & ", " & !FName
        fullname = UCase(StrReplace(fullname, "'", " "))
Print #1, "document.write('<option value=" & Chr(34) & "http://oldintranet.net/BCIntranet/hr/trainingrecords/employees/" & clocknum & ".cfm" & Chr(34) & ">" & clocknum & " - " & fullname & "');"
    On Error Resume Next
    DoCmd.DeleteObject acQuery, "Qry_Employee_Training"
    On Error GoTo Main_GENTEMP_Err
    strSQL = "SELECT  Temptrain.LName, Temptrain.Overdue, Temptrain.Name, Temptrain.Title, Temptrain.JobTitleid, Departments.Department, Temptrain.[Training Description], Temptrain.[Clock Number], Managers.Manager, Temptrain.Date, Temptrain.IDisotraining, Temptrain.id"
    strSQL = strSQL & " FROM (Temptrain INNER JOIN Departments ON Temptrain.Departmentid = Departments.Departmentid) INNER JOIN Managers ON Temptrain.Managerid = Managers.Managerid"
    strSQL = strSQL & " WHERE (((Temptrain.[Clock Number])=" & clocknum & "));"
    ' Create new query.
    Set qdf = dbs.CreateQueryDef("Qry_Employee_Training", strSQL)
        
        
         .MoveNext
        Loop
    End With
    rst.Close
    Set dbs = Nothing




'End of loop
Print #1, "document.write('</select>');"
Print #1, "document.write('</form>');"
Print #1, "// end hiding contents -->"
Print #1, "</script>"
Print #1, "    </tr></td>"
' end of 2nd employee number drop down list.












'*************************************************************************************


Print #1, "    <tr><td>Select Manager</td></tr>"


Print #1, "    <tr><td>"
Print #1, "<script language=" & Chr(34) & "JavaScript" & Chr(34) & " type=" & Chr(34) & "text/javascript" & Chr(34) & ">"
Print #1, "<!-- "
Print #1, "document.write('<form name=" & Chr(34) & "selecter2" & Chr(34) & "><select name=" & Chr(34) & "select1" & Chr(34) & "onchange=" & Chr(34) & "go2()" & Chr(34) & ">');"
Print #1, "document.write('<option value=none>Employees by Manager');"
Print #1, "document.write('<option value=none>--------------------');"
    ' Return reference to current database.
    Set mgrdbs = CurrentDb
    strSQL = " SELECT Managers.Managerid, Managers.Manager FROM Managers ORDER BY Managers.Manager;"
    Set mgrrst = mgrdbs.OpenRecordset(strSQL)
' Start mgr Loop
        With mgrrst
         Do While Not .EOF
manager = StrReplace(!manager & "", " ", "")
            Print #1, "document.write('<option value=" & Chr(34) & "http://oldintranet.net/BCIntranet/hr/trainingrecords/employees/" & manager & ".cfm" & Chr(34) & ">" & !manager & "');"
        'DoCmd.OpenReport stDocName, acPreview, , "[manager] = " & Chr(34) & !manager & Chr(34)
        'Reports![Employee_Training].Filter = "[manager] = " & Chr(34) & !manager & Chr(34)
        
    On Error Resume Next
    DoCmd.DeleteObject acQuery, "Qry_Employee_Training"
    On Error GoTo Main_GENTEMP_Err
    strSQL = "SELECT  Temptrain.LName, Temptrain.Overdue, Temptrain.Name, Temptrain.Title, Temptrain.JobTitleid, Departments.Department, Temptrain.[Training Description], Temptrain.[Clock Number], Managers.Manager, Temptrain.Date, Temptrain.IDisotraining, Temptrain.id"
    strSQL = strSQL & " FROM (Temptrain INNER JOIN Departments ON Temptrain.Departmentid = Departments.Departmentid) INNER JOIN Managers ON Temptrain.Managerid = Managers.Managerid"
    strSQL = strSQL & " WHERE (([manager]='" & !manager & "'));"
    ' Create new query.
    Set qdf = mgrdbs.CreateQueryDef("Qry_Employee_Training", strSQL)
        
        
        
        manager = UCase(StrReplace(manager, "'", " "))
        
        DoCmd.OutputTo acOutputReport, stDocName, acFormatHTML, "\\bciappsrv1\trainingrecords\employees\" & manager & ".cfm", , "\\bciappsrv1\trainingrecords\employees\employeetemplate.cfm"
        DoCmd.Close acReport, stDocName
            
            .MoveNext
        Loop
    End With
    mgrrst.Close
    Set mgrdbs = Nothing
'End mgr loop
Print #1, "document.write('</select>');"
'Print #1, "document.write('<input type=" & Chr(34) & "button" & Chr(34) & " value=" & Chr(34) & "go2" & Chr(34) & " onclick=" & Chr(34) & "go2()" & Chr(34) & ">');"
Print #1, "document.write('</form>');"
Print #1, "// end hiding contents -->"
Print #1, "</script>"
Print #1, "    </tr></td>"
'-------- end of writing Mgr select list




'*************************************************************************************


Print #1, "    <tr><td>Select by Training</td></tr>"


Print #1, "    <tr><td>"
Print #1, "<script language=" & Chr(34) & "JavaScript" & Chr(34) & " type=" & Chr(34) & "text/javascript" & Chr(34) & ">"
Print #1, "<!-- "
Print #1, "document.write('<form name=" & Chr(34) & "selecter4" & Chr(34) & "><select name=" & Chr(34) & "select1" & Chr(34) & "onchange=" & Chr(34) & "go4()" & Chr(34) & ">');"
Print #1, "document.write('<option value=none>Qualified for:');"
Print #1, "document.write('<option value=none>--------------------');"
    ' Return reference to current database.
    Set mgrdbstrn = CurrentDb
    ' Qualified
   ' strSQL = "SELECT [ISO Required Training Descriptions].[Training Description], [ISO Required Training Descriptions].IDisotraining, Count([Training Subjects].IDisotraining) AS CountOfIDisotraining FROM [ISO Required Training Descriptions] INNER JOIN [Training Subjects] ON [ISO Required Training Descriptions].IDisotraining = [Training Subjects].IDisotraining GROUP BY [ISO Required Training Descriptions].[Training Description], [ISO Required Training Descriptions].IDisotraining HAVING (((Count([Training Subjects].IDisotraining))>0));"
    strSQL = "SELECT [ISO Required Training Descriptions].[Training Description], [ISO Required Training Descriptions].IDisotraining, Count([Training Subjects].IDisotraining) AS CountOfIDisotraining, Count([Employee Training].IDtraining) AS CountOfIDtraining FROM [Employee Training] INNER JOIN ([ISO Required Training Descriptions] INNER JOIN [Training Subjects] ON [ISO Required Training Descriptions].IDisotraining = [Training Subjects].IDisotraining) ON [Employee Training].IDtrainsubject = [Training Subjects].IDtrainsubject WHERE ((([ISO Required Training Descriptions].[Training Description]) Like 'Qualified*')) GROUP BY [ISO Required Training Descriptions].[Training Description], [ISO Required Training Descriptions].IDisotraining HAVING (((Count([Training Subjects].IDisotraining))>=1) AND ((Count([Employee Training].IDtraining))>=1));"
    Set mgrrsttrn = mgrdbstrn.OpenRecordset(strSQL)
' Start mgr Loop
        With mgrrsttrn
         Do While Not .EOF
TrainingDescription = StrReplace(![Training Description] & "", " ", "")
TrainingDescription = StrReplace(TrainingDescription & "", ":", "")
TrainingDescription = StrReplace(TrainingDescription & "", """", "")
TrainingDescription = StrReplace(TrainingDescription & "", "&", "")
TrainingDescription = StrReplace(TrainingDescription & "", "/", "-")
TrainingDescription = StrReplace(TrainingDescription & "", ")", "")
TrainingDescription = StrReplace(TrainingDescription & "", "(", "")
TrainingDescription = StrReplace(TrainingDescription & "", ",", "")
TrainingDescription = StrReplace(TrainingDescription & "", "'", " ")
TrainingDescription = StrReplace(TrainingDescription & "", "%", " ")
TrainingDescription2 = StrReplace(![Training Description] & "", " ", "-")
TrainingDescription2 = StrReplace(TrainingDescription2 & "", "&", "")
TrainingDescription2 = StrReplace(TrainingDescription2 & "", "'", "")
TrainingDescription2 = StrReplace(TrainingDescription2 & "", """", " ")
TrainingDescription2 = StrReplace(TrainingDescription2 & "", ",", "-")
TrainingDescription2 = StrReplace(TrainingDescription2 & "", "%", "-")


            Print #1, "document.write('<option value=" & Chr(34) & "http://oldintranet.net/BCIntranet/hr/trainingrecords/employees/" & TrainingDescription & ".cfm" & Chr(34) & ">" & TrainingDescription2 & "');"


        
    On Error Resume Next
    DoCmd.DeleteObject acQuery, "Qry_Employee_Training0"
    On Error GoTo Main_GENTEMP_Err
    strSQL = "SELECT [ISO Required Training Descriptions].[Training Description], [ISO Required Training Descriptions].IDisotraining, [Training Subjects].IDtrainsubject, Employee.[Clock Number], [Employee Training].Date, Employee.FName, Employee.Lname FROM Employee INNER JOIN (([ISO Required Training Descriptions] INNER JOIN [Training Subjects] ON [ISO Required Training Descriptions].IDisotraining = [Training Subjects].IDisotraining) INNER JOIN [Employee Training] ON [Training Subjects].IDtrainsubject = [Employee Training].IDtrainsubject) ON Employee.[Clock Number] = [Employee Training].[Clock Number] WHERE ((([ISO Required Training Descriptions].IDisotraining)=" & !IDisotraining & "));"
    ' Create new query.
    Set qdf = mgrdbstrn.CreateQueryDef("Qry_Employee_Training0", strSQL)
        
        
        
        
         stDocName = "RPT_Employee_Training0"
        DoCmd.OutputTo acOutputReport, stDocName, acFormatHTML, "\\bciappsrv1\trainingrecords\employees\" & TrainingDescription & ".cfm", , "\\bciappsrv1\trainingrecords\employees\employeetemplate.cfm"
        DoCmd.Close acReport, stDocName
            
            .MoveNext
        Loop
    End With
    mgrrsttrn.Close
    Set mgrdbstrn = Nothing
'End mgr loop
Print #1, "document.write('</select>');"
'Print #1, "document.write('<input type=" & Chr(34) & "button" & Chr(34) & " value=" & Chr(34) & "go4" & Chr(34) & " onclick=" & Chr(34) & "go4()" & Chr(34) & ">');"
Print #1, "document.write('</form>');"
Print #1, "// end hiding contents -->"
Print #1, "</script>"
Print #1, "    </tr></td>"
'-------- end of writing Mgr select list




'*************************************************************************************


Print #1, "</table>"
Print #1, ""
Print #1, "<p>&nbsp;</p>"
Print #1, "</body>"
Print #1, "</html>"


Close #1
   
MsgBox "Finished"


Main_GENTEMP_Exit:
    On Error Resume Next
    DoCmd.DeleteObject acQuery, "Qry_Employee_Training"
    Exit Sub




Main_GENTEMP_Err:
    MsgBox Error$
    
    Resume Main_GENTEMP_Exit
    Resume
End Sub


Private Sub Form_Load()


SetOption "Confirm Action Queries", "0"
SetOption "Confirm Record Changes", "0"
SetOption "Confirm Document Deletions", "0"
SetOption "Show Hidden Objects", "0"
SetOption "Show System Objects", "0"
SetOption "Show Startup Dialog Box", "0"
End Sub
Private Sub Command36_Click()
On Error GoTo Err_Command36_Click


    Dim stDocName As String
    Dim stLinkCriteria As String


    stDocName = "Qry_Append_Blank_Training"
    DoCmd.OpenQuery stDocName, acNormal, acEdit


    stDocName = "frm-EmployeeTraining"
    DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria, acFormAdd


Exit_Command36_Click:
    Exit Sub


Err_Command36_Click:
    MsgBox Err.Description
    Resume Exit_Command36_Click
    
End Sub
Private Sub Command38_Click()
On Error GoTo Err_Command38_Click


    Dim stDocName As String


    stDocName = "Qry_Append_Blank_Training"
    DoCmd.OpenQuery stDocName, acNormal, acEdit


Exit_Command38_Click:
    Exit Sub


Err_Command38_Click:
    MsgBox Err.Description
    Resume Exit_Command38_Click
    
End Sub

Result of code:

Click image for larger version.

Name: Screen Shot 2013-10-15 at 8.11.41 AM.png
Views: 9
Size: 69.7 KB
ID: 14058

**NTC** · 10-15-2013, 07:52 AM

Within Access; if a report content is too wide for the 8x11 standard print paper size and the report is optioned for print preview mode (as opposed to report view mode)...then on screen when it displays (and when it prints) it will auto generate an additional page for every page...this additional page may display the edge of the content that was too wide but often is a blank page as the content is only a little bit too wide and doesn't spill over onto this 2nd page. this is very common in report design and the solution is to squeeze the content together and not infringe onto the margins (or reduce margin size) and that 2nd page goes away....

Thats what can be done within Access - and if Access is sending 2 page objects to your web code is perhaps your fix.....however if your web code is generating 2 pages when Access definitely is sending only 1 - then you'll need to seek advice from a specialist in that code type......

**ItsMe** · 10-15-2013, 07:58 AM

I did not look at the code posted. Based on your comment only, it sounds like your HTML template is hardcoded in a way that does not allow for the growing numbers of records.

If, in fact, this is the problem you could try "shrinking" the number if records the query returns. I am not sure what the business rules are. Maybe the records are greater in number because of dates.

Make a copy of the DB and try adjusting the query for the report. Adjust the query in a way it returns fewer records. Have it return records so it fits on one page for instance. Check the report without exporting to HTML. Maybe then it will fit into the hardcoded template.

I mention this as a way to test where the problem is. A way to test without going into writing new HTML. Afterwards, you can make a decision on what to do and address the business rules.

**wdrescher** · 10-15-2013, 08:05 AM

Originally Posted by ItsMe

I did not look at the code posted. Based on your comment only, it sounds like your HTML template is hardcoded in a way that does not allow for the growing numbers of records.

If, in fact, this is the problem you could try "shrinking" the number if records the query returns. I am not sure what the business rules are. Maybe the records are greater in number because of dates.

Make a copy of the DB and try adjusting the query for the report. Adjust the query in a way it returns fewer records. Have it return records so it fits on one page for instance. Check the report without exporting to HTML. Maybe then it will fit into the hardcoded template.

I mention this as a way to test where the problem is. A way to test without going into writing new HTML. Afterwards, you can make a decision on what to do and address the business rules.

Great idea, I will test this theory out and see if that works - so basically instead of it sending out 100 which lets say 80 is the max for one page - I can change the items to under 100 or let's say 50 and it will render the pages right?

**ItsMe** · 10-15-2013, 08:09 AM

Just to test a theory, yes

Reports second page is blank when writing to hml (cfm)

Thread Tools

Reports second page is blank when writing to hml (cfm)

Similar Threads

Extra Blank Page in Report

File I/O - Writing page headers.

Blank Pages in my Reports

Writing ASP into a Data Access Page

REPORTS BLANK PAGES/I can't remember

Tags for this Thread

Posting Permissions