On Report: Hide duplicates

[jplink49]

I have my Report in Tabular Format. I have a field [US018]that contains the word "Composite". The report looks crowded with the word "Composite" being displayed in each record in that field throughout the report. So, I went to the property of that field and set Hide Duplicate to YES. Of course, because of this, only the 1st record with the [US018] field populated “Composite” is going to show per page; however, I want it to show "Composite" one more time on that page, at the 8th record. How can I make that field show "Composite" only at the 1st and 8th record? With a formula/expression at the field [US018] level?

 

[RuralGuy]

What data in your record will indicate this is the 8th record? Will it always be the 8th record?

 

[jplink49]

What data in your record will indicate this is the 8th record? Will it always be the 8th record?

Thanks for responding RuralGuy
I see your point (now that I think about it). Trying to fix this by placing the "Composite" value at a specific place in the record may not be a great idea since the data may change, or I may end up with different numbers of records every time.

Basically, what I want to do is place "Composite" when the field [Name] associated with the[US018] field changes; that is, place "Composite" in [US018] record 1, and when the field [Name]changes (indicating a new sample, usually it's the 8th record) place "Composite" in that [US018] field.

 

[RuralGuy]

Would I be correct in assuming that the [US018] control *always* contains the word "Composite" and all we need to do is play with the visibility of the control?

 

[jplink49]

Yes... exactly. It is *always* "Composite". And yes it's playing with the visibility of the control.

 

[RuralGuy]

At the top of the Report's code module I would create a public variable with a name like:
Dim ThisName As String
Strings initialize to "" or a ZeroLengthString.
Then in the Format event of the Detail section I would have something like:

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

   If [Name] <> ThisName Then
      '-- The name has changed - get the new name
      ThisName = [Name]
      Me.US018.Visible = True
    Else
      Me.US018.Visible = False
    End If

End Sub

BTW, I hope your field name is not Name since that is a reserved word in Access.
Problem names and reserved words in Access

 

[jplink49]

Thanks RuralGuy!

I'm about to try this code now. I'll let you know how it goes.

 

[jplink49]

RuralGuy

It worked. Thanks a lot!

 

[RuralGuy]

You're welcome and thanks for posting back with your success.

 

[M_S_Jones]

Hi guys,

I'm working on what is basically the same scenario, but I'm having problems with RG's code. No matter where I declare the variable it always enters the if statement blank, even if it's had a new value assigned to it; it will contain the value for the remainder of that record, and then revert to being blank. I also found that if I specified that the variable should be "x" it still entered the if statement as "". So I've tried using the value of a textbox instead of the variable, but that doesn't work either. Any ideas?

Thanks

 

[RuralGuy]

Go ahead and post your code starting with Private and ending with End Sub. Use the code tags so it is easier to read.

 

[M_S_Jones]

Right well, I've found that my problem was caused by me not declaring the public variable properly. But I still have a problem. I'm working on a report that lists all of the faults detected in a given time period for each weaver. The report lists each weaver and next to it the type of fault that was detected and then more information about that given fault. I want to hide duplicates on both weaver's names and the type of fault. However, I only want duplicates of the fault type hidden until there is a new weaver's name. So where there is a new weaver listed, even if the fault type is the same as that of the weaver above, I want it displayed.

I set weaver's name's duplicates value to off and ran RG's code over that, but with an additional if, about whether or not the weaver's name field was visible. This all worked, but it was staggered a line, ie when a new weaver's name was visible, the duplicated fault type next to it was hidden, but the next row's was displayed. I interpreted this as a sign that RG's code was being run before the visibility of the that row's weaver's name had been determined, and thus it was using the previous row in its comparison. So I added code to simulate the duplicates off over weaver's name and put that before RG's code, so that I knew the weaver's name's visibility had already been determined by the time the code had been executed. However, now it just runs as if both fields have duplicates turned off. Here's my code:

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

If [Weaver_Name] <> NewVarName Then
    NewVarName = [Weaver_Name]
    Me.txtWeaver_Name.Visible = True
Else
    Me.txtWeaver_Name.Visible = False
End If

If [Fault_Type] <> NewVarText Then
      '-- The name has changed - get the new name
    NewVarText = [Fault_Type]
    Me.txtFault_Type.Visible = True
ElseIf [Fault_Type] = NewVarText Then
    If Me.txtWeaver_Name.IsVisible = False Then
        Me.txtFault_Type.Visible = False
    ElseIf Me.txtFault_Type.IsVisible = True Then
        Me.txtFault_Type.Visible = True
    End If
End If



End Sub

Thanks in advance guys

 

[M_S_Jones]

Ahh! Just realised what the problem was. The final 'if' should have read as follows:

        ElseIf Me.txtWeaver_Name.IsVisible = True Then
            Me.txtFault_Type.Visible = True
        End If

Thanks anyway!

Matthew

 

[RuralGuy]

You always learn more when you find your own problems. Thanks for posting back with your success.