Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Alternate shading of continuous form driven by a change in field value

Posted on 2014-01-28
12
Medium Priority
?
1,746 Views
Last Modified: 2014-02-24
Hi all

I have a continuous form in which records are grouped by a field which can have from 1 to 6 rows for each value of the field. I want to visually group these by the field by changing the back colour so that all the rows for the same value are shaded the same. I am happy to use a two colour option a bit like the built in option for alternate rows, but needs to shade all the records for a given field value the same.

As the possible list values is quite large (but never more than 6 or so on any one form), I am reluctant to try and allocate a set colour for each value - in fact a grey and white might be easiest.

Any suggestions?

Kelvin
0
Comment
Question by:Kelvin Sparks
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
12 Comments
 
LVL 4

Expert Comment

by:Jack Leach
ID: 39817362
If you're using a newer version of Access (2010+, I think off the top of my head), you can set alternating back colors in reports for individual rows, or at the group level.

Select your desired group section of the report and open the Properties dialog, under the Format tab and take a look at the relevant properties to find the back color and set it to alternating.  You may find that you'll want the detail rows within the group to NOT alternate colors per row, so you'll probably want to change that as well.

hth
-jack
0
 
LVL 58
ID: 39817483
You can do this with conditional  formatting or by using a control behind all the others, and setting the controls back ground to transparent.

I've attached a sample database of the latter.  

Let me know if you need more
Jim.
ContinuousFormColors.MDB
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39817984
@jack
welcome to ee!
0
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 4

Expert Comment

by:Jack Leach
ID: 39818193
Thanks Rey!  Just trying to find my way around now...
0
 
LVL 22

Author Comment

by:Kelvin Sparks
ID: 39828835
Thanks Guys

Will be a day or so before  get back onto this. Although I'm developing this for the client using Access 2010, I have to use Access 2003 format for backward compatibility (there may be a chance they will wear 2007), so any solution will need that. Also, one solution looked at functionality for reports - this is for user readability in data entry forms, not reports.


Kelvin
0
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 1200 total points
ID: 39829709
With 2003 or 2007, either method will work that I posted.

Conditional formatting is the easist to use, and for this, is probably the route I'd go.

The other technique is the "old school" method of doing this before conditional formatting was added (A97 and back), but it also allows you to do things besides color, like displaying a graphic.

Jim.
0
 
LVL 22

Author Comment

by:Kelvin Sparks
ID: 39866672
Thanks guys,

I'm tempted to use conditional formatting, but the client wants alternate colours changing on the initial field value. There will be between 1 and 7 rows for each initial field and as this is a subform, there can be any combination of initial fields on the form. The only thing I can think of here is to assign each initial value a sequence number as the records are created (unlikely to add additional records after creation) and colour by odd/even value of the sequence. Any better ideas?

Remember, this will have to run in Access 2003 and/or 2007


Kelvin
0
 
LVL 58
ID: 39867202
<<The only thing I can think of here is to assign each initial value a sequence number as the records are created (unlikely to add additional records after creation) and colour by odd/even value of the sequence. Any better ideas?>>

That is one way to do it.  There are others.  Take a look at frmAlternateRows OnOpen logic in the example I posted.

It clears/creates a linking table, then assigns a flag in qryfrmAlternateRowsSetColor.

This query has a call to GetColor(), which simply toggles back and forth between two colors based on the last one assigned.

There's no reason you could not warp all that logic into a procedure, which you would call every time a new record is added in your form.  On a small recordset, it would be fast enough.

 Another would be to use conditional formatting using the records absolute position property in the recordset (odd's are one color, evens another).

Jim.
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 800 total points
ID: 39867270
<The only thing I can think of here is to assign each initial value a sequence number as the records are created>

that is the way it is done on the link that i posted (that was deleted) :-(
0
 
LVL 58
ID: 39867437
<<that is the way it is done on the link that i posted (that was deleted) :-( >>

  Not quite.  The link you had posted had a video showing how to color a row based on a flag, but not how to set that flag.

Jim.
0
 
LVL 22

Author Closing Comment

by:Kelvin Sparks
ID: 39884835
Thanks Guys, This issue has been parked by client for now, but appreciate the input of a couple of the best in the business!

Kelvin
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

718 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