Full Version: Continuous Formatting with VBA
UtterAccess Forums > Microsoft® Access > Access Forms
Hello all,
I've been searching the archives of UA for a solution, and seemed to have found one -
the suggested code looks similar to this :
    For each ctl in me.form
        If TypeOf ctl Is TextBox Then
                ctl.BackColor = vbRed
         End If
    Next ctl

Now, i've stripped it down slightly from what i'm running, but there are no major changes (my backcolour uses a dlookup function to determine the function, and I check if the textbox is hidden before moving focus) - so essentially, I'm using the same code.
The problem is that it doesnt work very well. It counts only one set of controls (as in, one record's worth of the accompanying text boxes). I've stepped through the loop, and it just exits when it gets through one record. If the form is moved repeatitly from design / form view, it will select whichever record had the focus last time it was run as the set of controls to use. I've also tried using For x = 0 to me.count-1 , but again, it only counts one set of records worth of controls.
Basically, what I need is advanced conditional formmatting. I have colours constants stored in a table. I need one textbox to lookup that colour constant based on the string value of the textbox. If the value is there, it becomes that colour. It sounds very simple, but aggrivates me so.
If anyone has any thoughts or suggestions, they'd be greatly appreciated.
Jack Cowley
Sounds like you have a Continuous Form... Take a look here.
Did I fail to mention that crucial part? Whoops.
Yes, I have a Continuous form, and I need to do conditional formatting. However, I need to use a domain aggrigate to determine the formatting to use. Is this possible at all?
You can safely ignore most of what was said in the previous post - I went and checked the example of the previous post I was quoting; it had a different goal than mine - I misinterpreted what was being asked and answered. So, obviously, the suggestion that helped the other fellow did not work for me.
The exact thing I am trying to do is always set one textbox (per record) in a continuous form to a colour constant in a table. So my domain aggrigate is not being used to determine whether it should be coloured, but what colour it will be.
Jack Cowley
Adam -
mmm. I don't see how you can do this, but that does not mean it isn't possible. Conditional Formatting only allows 3 colors and they are chosen in the Conditioanal Formatting dialogs. I don't have an answer, but maybe someone else here does.
If you get no responses to this thread then try another post and see what happens... Good luck!!
Not exactly sure what you want to do. You can certainly use conditional formatting to control the color of a text box
Me.Selector.FormatConditions.Add acFieldValue, acEqual, """" & Me.Country & """"
Me.Selector.FormatConditions(0).BackColor = SelectorAreaColG
Me.Selector.FormatConditions(0).ForeColor = SelectorAreaColG
Selector is the name of a field in this example, if it equals Me.Country then it changes color
That won't work, since the conditions for the format are supplied only by the record that is selected. If the record that is selected would mean that the control should be blue for instance, ALL records will be blue.
The conditional formatting formats the CONTROL, not the record's field...
While I'm not sure of how to incorporate the table level color constants, I'm almost positive this can be achieved using the method that Jack first suggested. (or some derivation of it). How many color constants do you have? Can you post your DB?
Solutions for alternative conditional formatting are best shown and not described. That's why I ask for the DB) frown.gif
Ok, here is a copy of the db - with only the continuous form, and the two tables it references. Its full of nice, new, dummy data.
The form shows two fields (if you see something that doesn't belong, it belonged somewhere else, and I just didn't delete it when I transfered it to a new, friendly db). The field on the left represents a name, the field on the right, a description of the frequency. If you look in the frequency table, there is a matching name, description, and colour constant (just a number between 0x000000 and 0xFFFFFF).
What I need is each textbox that contains the name of the frequency to be coloured according to the colour constant found in the table.
Hopefully someone can shed some light on this frown.gif
Here's an example of what you can do...Or rather three examples. I didn't know if you wanted the forecolor or backcolor of the control or the entire record highlighted. So I did them all.
Sometimes you have to tinker with the font weight if you screen resolution is set to an odd setting but overall it works perfectly.
This solution uses NO VBA and it DOES NOT reference your color tables. But if your entries are pretty static you'll probably never have to mess with it again, anyway.
HAs a side bar, you should get rid of the lookups at the table level. They only confuse you the farther in the development cycle you get.
Let me know if want anything explained in more detail.
Well, the problem with it is that the entries are not static, and the colours can and will change according to an individual users specifications. There can be 0, 1, or many reports shown on that subform, which will be filtered according to date (None of that appears on that form; I stripped it out).
ow, I may be able to use something like what you did - but i'd still have to use the domain lookup to determine colour (and it was back colour) - but if I ever added a new frequency, I'd have to add another box. That, to me, would be too clumsy for what I need it to do.
If you could explain your side bar note to me a little more, too, that would be appreciated. All of the applications I develop with access are code heavy, and behave more like applications than an access database.
That technique will work whether it is a form or subform. I use it all the time. You can always prebuild more boxes than what you need but it doesn't seem like something that would change that often. It's up to you.
bout the lookups:
In your TempPeriodic table you have a field called Frequency. This field has a lookup on it. It shows the Frequency, but in reality it is a Long Integer. You should remove the lookup at the table level and call this field FreqID. Just let it show the number in the table. Then it should be related to the FreqID in the Frequencies table with referential integrity enforced and cascading updates allowed.
Then you wouldn't have to jump through hoops like you did in the query that the form is based on to display the Frequency.
MrSiezen you are completly wrong
If the record that is selected would mean that the control should be blue for instance, ALL records will be blue."
No only the selected record's control would be blue, all the others would be a different color
Conditional formatting allows each record to have different formats (color in this case) for the same control.
see attachment
With regards to changing the table - The sql to do that wasn't difficult, and I haven't noticed any slowdows doing it myself and letting Access make that relationship. But thats beside the point.
Actually, they *will* change all the time. Each individual user can change the colours as often as they want. Those colours will be a table in the front end, so each user can customize how *they* want it to look. So anything that requires maintenence after a colour is changed is a solution that will not work for me.
EFCoins, I will try your solution on Monday.
Thanks again,
I believe the conditional formatting that EFCoins is referring to has a maximum of 3 conditions. So, at most you can have four colors (the default, and the three conditional colors).

You *may* still be able to use your user defined color selector by instead of associating the control with a set condition, like "MONTHLY", use a Dlookup or some global variable. You would have to pre-define the avaiable colors and have all the controls pre-built using these colors. Seems very "doable" the more I think about it. The only con is that they could not choose a "custom" color, only the ones you had pre-selected.
The colors do not have to be predefined, each user can choose what colors they want to use
ycolor = anything the user chooses
Me.Selector.FormatConditions(0).ForeColor = mycolor
What I ment was if you would directly format the control by using VBA, and not with the build in conditional formatting.
I'm sorry if I wasn't clear about that!
But I didn't read your post well enough I see. I thought you were changing the formatting directly, but you are editing the conditional settings. Didn't notice that, and this is actually now to me! Is this still limited to three conditions?
Ok, i've tried a few things.,

First off - I only need *one* condition. It always fires. It always needs to change the colour of the box to technically the same thing - the results of a domain lookup function. So that limitation is no problem.


Otried your method, and it may be my overall unfamiliarity with continuous forms, but I was only able to change the colour of the textbox which was in focus (ie the first one when the form first loads, and i could change focus to others, then design/reload form to cause it to alter another one). If I could find a way of looping through (or even accessing) the other text boxes, it would solve all my needs for conditional formatting.


To do your solution would be too much work for what I get in return. I need this to be as modular as possible, with any colour being able to be chosen (I also need it to have the ability to add new "Frequencies" if there are more in the table). If I fail at this attempt, I am going to abandon the colour system alltogether and create a regular form with some sort of list box.

To all,

Thanks for your ongoing help in this matter- it's really appreciated.

Edited by: adamsherring on Mon May 8 10:07:32 EDT 2006.
Sounds like you are not using a continuous form.
A continuous form will have a single control which is repeated for each record. Continuous formatting will apply to all copies of thatr control. As far as your form is concerned there is only 1 control, there are no other text boxes to loop to.
FOr it could be that your condition is not right
my example was
Me.Selector.FormatConditions.Add acFieldValue, acEqual, """" & Me.Country & """"
but what do you have
No, I'm not confused as to the form I'm using. The form *is* continuous.
My condition *was* right, in my eye. The problem is that the continuous form won't "loop through" like I want it to. My condition was if it equals itself, then dlookup the colour. It only worked for the selected control. And, unfortunatly, I am limited to three contitions, so I am not able to create format conditions for each event. I was hoping that the text boxes would hit the condition on each (as they all equal themselves), then lookup their own colour. It didn't want to work that way. I am going to have to abandon that avenue, as it does not look like it will work satisfactorially while remaining modular.
Thanks to all,
A control can only have 3 conditions, and they must be the same for each record. You can not have a different condition for each record.
That's not entirely what I meant - the condition was the same, in that the characters I type were the same - a domain lookup, but the results were different. I was under the impression that for each text, it would look at the condition, fire, and then apply the results of a domain lookup. But, it only did that for the control that was in focus, and ignored all others.
Thanks anyway,
If you post your conditional formatting code I might see what is going wrong
Me.txtColour.FormatConditions.Add acFieldValue, acEqual, """" & Me.txtColour & """"
Me.txtColour.FormatConditions(0).BackColor = dlookup("FreqColour","Frequencies","Freqname = " & chr(34) & me.txtcolour & chr(34))
ow, this isnt a direct paste from the code, so if there's a little syntical error you see - its not really there. There is always room for a gaping logic error though frown.gif
The code is in form_load event.
HAs I said, this works only for the text that has the focus. If I load it up, switch focus to another record, then flip between design/form view, the record that I switched to prior to flipping design/form view will have the colour change.
The colours *do* change to the right colour -- but only one of them. And, unfortunatly, there can be many different colours/names to choose from.
The idea, if I haven't been clear enough in the past, is that every value that appears in txtColour gets its colour value from the accompanying table.
Again, thanks for your time & effort,
Me.txtColour.FormatConditions.Add acFieldValue, acEqual, """" & Me.txtColour & """"
This selects all Me.txtColour controls which have a value = Me.txtColour of the current record
"there can be many different colours/names to choose from."
You are limited to having 4 colors displayed for each control. Sounds like you want more than 4, which conditional formatting can not do.
Yeah, that was my conclusion as well.
The problem was that I really needed only one condition - always, and domain lookup the colour it is supposed to be.
Apparently, that is too much for the conditional formatting.
It would also be usefull if there was a detail_format event.
Thanks again for the help,
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.