Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.
How can I change controls on each row of a continuous form using VBA - Access 2013?
I have a continuous form. Based on the value in each field I want to change the color of that cell. The color code is stored in another field of the record. I do not want to do this with conditional formatting
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
If you have a limited amount of colors, you could add a 1x1 bitmap to the "Shared Images", then give it a name matching the color, and bind an imagecontrol with Zoom to the field.
Alternatively, you could use the paint event to change the color.
Alternatively, you could use the paint event to change the color.
I am not aware of any disadvantages of using conditional formatting, ...so as Jim asked, ...can you tell us the reason why you are not considering this as an option...?
There are 21 fields on each row of the continuous form. Each field could have 1 of 8 colors assigned to it. The corresponding color is kept in another 21 hidden fields of the form. Based on the value of the hidden field I want to change the color.
OK, your the first person in a long time that actually can't use conditional formatting<g>.
Attached is the sample DB that shows you how to do it.
With the technique I described, you can do any number of columns and conditions.
Jim.
ContinuousFormColors.MDB
Attached is the sample DB that shows you how to do it.
With the technique I described, you can do any number of columns and conditions.
Jim.
ContinuousFormColors.MDB
Jim,
I found no stated limits on the number of Rules in Access 2010-2013,.
I got up to 11 rules/11 fields with no problem...
I found no stated limits on the number of Rules in Access 2010-2013,.
I got up to 11 rules/11 fields with no problem...
@Jeff,
Thanks for posting that. I haven't gone back and looked at conditional formatting for quite some time (don't often use it).
Jim.
Thanks for posting that. I haven't gone back and looked at conditional formatting for quite some time (don't often use it).
Jim.
Jim,
I'm running Access 2013 under Office 365, I can't open your sample database.
The basic question is really how can you get at controls in a continuous form after each row is rendered?
Dave
I'm running Access 2013 under Office 365, I can't open your sample database.
The basic question is really how can you get at controls in a continuous form after each row is rendered?
Dave
Sorry about that. New one is attached.
<<The basic question is really how can you get at controls in a continuous form after each row is rendered?>>
You don't, but the trick with continuous forms is that as long as you have bound controls, they are treated individually.
That's why I mentioned temp tables.
What you do is set all your current controls backgrounds to transparent. Then place one (or more) image controls behind the normal controls (send to back) and set the mode to stretch. These are bound to a bitmap field.
I create a temp table with the PK from my main table along with the DisplayColorID field, then base the form on a query that joins the table I'm working to the temp table, and also my bitmap table (I have seen where someone generated the bitmaps in memory, but I've never found the need to go that route - not sure I still have the example either).
From that point on, it's automatic. I change the color then by updating the DisplayColorID field in the temp table.
Jim.
ContinuousFormColors_A2010.accdb
<<The basic question is really how can you get at controls in a continuous form after each row is rendered?>>
You don't, but the trick with continuous forms is that as long as you have bound controls, they are treated individually.
That's why I mentioned temp tables.
What you do is set all your current controls backgrounds to transparent. Then place one (or more) image controls behind the normal controls (send to back) and set the mode to stretch. These are bound to a bitmap field.
I create a temp table with the PK from my main table along with the DisplayColorID field, then base the form on a query that joins the table I'm working to the temp table, and also my bitmap table (I have seen where someone generated the bitmaps in memory, but I've never found the need to go that route - not sure I still have the example either).
From that point on, it's automatic. I change the color then by updating the DisplayColorID field in the temp table.
Jim.
ContinuousFormColors_A2010.accdb
See the Access Archon article I wrote on this topic:
http://www.helenfeddema.com/Files/accarch173.zip
Here is a screen shot of the form:
http://www.helenfeddema.com/Files/accarch173.zip
Here is a screen shot of the form:
Clever idea. However, I went with conditional formatting. Works well. The additional issue was I had to look at another field to determine the color. The data in the cell would not indicate the color.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access
From novice to tech pro — start learning today.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
Reason? There is the old school method of setting a controls background transparent, then placing a filled control behind it.
In doing that, you can display a color, graphic, or whatever. For colors, you can use a 1x1 bitmap stored in a "color table" and have it fill the control. Quite fast that way and your not limited on the number of conditions.
But it poses some problems as well. Often you must then use a temp table for the form.
Conditional formatting is much easier. i can walk you through the other however and have a sample database that I can attach that shows you how to do it.
Jim.