none
Subreport resize bug in Access 2013 RRS feed

  • Question

  • I had a working report in Access 2000, 2002, and 2003 (at least).  I just tried running it in Access 2013 and it hangs indefinitely.  After several hours of troubleshooting, I managed to isolate and reliably reproduce the error.  I offer the following Minimal, Complete, and Verifiable Example of the problem. 

    I dare say this is a bug in the Access engine.  I would love to be proved wrong.

    To reproduce, open a new blank database in Access 2013.  It can be any format, 2000 mdb; 2002-2003 mdb; or 2007-2013 accdb.  Then copy and paste the code below into a new standard code module.  Run the procedure to reproduce the problem:

    Sub ShowcaseSubreportResizeBug()
        'Create a simple table to act as Recordsource
        CurrentDb.Execute "SELECT 1 AS ID INTO Mcve"
        CurrentDb.Execute "INSERT INTO Mcve(ID) Values (2)"
    
        'Create a subreport bound to our simple table
        Dim SubRpt As Report, SRName As String
        Set SubRpt = CreateReport
        SubRpt.RecordSource = "SELECT TOP 2 * FROM Mcve"
        SRName = SubRpt.Name
        DoCmd.Save , SRName
        DoCmd.Close acReport, SRName
        Set SubRpt = Nothing
    
        'Create the main report...
        Dim Rpt As Report, RptName As String, SRCtlName As String
        Set Rpt = CreateReport
        RptName = Rpt.Name
        '...add the subreport control...
        With CreateReportControl(RptName, acSubform, acDetail)
            .SourceObject = "Report." & SRName
            SRCtlName = .Name
        End With
        '...change the size of the subreport control in code...
        Rpt.Section(acDetail).OnFormat = "[Event Procedure]"
        Rpt.HasModule = True
        Rpt.Module.InsertLines Rpt.Module.CountOfLines, vbCrLf & _
                                                        "Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)" & vbCrLf & _
                                                        "    Me.Child0.Height = 0.1" & vbCrLf & _
                                                        "End Sub"
        '...save and close the main report
        DoCmd.Save , RptName
        DoCmd.Close acReport, RptName
        Set Rpt = Nothing
        
        'Preview the newly created report (this will cause msaccess.exe to stop responding)
        MsgBox "Click OK to observe MS Access hanging when trying to open report, '" & RptName & "'."
        DoCmd.OpenReport RptName, acViewPreview
    End Sub

    Friday, September 11, 2015 9:08 PM

