Alternating colors in a continuous subform with a twist

Posted on 2009-02-14
Medium Priority
Last Modified: 2012-06-22
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?
Question by:davidlittle2008
  • 3
  • 2
  • 2
  • +1
LVL 93

Expert Comment

by:Patrick Matthews
ID: 23641717
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.


LVL 75

Accepted Solution

DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 23641963
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 ...


Author Comment

ID: 23642093
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.
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

LVL 75
ID: 23642111
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 ...


Author Comment

ID: 23642136
There could be groups of 3 or more unfortunately depending on data entry error.

Expert Comment

ID: 23646065
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.

Expert Comment

ID: 23646071
correction ...individual lines on a form that is set...
LVL 75
ID: 23646185
" 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.


Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question