Results 1 to 9 of 9
  1. #1
    nathan.malone is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2018
    Posts
    5

    Getting the Height of the Detail Section After CanGrow/CanShrink

    I have an MS Access report with a detail section that automatically adjusts its height to fit its contents using the CanGrow and CanShrink properties. The report is about ongoing projects within the company, and the height of the detail section can fluctuate substantially for each project. Additionally, there are two versions of this report: general and administrative. The administrative version has some information that the general version does not have, which causes height differences for the same projects between the two versions. My current task is to make the height of the detail section for each project match between the two versions of the report, even if the contents don't match.




    My current idea to approach this problem is to record the height of the detail section of each project on the administrative report, then run the general report and set the height of each detail section to match the recorded heights from the administrative report. To do this, I have to determine the height of the detail section AFTER the section has automatically adjusted its height, which is the keystone that I cannot figure out. The reason that I put emphasis on the word "after" is that, in my experience, the Detail.Height property during the Detail.OnFormat event does not reflect the height of the detail section after the section adjusts to fit its contents, but rather it reflects the height of the detail section before it adjusts to fit its contents (effectively giving me the height that I assigned to it in design view).


    I have been beating my head against the wall with this problem for far too long. How do I get the height of the detail section after it has automatically adjusted to fit its contents? Alternatively, is there a better approach to making the corresponding detail sections between the two report versions match in height?

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Why not just have one version of the report with all the data used in the admin version of the report.
    Open the report using open args -either admin or general.

    In the Report Open event use code like this

    Code:
    if me.openargs ="general" Then
    .  'add list of controls to be hidden here
    Else 'admin
    .  'add code here if needed for admin version
    End If
    This should be much simpler
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    nathan.malone is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2018
    Posts
    5
    Quote Originally Posted by ridders52 View Post
    Why not just have one version of the report with all the data used in the admin version of the report.
    Open the report using open args -either admin or general.

    In the Report Open event use code like this

    Code:
    if me.openargs ="general" Then
    .  'add list of controls to be hidden here
    Else 'admin
    .  'add code here if needed for admin version
    End If
    This should be much simpler
    That would be a possibility, but the contents of the detail section cause the height of the report to fluctuate quite a lot even just within one version of the report. As an example of one of the fields that causes this, there is a "Comments" field in which there may be no comments or there may be several paragraphs of comments. So, (if I'm understanding you correctly) I would need to hide the fields AFTER the detail section adjusts to fit the comments and other data. If I were to hide the fields in the Report.Open event, the detail section would not have adjusted to fit the contents yet. It seems like there might be something there, but ultimately I think it would require that the detail section have a static height to begin with. Thanks for the idea, and I'll keep it in mind and post back if it ends up helping me.

    As a side note outside of the scope of my original question, I'm hoping to be able to do what I was proposing in the original question because there is another report that I will be building soon that has 10+ different version that I will need to do something similar with. The idea I proposed in my original post would be more general and would work for both of my reports. However, it may very well be impossible.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by nathan.malone View Post
    That would be a possibility, but the contents of the detail section cause the height of the report to fluctuate quite a lot even just within one version of the report. As an example of one of the fields that causes this, there is a "Comments" field in which there may be no comments or there may be several paragraphs of comments. So, (if I'm understanding you correctly) I would need to hide the fields AFTER the detail section adjusts to fit the comments and other data. If I were to hide the fields in the Report.Open event, the detail section would not have adjusted to fit the contents yet. It seems like there might be something there, but ultimately I think it would require that the detail section have a static height to begin with. Thanks for the idea, and I'll keep it in mind and post back if it ends up helping me.
    If it fluctuates a lot then perhaps its not feasible
    You are quire correct about the Report_Open event but that's not the place to do it
    You need to hide the fields using the On_Format event e.g. Detail_Format so all changes have been processed first!
    Try it & let me know how you get on

    Quote Originally Posted by nathan.malone View Post
    As a side note outside of the scope of my original question, I'm hoping to be able to do what I was proposing in the original question because there is another report that I will be building soon that has 10+ different version that I will need to do something similar with. The idea I proposed in my original post would be more general and would work for both of my reports. However, it may very well be impossible.
    Best of luck - if it works for one it should in theory work for the other !!!
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    nathan.malone is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2018
    Posts
    5
    The question was cross-posted to the Microsoft Access Help Center at https://www.access-programmers.co.uk...=1#post1572932.

  6. #6
    nathan.malone is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2018
    Posts
    5
    I have finally worked out a solution to this. I hope there is not a rule on line limits. If so, I will gladly edit my post to attach the code as a file instead.

    Effectively, my approach was not to hide the controls, but rather count how many lines would go into those controls if I did show the administrative information, and replace those lines with dummy blank lines. Below is a simplified version of the code I used in my Detail.OnFormat event. Note, txtId, txtAdminNotes, and txtAdminNotesLabel are all textboxes on my form. All administrative controls are set with Visible, CanGrow, and CanShrink equal to True.

    Code:
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
        Dim sql As String
        Dim rs As DAO.Recordset
        Dim adminNotes As String
        
        sql = "SELECT * " & _
                "  FROM [My_Table] " & _
                " WHERE [Id] = " & txtId.value
        
        Set rs = CurrentDb.OpenRecordset(sql, dbOpenSnapshot, dbSeeChanges)
        
        If Not rs.BOF Or Not rs.EOF Then
            rs.MoveFirst
    
    
            'Get special administrative notes.
            adminNotes = Nz(rs!STIP_Comments_Internal, vbNullString)
            
            'Add special administrative information (or blank lines) to the report.
            HandleAdministrativeTextBox adminNotes, txtAdminNotes, "Admin. Notes:", txtAdminNotesLabel
        End If
    End Sub
    Below you will find the helper subs that I used that actually do the majority of the work. I broke them out into reusable subs because there is more than one set of controls for which I used this same logic.

    Code:
    Private Sub HandleAdministrativeTextBox(ByRef textVal As String, _
                                            ByRef txtBox As TextBox, _
                                            labelText As String, _
                                            txtBoxLabel As TextBox)
        Dim lineCount As Long
        textVal = BreakTextToFitTextBox(textVal, txtBox, lineCount)
        
        If isAdministrative Then
            'Add administrative information to the report.
            txtBox.value = textVal
            txtBoxLabel.value = IIf(Len(textVal), labelText, vbNullString)
        Else
            If Len(textVal) Then
                'Add blank lines to the report.
                If lineCount <> 1 Then
                    txtBox.value = GenerateBlankLines(lineCount)
                Else
                    'Single blank line. (Trivial; avoid unnecessary function call)
                    txtBox.value = " "
                End If
            Else
                txtBox.value = vbNullString
            End If
        End If
    End Sub
    
    Private Function GenerateBlankLines(numLines As Long) As String
        Dim blankLines As String
        Dim index As Long
        
        For index = 1 To numLines
            blankLines = blankLines & IIf(index <> 1, vbCrLf, vbNullString) & " "
        Next
        
        GenerateBlankLines = blankLines
    End Function
    
    Private Function BreakTextToFitTextBox(ByRef textVal As String, _
                                           ByRef txtBox As TextBox, _
                                           Optional ByRef numLines As Long = 0) As String
        Dim brokenTextVal As String
        Dim lineCount As Long
        
        If InStrB(textVal, " ") Then
            Dim words() As String
            Dim wordIndex As Long
            Dim currentWord As String
            Dim lines() As String
            Dim currentLine As String
            Dim testLine As String
            
            'Sync the formatting between the report and the textbox
            'since the report is the object that measures the text.
            Me.FontName = txtBox.FontName
            Me.FontSize = txtBox.FontSize
            Me.FontBold = txtBox.FontBold
            Me.FontItalic = txtBox.FontItalic
            
            'Split the text on spaces.
            words = Split(textVal, " ")
            lineCount = 0
            ReDim lines(lineCount) As String
            
            'Loop through the words (or otherwise cluster of characters) one by one.
            For wordIndex = LBound(words) To UBound(words)
                currentWord = words(wordIndex)
                
                'Test if the word can fit in the textbox with the current line.
                'NOTE: The "+ 100" that you see below is a hack. For some reason,
                '      the measurement didn't work perfectly. This seemed to work
                '      in most cases, but there is probably a better solution
                '      somewhere out there to be discovered.
                testLine = IIf(Len(currentLine), currentLine & " ", vbNullString) & currentWord
                If txtBox.Width > Me.TextWidth(testLine) + 100 Then
                    'It fit. Add the word to the line and move on.
                    currentLine = testLine
                Else
                    'It didn't fit. Start a new line with the current word.
                    lineCount = lineCount + 1
                    ReDim Preserve lines(lineCount) As String
                    
                    lines(lineCount - 1) = currentLine
                    currentLine = currentWord
                End If
            Next
            
            'Cleanup: Make sure to add the last line to the lines array.
            If Len(currentLine) Then
                lineCount = lineCount + 1
                ReDim Preserve lines(lineCount) As String
                
                lines(lineCount - 1) = currentLine
            End If
            
            'Concatenate the lines array with vbCrLf.
            brokenTextVal = Join(lines, vbCrLf)
        Else
            'No spaces to split the text on.
            lineCount = IIf(Len(textVal), 1, 0)
            brokenTextVal = textVal
        End If
        
        numLines = lineCount
        BreakTextToFitTextBox = brokenTextVal
    End Function

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Glad you've found a solution
    Whilst I understand how it works, it seems an unnecessarily complex solution to the problem

    Belatedly I tested my suggested solution on a couple of reports - it worked perfectly.
    I also checked whether I could find a way of making the hidden items visible in the PDF - I couldn't do so

    Here's one example with both versions of the report side by side
    Ignore the map change - that's due to me changing from design view to print preview without updating

    Click image for larger version. 

Name:	ReportHideSelectedControls.PNG 
Views:	11 
Size:	239.0 KB 
ID:	33505
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    nathan.malone is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2018
    Posts
    5
    That approach would have worked if I knew the exact height of the information that would be going into both versions of the report. Such as here, each field is exactly one line. In my report, I cannot assume the height of any of the information that is shown. This requires me to use the CanGrow/CanShrink properties, which will cause that top section to shrink when you hide those fields.

    I'll go ahead and provide a redacted version of the report. (I work for a department of transportation and the report will be public knowledge in a few months, but we are still refining the list of projects and the information on the projects and are not yet ready to publish the information.) Fields covered in black are general project information, fields covered in blue are a breakdown of the funding for the project, and fields covered in green is the administrative-only information I was referring to. If you notice, even without the administrative information, the height of projects fluctuate. Nothing is a static height. Additionally, there are other examples where the administrative fields span more than one line. For further complexity, not all projects utilize all available of the administrative fields, and we would prefer not insert unnecessary whitespace. This is the reason for the convolution.

    Click image for larger version. 

Name:	STIP_Administrative_Example_Redacted.png 
Views:	8 
Size:	27.9 KB 
ID:	33513

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    What happens if you set Can Shrink =False?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Replies: 2
    Last Post: 02-28-2018, 05:04 PM
  2. How to use CanShrink/CanGrow properties
    By kdbailey in forum Access
    Replies: 7
    Last Post: 04-04-2017, 10:02 AM
  3. Canshrink or cangrow on list box
    By gstylianou in forum Access
    Replies: 7
    Last Post: 08-25-2014, 02:59 AM
  4. height of Detail section in reports
    By Nicola in forum Reports
    Replies: 4
    Last Post: 05-31-2013, 02:46 AM
  5. Replies: 6
    Last Post: 03-26-2013, 12:17 PM

Tags for this Thread

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