Answers

  • I had a working report in Access 2000, 2002, and 2003 (at least).  I just tried running it in Access 2013 and it hangs indefinitely.  After several hours of troubleshooting, I managed to isolate and reliably reproduce the error.  I offer the following Minimal, Complete, and Verifiable Example of the problem. 

    I dare say this is a bug in the Access engine.  I would love to be proved wrong.

    To reproduce, open a new blank database in Access 2013.  It can be any format, 2000 mdb; 2002-2003 mdb; or 2007-2013 accdb.  Then copy and paste the code below into a new standard code module.  Run the procedure to reproduce the problem:

    Sub ShowcaseSubreportResizeBug()
        'Create a simple table to act as Recordsource
        CurrentDb.Execute "SELECT 1 AS ID INTO Mcve"
        CurrentDb.Execute "INSERT INTO Mcve(ID) Values (2)"
    
        'Create a subreport bound to our simple table
        Dim SubRpt As Report, SRName As String
        Set SubRpt = CreateReport
        SubRpt.RecordSource = "SELECT TOP 2 * FROM Mcve"
        SRName = SubRpt.Name
        DoCmd.Save , SRName
        DoCmd.Close acReport, SRName
        Set SubRpt = Nothing
    
        'Create the main report...
        Dim Rpt As Report, RptName As String, SRCtlName As String
        Set Rpt = CreateReport
        RptName = Rpt.Name
        '...add the subreport control...
        With CreateReportControl(RptName, acSubform, acDetail)
            .SourceObject = "Report." & SRName
            SRCtlName = .Name
        End With
        '...change the size of the subreport control in code...
        Rpt.Section(acDetail).OnFormat = "[Event Procedure]"
        Rpt.HasModule = True
        Rpt.Module.InsertLines Rpt.Module.CountOfLines, vbCrLf & _
                                                        "Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)" & vbCrLf & _
                                                        "    Me.Child0.Height = 0.1" & vbCrLf & _
                                                        "End Sub"
        '...save and close the main report
        DoCmd.Save , RptName
        DoCmd.Close acReport, RptName
        Set Rpt = Nothing
        
        'Preview the newly created report (this will cause msaccess.exe to stop responding)
        MsgBox "Click OK to observe MS Access hanging when trying to open report, '" & RptName & "'."
        DoCmd.OpenReport RptName, acViewPreview
    End Sub

    Try the following code.  I think you'll find that it works now.  Your main problem is that you need to set the CanGrow property of the SubForm to False.  Also, as Dirk mentioned, you need to set the size value in twips.  There are a couple of other little issues, but the main ones are solved.

    Sub ShowcaseSubreportResizeBug()
        
        Dim rpt1 As Report
        Dim rpt2 As Report
        Dim ctl1 As Control
        Dim strReport1 As String
        Dim strReport2 As String
        Dim strControl As String
        
        'Create a simple table to act as Recordsource
        CurrentDb.Execute "SELECT 1 AS ID INTO Mcve"
        CurrentDb.Execute "INSERT INTO Mcve(ID) Values (2)"
    
        'Create a subreport bound to our simple table
        Set rpt2 = CreateReport
        rpt2.RecordSource = "SELECT TOP 2 * FROM Mcve"
        strReport2 = rpt2.Name
        DoCmd.Close acReport, strReport2, acSaveYes
    
        'Create the main report...
        Set rpt1 = CreateReport
        strReport1 = rpt1.Name
        '...add the subreport control...
        Set ctl1 = CreateReportControl(strReport1, acSubform, acDetail)
        With ctl1
            .SourceObject = "Report." & strReport2
            .CanGrow = False
            strControl = .Name
        End With
        
        '...change the size of the subreport control in code...
        rpt1.Section(acDetail).OnFormat = "[Event Procedure]"
        'rpt1.HasModule = True
    
        rpt1.Module.InsertLines rpt1.Module.CountOfLines, vbCrLf & _
        "Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)" & vbCrLf & _
        "    Me.Child0.Height = 2*1440" & vbCrLf & _
        "End Sub"
        
        '...save and close the main report
        DoCmd.Close acReport, strReport1, acSaveYes
        
        'Preview the newly created report (this will cause msaccess.exe to stop responding)
        MsgBox "Click OK to observe MS Access hanging when trying to open report, '" & strReport1 & "'."
        DoCmd.OpenReport strReport1, acViewPreview
        
        Set rpt1 = Nothing
        Set rpt2 = Nothing
        Set ctl1 = Nothing
        
    End Sub


    • Edited by RunningManHD Tuesday, September 15, 2015 5:57 AM
    • Proposed as answer by David_JunFeng Monday, September 21, 2015 9:12 AM
    • Marked as answer by mwolfe02 Thursday, October 22, 2015 8:11 PM
    Tuesday, September 15, 2015 5:55 AM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Office, your issue need some VBA code, I'll move your question to the MSDN forum for Access

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=accessdev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    George Zhao
    TechNet Community Support


    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs.

    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.
    Monday, September 14, 2015 6:53 AM
  • Not sure why you set this property

    Rpt.HasModule = True

    I don't see a purpose in that.

    Other than that, without testing it seems strange to me to ask for a top two when there are only 2. I wouldn't be surprised that that cannot be resolved by Access as you are asking Access to compare more records than exist.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Tuesday, September 15, 2015 2:59 AM
  • Without actually running your demo (it's late, where I am), I'm struck by this line of code you are adding to the Main report's Detail_Format event:

        Me.Child0.Height = 0.1

    In VBA, control heights and widths are specified in twips (1440 twips to the inch).  So what height is that line trying to set?  Does it still hang if you change that line to:

        Me.Child0.Height = 1440

    or

        Me.Child0.Height = 144

    ?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Proposed as answer by David_JunFeng Monday, September 21, 2015 9:12 AM
    Tuesday, September 15, 2015 4:09 AM
  • I had a working report in Access 2000, 2002, and 2003 (at least).  I just tried running it in Access 2013 and it hangs indefinitely.  After several hours of troubleshooting, I managed to isolate and reliably reproduce the error.  I offer the following Minimal, Complete, and Verifiable Example of the problem. 

    I dare say this is a bug in the Access engine.  I would love to be proved wrong.

    To reproduce, open a new blank database in Access 2013.  It can be any format, 2000 mdb; 2002-2003 mdb; or 2007-2013 accdb.  Then copy and paste the code below into a new standard code module.  Run the procedure to reproduce the problem:

    Sub ShowcaseSubreportResizeBug()
        'Create a simple table to act as Recordsource
        CurrentDb.Execute "SELECT 1 AS ID INTO Mcve"
        CurrentDb.Execute "INSERT INTO Mcve(ID) Values (2)"
    
        'Create a subreport bound to our simple table
        Dim SubRpt As Report, SRName As String
        Set SubRpt = CreateReport
        SubRpt.RecordSource = "SELECT TOP 2 * FROM Mcve"
        SRName = SubRpt.Name
        DoCmd.Save , SRName
        DoCmd.Close acReport, SRName
        Set SubRpt = Nothing
    
        'Create the main report...
        Dim Rpt As Report, RptName As String, SRCtlName As String
        Set Rpt = CreateReport
        RptName = Rpt.Name
        '...add the subreport control...
        With CreateReportControl(RptName, acSubform, acDetail)
            .SourceObject = "Report." & SRName
            SRCtlName = .Name
        End With
        '...change the size of the subreport control in code...
        Rpt.Section(acDetail).OnFormat = "[Event Procedure]"
        Rpt.HasModule = True
        Rpt.Module.InsertLines Rpt.Module.CountOfLines, vbCrLf & _
                                                        "Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)" & vbCrLf & _
                                                        "    Me.Child0.Height = 0.1" & vbCrLf & _
                                                        "End Sub"
        '...save and close the main report
        DoCmd.Save , RptName
        DoCmd.Close acReport, RptName
        Set Rpt = Nothing
        
        'Preview the newly created report (this will cause msaccess.exe to stop responding)
        MsgBox "Click OK to observe MS Access hanging when trying to open report, '" & RptName & "'."
        DoCmd.OpenReport RptName, acViewPreview
    End Sub

    Try the following code.  I think you'll find that it works now.  Your main problem is that you need to set the CanGrow property of the SubForm to False.  Also, as Dirk mentioned, you need to set the size value in twips.  There are a couple of other little issues, but the main ones are solved.

    Sub ShowcaseSubreportResizeBug()
        
        Dim rpt1 As Report
        Dim rpt2 As Report
        Dim ctl1 As Control
        Dim strReport1 As String
        Dim strReport2 As String
        Dim strControl As String
        
        'Create a simple table to act as Recordsource
        CurrentDb.Execute "SELECT 1 AS ID INTO Mcve"
        CurrentDb.Execute "INSERT INTO Mcve(ID) Values (2)"
    
        'Create a subreport bound to our simple table
        Set rpt2 = CreateReport
        rpt2.RecordSource = "SELECT TOP 2 * FROM Mcve"
        strReport2 = rpt2.Name
        DoCmd.Close acReport, strReport2, acSaveYes
    
        'Create the main report...
        Set rpt1 = CreateReport
        strReport1 = rpt1.Name
        '...add the subreport control...
        Set ctl1 = CreateReportControl(strReport1, acSubform, acDetail)
        With ctl1
            .SourceObject = "Report." & strReport2
            .CanGrow = False
            strControl = .Name
        End With
        
        '...change the size of the subreport control in code...
        rpt1.Section(acDetail).OnFormat = "[Event Procedure]"
        'rpt1.HasModule = True
    
        rpt1.Module.InsertLines rpt1.Module.CountOfLines, vbCrLf & _
        "Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)" & vbCrLf & _
        "    Me.Child0.Height = 2*1440" & vbCrLf & _
        "End Sub"
        
        '...save and close the main report
        DoCmd.Close acReport, strReport1, acSaveYes
        
        'Preview the newly created report (this will cause msaccess.exe to stop responding)
        MsgBox "Click OK to observe MS Access hanging when trying to open report, '" & strReport1 & "'."
        DoCmd.OpenReport strReport1, acViewPreview
        
        Set rpt1 = Nothing
        Set rpt2 = Nothing
        Set ctl1 = Nothing
        
    End Sub


    • Edited by RunningManHD Tuesday, September 15, 2015 5:57 AM
    • Proposed as answer by David_JunFeng Monday, September 21, 2015 9:12 AM
    • Marked as answer by mwolfe02 Thursday, October 22, 2015 8:11 PM
    Tuesday, September 15, 2015 5:55 AM
  • Indeed, this was the problem:

    "you need to set the CanGrow property of the SubForm to False."

    This is a change in behavior from Access 2000 - Access 2003, but it is at least understandable.  Access is trying to set the height of the control dynamically via the CanGrow property and I'm trying to set it dynamically via VBA.  Makes sense that it needs to be one or the other but not both.

    Thursday, October 22, 2015 8:14 PM