Results 1 to 15 of 15
  1. #1
    evenlater is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    8

    Subreport Won't Resize Controls in VBA

    I have a report with a subreport that has worked quite well for years. It looks like this: Click image for larger version. 

Name:	blank.jpg 
Views:	44 
Size:	98.8 KB 
ID:	34542

    The controls are sized in the Detail_Format event with this code:


    Code:
     Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)    Dim c As Control
        Dim intDimensions As Integer
        Dim intWidth As Integer
        
        intDimensions = DCount("DimensionID", "qryStatisticsByDimensionSATAll")
    
    
        For Each c In Me.Controls
            intWidth = (1440 * 9) / intDimensions
            c.Width = intWidth
        Next
    
    
    End Sub
    The idea is that the controls are sized to exactly fit a 9" subreport regardless of the # of columns needed.

    I made some changes to the recordsource and now when I try to run the report with 4 columns of data I get the error "The control or subform control is too large for this location." I can't figure out why it isn't working like it usually does. Any ideas?

  2. #2
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    are you sure that intDimensions is returning a non-zero?

    Did you change the twips conversion code at all?

    Did you add more controls to the detail of the form? The report detail is looping through all the controls. It would be moving all of the controls. I usually put a select case in those types of statements like

    Code:
     for each c in me.controls
       select case typename(c)
          case "TextBox"
                   ' move the control here, because we know it was a textbox and should be
              if c.tag = "specialformatting" then ' similar more validation logic to what you are moving.
          case else
             'other items
       end select
    next

  3. #3
    evenlater is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    8

    Subreport Won't Resize Controls in VBA

    Thanks for the suggestions.

    intDimensions is returning 4 and 9 in my two test cases.

    I have not changed the twips code. It's a 9 inch subform, so I'm multiplying the number of twips in an inch by the number of inches in the subform and dividing by the number of columns.

    I have not added any controls. I shouldn't need to tag them because they are all text boxes that should be the same width. Also, I get the error right away on the first control. But I will try a select case and see if that helps.

    Could it be something to do with the number of columns in the subreport settings (10)? Or the width of the controls before they are resized?

    Thanks again.


    Quote Originally Posted by Perceptus View Post
    are you sure that intDimensions is returning a non-zero?

    Did you change the twips conversion code at all?

    Did you add more controls to the detail of the form? The report detail is looping through all the controls. It would be moving all of the controls. I usually put a select case in those types of statements like

    Code:
     for each c in me.controls
       select case typename(c)
          case "TextBox"
                   ' move the control here, because we know it was a textbox and should be
              if c.tag = "specialformatting" then ' similar more validation logic to what you are moving.
          case else
             'other items
       end select
    next

  4. #4
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    Ive been able to recreate the issue. When you speak of columns, I guess that you have the controls at set positions since there is not adjustment to the the controls left property.

    btw, thats just a typo in your code paste? the first line having Dim C as control?

  5. #5
    evenlater is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    8

    Subreport Won't Resize Controls in VBA

    I'm speaking of the columns settings in Page Layout. See design view screenshot:
    Click image for larger version. 

Name:	design view.jpg 
Views:	28 
Size:	128.6 KB 
ID:	34600

    Yes, the Dim c as control is on the second line in the actual code.

    When you recreate the issue, are you getting the same error message? ("The control or subform control is too large for this location.")

    Thanks.


    Quote Originally Posted by Perceptus View Post
    Ive been able to recreate the issue. When you speak of columns, I guess that you have the controls at set positions since there is not adjustment to the the controls left property.

    btw, thats just a typo in your code paste? the first line having Dim C as control?

  6. #6
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    im not using it as subform. just a form. but the code produces this. Also I have never used said columns properties that you are showing. looks interesting.


    My code is yours with a small change
    Code:
     Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)    Dim c As Control
        Dim intDimensions As Integer
        Dim intWidth As Integer
        
        intDimensions = 9 'DCount("DimensionID", "qryStatisticsByDimensionSATAll")
    
    
        For Each c In Me.Controls
            intWidth = (1440 * 9) / intDimensions
            c.Width = intWidth
        Next
    
    
    End Sub
    Attached Thumbnails Attached Thumbnails toobig.png  

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    sounds to me like there is an assumption that the form is 9" wide - perhaps it is not - or perhaps your calc, due to rounding, is coming up with a twips value just a twip or two over the 9". Suggest try

    intWidth = insidewidth / intDimensions

    or

    intWidth =me.width / intDimensions

    or

    intWidth = ((1440 * 9) / intDimensions)-1

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    For Each c In Me.Controls
    This was addressed, but I don't see any response about it. I'm struggling with how you get this to work if you set the subform/subreport control to the same width as each and every control it holds.

    Perchance when you altered the record source you switched to something other than Print Preview? Are you sure your code is running, or is Access generating the error in spite of your code? This event doesn't fire for all report views.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    evenlater is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    8

    Subreport Won't Resize Controls in VBA

    I get the error whether I use it as a subreport or not. Is yours in landscape? Also, I think you need the column settings or else you will just get one column of data.

    Quote Originally Posted by Perceptus View Post
    im not using it as subform. just a form. but the code produces this. Also I have never used said columns properties that you are showing. looks interesting.


    My code is yours with a small change
    Code:
     Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)    Dim c As Control
        Dim intDimensions As Integer
        Dim intWidth As Integer
        
        intDimensions = 9 'DCount("DimensionID", "qryStatisticsByDimensionSATAll")
    
    
        For Each c In Me.Controls
            intWidth = (1440 * 9) / intDimensions
            c.Width = intWidth
        Next
    
    
    End Sub

  10. #10
    evenlater is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    8

    Subreport Won't Resize Controls in VBA

    Thanks, good suggestions. When I try Me.Width / intDimensions, I don't get the error, but I get this:

    Click image for larger version. 

