Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Highlight duplicates (2000)

    I have a report where anytime a field (client number) is duplicated, I would like it to be highlighted. I think I can use conditional formatting -but how would I set up the conditional format for this. Thank you for your help.....

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Highlight duplicates (2000)

    I don't think you can use conditional formatting for comparing values between records.
    Use the following :
    In the code window of the report enter a Dim statement line at the top of the code window like this :
    <pre>Option Compare Database
    Option Explicit
    Dim PreviousField As String</pre>

    In the on open event of the report enter the following code :
    <pre>Private Sub Report_Open(Cancel As Integer)
    PreviousField = ""
    End Sub</pre>

    In the On Format event of the detail section of the report open the following code :
    <pre>Private Sub Detail1_Format(Cancel As Integer, FormatCount As Integer)
    If Me.NameField = PreviousField Then
    Me.NameField.BackColor = 65535 'Set background color to Yellow
    Else
    Me.NameField.BackColor = 16777215 'Set background color to White
    End If
    PreviousField = Me.NameField
    End Sub</pre>

    Replace NameField with your textbox name.
    This code will switch the background color of the textbox to yellow when it is the same as in the previous record. You could use anything else like Fore Color or set the font to bold or what ever you want. Dont forget to undo your setting in the else part.
    Francois

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Highlight duplicates (2000)

    Just an idea (sorry I can't work it out properly... there's no Access on this pc and my knowledge-by-heart of sql... well, yeah... but I hope others will correct me if I'm wrong)...

    Create a calculated field which 'flags' (or even shows it's frequency) if a field value occurs multiple times in a particular recordset. I guess the expression would be something like: Duplicates: Iif(DCount(value ... in field... of table/query...)>1;DCount(...);"")
    Probably you can use this field for the conditional formatting too.

    ps Assuming there's a sorted recordset involved: if you combine this with DFirst... in one way or another, you might even be capable of formatting the first & ... occurence. So if you have to go looking for the other duplicates in the listing, you know which way to go <img src=/S/grin.gif border=0 alt=grin width=15 height=15>. However, for that part it might be easier to just create another report listing only the duplicates...
    ps One disadvantage if there's a large recordset involved: I expect that this might significantly slow down the report...

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Highlight duplicates (2000)

    I used the following in my report as you suggested but the field doesn't change to yellow. Am I doing something wrong?

    Option Compare Database
    Option Explicit
    Dim PreviousField As String
    Private Sub Report_Open(Cancel As Integer)
    PreviousField = ""
    End Sub

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

    If Me.ClientName = PreviousField Then
    Me.ClientName.BackColor = 65535 'Set background color to yellow
    Else
    Me.ClientName.BackColor = 16777215 'Set background color to White
    End If
    PreviousField = Me.ClientName

    End Sub

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Highlight duplicates (2000)

    You must also set the Back Style property of the ClientName text box to Normal instead of Transparent. If it is Transparent, you won't see the BackColor!

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Highlight duplicates (2000)

    That works. It does highlight the duplicates but it also highlights the name at the top of each page which isn't a duplicate...

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Highlight duplicates (2000)

    Can you tell us a bit more about your report? From your description, I guess your report is grouped on a field, with each group beginning on a new page, but I'd like to know for sure.

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Highlight duplicates (2000)

    It isn't grouped. It is a simple list report so people can see when they run the report, where the duplicate entries are. I thought highlighting the duplicates would help to see the duplicates. I do have it sorted by group number and then by Client name in the grouping box.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Highlight duplicates (2000)

    Thanks. The cause is probably that On Format event occurs more than once for the first item on a page. Try changing

    If Me.ClientName = PreviousField Then

    to

    If Me.ClientName = PreviousField And FormatCount = 1 Then

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Highlight duplicates (2000)

    That works. Thanks a bunch to all of you..................

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •