Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2022
    Posts
    9

    Question Hide elements in report based on query field value

    Hi all,



    I have created a report that shows a graphical representation of an IO card for a PLC configuration, which contains lines and boxes indicating cabling to external cabinets. However, depending on the value of fields in certain records I would like to be able to hide lines and boxes. Is there a way in a report to hide elements based on query field value?

    For example the report shows on each page an IO card with 16 IOs and will show the variable name, the IO number as well as the cable details connected to the IO shown by some lines. However some IOs are not used and the variable name field text says "Spare". For records where the variable name is "Spare" I would like this line to become invisible. How can I do this?

    Thanks for your support.

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Either set it's visible property to False, or set the font to the same colour as the report background (normally white?)
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Join Date
    Mar 2022
    Posts
    9
    Quote Originally Posted by Welshgasman View Post
    Either set it's visible property to False, or set the font to the same colour as the report background (normally white?)
    I only want the lines to become invisible for certain records based on field values. If I set visible property to false or change the font it will become invisible for all records.

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Use Conditional Formatting, OR
    In the On Format event of the detail section, you would have code to determine whether to make visible or not.

    So NO!, it would not affect all records.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    Join Date
    Mar 2022
    Posts
    9
    Quote Originally Posted by Welshgasman View Post
    Use Conditional Formatting, OR
    In the On Format event of the detail section, you would have code to determine whether to make visible or not.

    So NO!, it would not affect all records.
    Ok so I added the following code in the On Format Event, but it's not working. What am I doing wrong?

    Option Compare Database


    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)


    If [_qryIO_Wiring_Diagrams]![Cable Core 1] = "A" Then
    Me![Core 1 Line].Visible = False
    End If


    End Sub

    The query _qryIO_Wiring_Diagrams that is driving the report contains a field [Cable Core 1] and I want the line with name [Core 1 Line] in the detail section to become invisible if the Cable Core is 1.

  6. #6
    Join Date
    Mar 2022
    Posts
    9
    Quote Originally Posted by Thomas Van Peteghem View Post
    Ok so I added the following code in the On Format Event, but it's not working. What am I doing wrong?

    Option Compare Database


    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)


    If [_qryIO_Wiring_Diagrams]![Cable Core 1] = "A" Then
    Me![Core 1 Line].Visible = False
    End If


    End Sub

    The query _qryIO_Wiring_Diagrams that is driving the report contains a field [Cable Core 1] and I want the line with name [Core 1 Line] in the detail section to become invisible if the Cable Core is 1.
    Managed to work it out further and got it functional now, apparently you need to reset the property again for each field in order to make it work? Also I had a bit of a sketchy approach to determine empty fields because I couldnt manage to get it working otherwise. Is there a better way to verify empty fields?

    Below a snap of the code, at least it works:

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    [Core 1 Line].Visible = True
    If Len("" & [Cable Core 1]) = 0 Then
    [Core 1 Line].Visible = False
    End If
    End Sub

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Is [Cable Core 1] holding a numeric or text value? In your post 5 you imply both (="A" and
    [Core 1 Line] in the detail section to become invisible if the Cable Core is 1.
    If numeric you can use:
    Code:
    If Nz([Cable Core 1],0)=0
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    Join Date
    Mar 2022
    Posts
    9
    Quote Originally Posted by Gicu View Post
    Is [Cable Core 1] holding a numeric or text value? In your post 5 you imply both (="A" and
    If numeric you can use:
    Code:
    If Nz([Cable Core 1],0)=0
    Cheers,
    The field can contain both numbers and letters, it's a short text field.

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    To check if a bound control is null you can use the IsNull() function; to lump the "nulls" with the "defaults" (for example if a numeric field is set to have a default value of 0 you use the Nz() function.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 2
    Last Post: 09-08-2021, 08:08 AM
  2. Hide Report Fields based on values in each record
    By hfreedman1957 in forum Reports
    Replies: 4
    Last Post: 08-02-2017, 05:45 PM
  3. Hide a column in a report based on user rights
    By whisp0214 in forum Reports
    Replies: 6
    Last Post: 06-29-2017, 03:11 PM
  4. Hide field in sub-report based on condition
    By vinsavant in forum Reports
    Replies: 4
    Last Post: 12-16-2012, 10:18 AM
  5. Hide elements of Pivot-table
    By ivancp in forum Access
    Replies: 0
    Last Post: 01-26-2011, 08:17 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