Name:	mewidth divided by intdimensions.jpg 
Views:	19 
Size:	75.9 KB 
ID:	34603

    The left column is the main report.

    If I try your third suggestion, I still get the error. "InsideWidth" is a variable? What would I set it to?


    Quote Originally Posted by Ajax View Post
    sounds to me like there is an assumption that the form is 9" wide - perhaps it is not - or perhaps your calc, due to rounding, is coming up with a twips value just a twip or two over the 9". Suggest try

    intWidth = insidewidth / intDimensions

    or

    intWidth =me.width / intDimensions

    or

    intWidth = ((1440 * 9) / intDimensions)-1

  11. #11
    evenlater is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    8

    Subreport Won't Resize Controls in VBA

    It should work (used to work) because there are only 10 controls (text boxes) and they all should be the same length.
    I get the error when I switch to Print Preview, and Access is running the code... it blows up on the line when the first control is resized.
    Thanks for the suggestions.

    Quote Originally Posted by Micron View Post
    This was addressed, but I don't see any response about it. I'm struggling with how you get this to work if you set the subform/subreport control to the same width as each and every control it holds.

    Perchance when you altered the record source you switched to something other than Print Preview? Are you sure your code is running, or is Access generating the error in spite of your code? This event doesn't fire for all report views.

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    InsideWidth is the width for the window the form/report is displayed in - which would be the width of the subform control you are using - so you don't set it

  13. #13
    evenlater is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    8

    Subreport Won't Resize Controls in VBA

    Quote Originally Posted by Ajax View Post
    InsideWidth is the width for the window the form/report is displayed in - which would be the width of the subform control you are using - so you don't set it
    Interesting, I didn't know that. Turns out though that it only applies to forms, not reports.

  14. #14
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I've never tried it on a report, so learned something new

  15. #15
    evenlater is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    8

    Subreport Won't Resize Controls in VBA

    For anyone interested: this problem was solved with a procedure that opens the report in design view before it is opened in print preview. See code below. Thanks to all.
    Code:
    Public Sub SizeSubReportColumns(strReport As String, intColumns As Integer, dblWidth As Double)
    'Opens the specified subreport and sets the report's width so that all the columns in the specified query
    'can be fit into the specified width.
        Dim intStrands As Integer
        Dim r As Report
        Dim c As Control
        Dim dblColWidth As Double
        
        'Set the size of the columns in the Subreport by:
        
        'Opening the subreport in design view (hidden from the user)...
        DoCmd.OpenReport strReport, acViewDesign, , , acHidden
        
        'Setting a reference to that report
        Set r = Reports(strReport)
    
    
        'Calculating the width of each column based on the width of the subreport and the number of columns...
        '(Note: 1440 = the number of twips in an inch. Access VBA measures width in twips.)
        dblColWidth = (1440 * dblWidth) / intColumns
       
        'Setting the width of all text boxes in the subreport to that column width...
        For Each c In r.Controls
            c.Width = dblColWidth
        Next
        
        'Setting the width of the report to the width of one column (dividing the width of the subreport by the # of columns)...
        r.Width = dblColWidth
    
    
        'and finally, saving and closing the subreport.
        DoCmd.Close acReport, strReport, acSaveYes
        
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. cycle thur controls on a subreport
    By markjkubicki in forum Reports
    Replies: 2
    Last Post: 10-01-2017, 07:27 AM
  2. Replies: 1
    Last Post: 12-07-2015, 07:46 PM
  3. Move form controls according to subform resize
    By Lukael in forum Programming
    Replies: 8
    Last Post: 12-06-2015, 01:36 PM
  4. Populate Subreport Controls Based On User Input
    By unifyzero in forum Reports
    Replies: 2
    Last Post: 06-25-2015, 08:42 AM
  5. Replies: 1
    Last Post: 04-15-2013, 10:02 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums