Solved

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

Posted on 2014-01-28
12
1,553 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
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 57
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 119

Expert Comment

by:Rey Obrero
ID: 39817984
@jack
welcome to ee!
0
 
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 300 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 57
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 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 200 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 57
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now