Solved

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

Posted on 2014-01-28
12
1,605 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39817984
@jack
welcome to ee!
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

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

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

813 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

11 Experts available now in Live!

Get 1:1 Help Now