none
MS access only show box if data RRS feed

  • Question

  • I have a MS access report that has multiple data fields.  I know how to get the box to not show if there is no data by using an event and the code visible.  

    Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
    If Len(Nz(Me.YourField)) > 0 Then
       Me.Label1.Visible = True
    Else
       Me.Label1.Visible = False
    End If
    End Sub

    My issue i have 8 text boxes that represent different columns in my table.  If they are blank i want to suppress them and then move up the lower boxes if they have data.  Basically shrink the whitepace.  Is there a way or will i have to code it and move up boxes that way?  Thanks.

    • Me.Control.Left = #
    • Me.Control.Top = #
    Tuesday, August 29, 2017 10:48 PM

All replies

  • I have a MS access report that has multiple data fields.  I know how to get the box to not show if there is no data by using an event and the code visible.  

    Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
    If Len(Nz(Me.YourField)) > 0 Then
       Me.Label1.Visible = True
    Else
       Me.Label1.Visible = False
    End If
    End Sub

    My issue i have 8 text boxes that represent different columns in my table.  If they are blank i want to suppress them and then move up the lower boxes if they have data.  Basically shrink the whitepace.  Is there a way or will i have to code it and move up boxes that way?  Thanks.

    • Me.Control.Left = #
    • Me.Control.Top = #

    Tuesday, August 29, 2017 3:29 AM
  • Why not use a calculated field with Can Shrink set Yes.    

    Use MyField:  [Field1] + (Chr(13) & Chr(10) & [Field2]) + (Chr(13) & Chr(10) & [Field3]) + (Chr(13) & Chr(10) & [Field4]) ....

    EDIT --   It seems to me you have repeating fields rather than records for the additional data under a subject.   If so then that is bad and not like a relational database.


    Build a little, test a little


    Tuesday, August 29, 2017 11:33 PM
  • Hi Kirk,

    I notice there are some codes used for Access report. And this forum focuses on general discussion on Office 2016. To better resolve your issue, I would move the thread to Access for Developers forum for further troubleshooting:

    https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=accessdev

    Thanks for your understanding.

    Best Regards,

    Winnie Liang


    Please remember to mark the replies as answers if they help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Wednesday, August 30, 2017 2:48 AM
  • Unfortunately i didn't design the db only dealing with what i was given.  Will give your suggestion a shot.  Thanks 
    Wednesday, August 30, 2017 5:38 PM
  • Hi KirkSH,

    >> My issue i have 8 text boxes that represent different columns in my table.  If they are blank i want to suppress them and then move up the lower boxes if they have data.

    Could you share us a screen shot about your current Report and your expected result?

    Do you mean you place 8 text boxes in the report and want to change the size of them according whether you have value? In general, the column width is stable. If the filed in first record is empty, and it in the second record is not empty, how did you want to suppress or move up it?

    It would be helpful if you could share us a simple demo to reproduce your issue.

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, September 5, 2017 8:56 AM
  • One way of suppressing Nulls is to concatenate all of the values into a single text box control by means of the CanShrinkLines function which Microsoft published some years ago.  Below is a slightly amended version of the function:

    Public Function CanShrinkLines(ParamArray arrLines())

        ' Pass this function the lines to be combined
        ' For example: strAddress =
        ' CanShrinkLines(Name, Address1, Address2, City, State, Zip)
        Dim X As Integer, strLine As String
        
        For X = 0 To UBound(arrLines)
            If Not IsNull(arrLines(X)) And Trim(arrLines(X)) <> "" Then
              strLine = strLine & vbCrLf & arrLines(X)
            End If
        Next
          
        ' remove leading carriage return/line feed
        CanShrinkLines = Mid(strLine, 3)
     
    End Function

    You would call the function as the ControlSource property of a text box control whose height is minimal and whose CanGrow property is True:

    =CanShrinkLines([Column1],[Column2],[Column3],[Column4],[Column5],[Column6],[Column7],[Column8])

    If you wish the height of the section to grow commensurately with that of the control set the section's CanGrow property to True also.  Otherwise set the section's height so that it can accommodate all eight lines.  This would be done for instance with a labels report, where the height of each label must remain fixed.

    You'll find examples of the use of this function and other concatenation functions in Concat.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Tuesday, September 5, 2017 11:40 AM Typo corrected.
    Tuesday, September 5, 2017 11:38 AM
  • If I understand your question, why not just set the CanShrink property of the TextBox controls to True?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, September 5, 2017 12:57 PM