?
Solved

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

Posted on 2014-01-28
12
Medium Priority
?
1,837 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
  • 4
  • 3
  • 2
  • +1
11 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 59
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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 59

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 59
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 59
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

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

601 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