I was using a quick technique today to hide certain fields on a report detail section that were repeating.

There is a very quick solution to this in which you can use the “Hide Duplicates” property in the Format properties of a text box let’s say. But what if it’s not a text box, or you need to hide multiple fields based on the one text box duplicating?

Also, that property can sometimes produce unexpected results when you turn it on because if the information repeats verbatim later on in the report, it will remain hidden and only appear the first time it’s used.

For example, if you have the records:

John Adams

John Jacob-jingleheimer-schmidt

Setting both a last and first name field to “Hide Duplicates” = On, would hide “John” in the second record.

So, the way I do this is as follows:

1. Create a new group for the detail section on a unique combination of things that you want to hide duplicates on. In the above recordset, I might group on FirstName & LastName to make the records unique.
2. I hide the group header that is created by default. You can either set the section’s visibility to False, or you can just change the height to 0.
3. I add a new Text Box to the detail section, change it’s visibility to false, and set the value to “=1”
4. Then on that Text Box I set it to display a running sum over the group in the data properties.
5. Finally, in the detail On Format event, I add code to hide the fields I want to hide if the Text Box’s total is greater than 1 (that way it only displays the first time it appears in the group)

Here is an example of what my code looks like in my On Format event:

Private Sub Detail_Format(Cancel As Integer,FormatCount As Integer) 
    If Me.PhaseLine = 1 Then 
        Me.Phase_Number.Visible = True 
        Me.phase_description.Visible = True 
    Else 
        Me.Phase_Number.Visible = False 
        Me.phase_description.Visible = False 
    End If 
End Sub 

Sign up For a Daily Email Adventure in Microsoft Access

Every business day (typically M-F), I'll send you an email with information about my ongoing journey as an advanced Access application developer. It will be loaded with my tips and musings.

Subscribe

We won't send you spam. Unsubscribe at any time.

Built with Kit