Results 1 to 8 of 8
  1. #1
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188

    Expanding text boxes to match others in same row

    See attached screenshot.


    I need to setup a packing list for a merchant who has demanding specs on the appearance. Attached is a screenshot showing my problem. The text problems are to be outlined with a 1pt border. No problem.

    Problem: Consumers are allowed to submit a gift message per each product which means there can be unpredictable text lengths. The product description text box can expand, but I need the other text boxes to expand to the same height.

    I don't even know the verbiage to search for to explain what I mean. I'm guessing there could be a VB solution, but I'm lost in that as well. I'm hoping I can find someone else that had a similar problem and found a solution.

    Thoughts anyone?

    Thanks!
    Attached Thumbnails Attached Thumbnails S1.jpg  

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Bing: Access report textbox height

    Review https://bytes.com/topic/access/answe...detail-section

    Instead of visible borders on textbox, code manipulates vertical Line controls. Horizontal line controls would be fixed in width and move as the textbox grows.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    Thank you for the link. Not knowing VB, it's little surprise I'm getting an error. See attached screenshot. I modified the author's code to accommodate my 4 fields.

    Does the below measure the height of a specific text box, or does it evaluate all text boxes for the highest?

    Any suggestions on the error is appreciated!

    Thanks!

    Code:
    Private Sub Report_Page()
    On Error Resume Next
     Dim lngCounter As Long, dblMaxHeight As Double
     dblMaxHeight = 0
     ReDim strcontrol(4)
     strcontrol(0) = "tbUPC"
     strcontrol(1) = "tbVendorSku"
     strcontrol(2) = "tbDescrip"
     strcontrol(3) = "tbQty"
     For lngCounter = 0 To UBound(strcontrol)
     If Me(strcontrol(lngCounter)).Height > dblMaxHeight Then dblMaxHeight = Me(strcontrol(lngCounter)).Height
     Next
     For lngCounter = 0 To UBound(strcontrol)
     If lngCounter = 0 Then
     Me.Line (Me(strcontrol(lngCounter)).Left, Me(strcontrol(lngCounter)).Top)-Step(Me(strcontrol(lngCounter)).Width, dblMaxHeight), , B
     Else
     Me.Line (Me(strcontrol(lngCounter)).Left, Me(strcontrol(lngCounter)).Top)-Step(Me(strcontrol(lngCounter)).Width, dblMaxHeight), , B
     End If
     Next
    End Sub
    Attached Thumbnails Attached Thumbnails S1.jpg  

  4. #4
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I think according to the link you should be putting this in the
    ON PRINT property of the detail section.
    You appear to have it in the Report Page event.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    I tried the below in the OnPrint as well. Pretty much the same error. See attached screenshot.

    Should the text box names below be in quotes: "UPC" or brackets: [UPC]? I tried both. Same error.

    Thoughts?

    Code:
    Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
    On Error Resume Next
     Dim lngCounter As Long, dblMaxHeight As Double
     dblMaxHeight = 0
     ReDim strcontrol(4)
     strcontrol(0) = "UPC"
     strcontrol(1) = "VendorSku"
     strcontrol(2) = "Descrip"
     strcontrol(3) = "Qty"
     For lngCounter = 0 To UBound(strcontrol)
     If Me(strcontrol(lngCounter)).Height > dblMaxHeight Then dblMaxHeight = Me(strcontrol(lngCounter)).Height
     Next
     For lngCounter = 0 To UBound(strcontrol)
     If lngCounter = 0 Then
     Me.Line (Me(strcontrol(lngCounter)).Left, Me(strcontrol(lngCounter)).Top)-Step(Me(strcontrol(lngCounter)).Width, dblMaxHeight), , B
     Else
     Me.Line (Me(strcontrol(lngCounter)).Left, Me(strcontrol(lngCounter)).Top)-Step(Me(strcontrol(lngCounter)).Width, dblMaxHeight), , B
     End If
     Next
    End Sub
    Attached Thumbnails Attached Thumbnails S1.jpg  

  6. #6
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    Eureka! Making headway. I found a case online where the user was getting the same error. Both they and I resolved by copying all assets into a new database. Repairing and compacting the old did nothing. But I copied all assets into a new database and the error was gone.

    Now, trying to fine tune. The lines were faint so I added the Me.DrawWidth = 20 and it closer matches the 1 pt line required.

    Few problems:
    1) The column headers and detail text boxes appear to be misaligned and not meeting each other. It prints correctly, but not visually correct. No idea if this can be resolved as it's an OnPrint event.

    2) Whenever there's a row that does not have an expanding textbox, the bottom line across all textboxes is missing. I tried adding an extra hand drawn line but it never lines up correctly. Any way to fix this?

    3) There's a vertical line splitting the UPC column. Where is this coming from?

    Thanks for your help!

    Code:
    Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
    On Error Resume Next
    Dim lngCounter As Long, dblMaxHeight As Double
    dblMaxHeight = 0
    ReDim strcontrol(4)
    strcontrol(0) = "UPC"
    strcontrol(1) = "VendorSku"
    strcontrol(2) = "Descrip"
    strcontrol(3) = "Qty"
    Me.DrawWidth = 20
    For lngCounter = 0 To UBound(strcontrol)
    If Me(strcontrol(lngCounter)).Height > dblMaxHeight Then dblMaxHeight = Me(strcontrol(lngCounter)).Height
    Next
    For lngCounter = 0 To UBound(strcontrol)
    If lngCounter = 0 Then
    Me.Line (Me(strcontrol(lngCounter)).Left, Me(strcontrol(lngCounter)).Top)-Step(Me(strcontrol(lngCounter)).Width, dblMaxHeight), , B
    Else
    Me.Line (Me(strcontrol(lngCounter)).Left, Me(strcontrol(lngCounter)).Top)-Step(Me(strcontrol(lngCounter)).Width, dblMaxHeight), , B
    End If
    Next
    End Sub
    Attached Thumbnails Attached Thumbnails S1.jpg  

  7. #7
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    Addendum: I think I have it fixed, but still questions...

    I changed this line: ReDim strcontrol(3)
    And the bottom line is being drawn and the vertical line is gone. Great! But why? Is it because all dimensions are based on the 3rd textbox which is: strcontrol(2) = "Descrip"...? Because it's the 3rd control? Maybe I'm answering my own question.

    Is there a way to improve the on screen aesthetics with regard to boxes lining up?

    Thanks again!

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    The example procedure tests height of multiple controls and uses the one that is greatest. If you are certain about which control will grow most, then can reference only that control for the height and eliminate the first array loop.

    I tested the original code in post 3 and it works for me. Although somehow I am getting an extra box drawn. Not understanding why the code has the second conditional If for generating box, the code to draw is identical. I removed the If code and left only the one line and it still works, although still get the extra box. Ah, finally figured out cause. Had to change code to:

    For lngCounter = 0 To UBound(strcontrol) - 1

    I expect getting the label borders to perfectly line up with the code generated boxes will require similar code in the Page Format event. Tested and that does work.

    Might want to set or increase textbox and label Margin properties. Make sure all textboxes CanGrow Yes. CanShrink No if you want to be able to open in Report view and still view data.

    Be aware Print and Format events only trigger for PrintPreview or direct to printer.

    Code is easier to read with indentation.
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
    On Error Resume Next
    Dim lngCounter As Long, dblMaxHeight As Double
    dblMaxHeight = 0
    ReDim strcontrol(4)
    strcontrol(0) = "UPC"
    strcontrol(1) = "VendorSku"
    strcontrol(2) = "Descrip"
    strcontrol(3) = "Qty"
    For lngCounter = 0 To UBound(strcontrol) - 1
        If Me(strcontrol(lngCounter)).Height > dblMaxHeight Then dblMaxHeight = Me(strcontrol(lngCounter)).Height
    Next
    For lngCounter = 0 To UBound(strcontrol) - 1
        Me.Line (Me(strcontrol(lngCounter)).Left, Me(strcontrol(lngCounter)).Top)-Step(Me(strcontrol(lngCounter)).Width, dblMaxHeight), , B
    Next
    End Sub
    
    Private Sub PageHeaderSection_Print(Cancel As Integer, PrintCount As Integer)
    On Error Resume Next
    Dim lngCounter As Long
    ReDim strcontrol(4)
    strcontrol(0) = "Label0"
    strcontrol(1) = "Label1"
    strcontrol(2) = "Label2"
    strcontrol(3) = "Label3"
    For lngCounter = 0 To UBound(strcontrol) - 1
        Me.Line (Me(strcontrol(lngCounter)).Left, Me(strcontrol(lngCounter)).Top)-Step(Me(strcontrol(lngCounter)).Width, Me(strcontrol(lngCounter)).Height), , B
    Next
    End Sub
    Think would not want to save "Product description goes here" in table.
    Last edited by June7; 05-03-2019 at 04:56 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Changing text boxes to combo boxes
    By Lou_Reed in forum Access
    Replies: 8
    Last Post: 09-15-2015, 11:09 AM
  2. Linking Combo boxes and Text boxes
    By Nathan4477 in forum Forms
    Replies: 6
    Last Post: 07-29-2015, 08:50 AM
  3. Replies: 9
    Last Post: 06-04-2014, 10:45 AM
  4. Expanding text string
    By KevinG in forum Access
    Replies: 9
    Last Post: 07-09-2013, 02:17 PM
  5. Replies: 3
    Last Post: 02-07-2013, 09:53 PM

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