Solved

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

Posted on 2014-01-28
12
1,593 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

863 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

19 Experts available now in Live!

Get 1:1 Help Now