Alternating colors in a continuous subform with a twist

Hello all,

I have just created a subform to track duplicate entries. What I would like to do is group the duplicates by alternating colors so that they stand out in the subform. The subform is a continuous form made to look like a datasheet.  I would only require 2 different colors. Can this be done via conditional formatting or VBA?
Who is Participating?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
First off, how are you doing the Grouping itself, since no grouping is available on a Form?  

Are you 'pre-grouping' the dups in the form's recordsource ... so that each row will be a different dup? Or can there be several rows of the same dup?  If the latter, then I'm not seeing any easy you could determine when to switch colors.

IF ... each row contains a different row and you just want to alternate colors in this respect, then you can probably use this, although I think I have tested this in the past (not sure) and it can be kind of slow if you have a LOT of rows in the recordset.

As MP suggested, we need a bit more information ...

Patrick MatthewsCommented:
Hello davidlittle2008,

It would be useful to see some sample data.  EE now allows you to directly upload
files to your question.

Please be advised that once you upload a file, it can be publicly accessed, and that it
may not be possible to fully and permanently delete it.  Therefore, be very careful about
posting proprietary, confidential, or other sensitive information.  If necessary, use "fake"
and/or obfuscated data in your sample.


davidlittle2008Author Commented:
I used the Find Duplicate Query Wizard to create a query to find duplicates in my field AppID. This is the SQL it produced:

SELECT tblComplaints.ApplicationID, tblComplaints.ComplaintID, tblComplaints.managedBy, tblComplaints.resolutionStatus, tblComplaints.complaintDate, tblComplaints.Process, [causeType] & ": " & [causeSubType] AS Cause
FROM tblComplaints INNER JOIN tblCause ON tblComplaints.Cause=tblCause.causeID
WHERE (((tblComplaints.ApplicationID) In (SELECT [ApplicationID] FROM [tblComplaints] As Tmp GROUP BY [ApplicationID] HAVING Count(*)>1 )))
ORDER BY tblComplaints.ApplicationID;

I used this as the record source of my subform and it works as I hoped. by sorting the AppID it keeps the duplicate records together in the subform. I was looking for a way to color those with the same AppID one color so you could easily tell how many duplicates there were per appID. ComplaintID is the unique identifier.

In the attached image I would like lines 1 & 2 to be one color 3 & 4 another, etc.

Let me know if you need more info.
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Are there always just two pairs of dups?  On the other hand, IF there are always two pairs - that in itself pretty much makes it stand out to me ...

davidlittle2008Author Commented:
There could be groups of 3 or more unfortunately depending on data entry error.
Unfortunately, you cannot change properties on individual lines on a report that is set to continuous form.  Once it is set to continuous, any color attribute (and most if not all other attributes) you change is changed on all lines of data on the form.
correction ...individual lines on a form that is set...
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
" Once it is set to continuous, any color attribute (and most if not all other attributes) you change is changed on all lines of data on the form."

Well, actually you can with Conditional Formatting ... which I don't think is going  to help here.  Also, the Lebans app does this also.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.