Changing font format based on value shown in specific field

[David57]

Hello everybody,
I have a subform, say sbfrm1, with 2 fields, Field1 and Field2.
Field1 is a Yes/No field while Field2 has numeric values.
Some records in Field2 may show the same number.
When I click any record within Field1, it corresponds, in the same record but in Field2, to a specific numeric value.
Let us suppose, as an example, that I click, within Field1, the record ID7. It corresponds, in Field2 to the number 426.
What I need is that, following the above action, all records of sbfrm1 showing in Field2 the same number 426 could change their font format (light gray color and smaller font size).
I have been wondering if such a problem can be solved in access.
I was thinking of creating a variable, Var1, where the corresponding value (426 in the example) could be stored. Then, search, in sbfrm1, the records that in Field2 show the value stored in Var1 and change their font format. I don’t know, however, if this can be the right approach to the problem considering my rather poor level of expertise in access vba.
Any useful suggestions are very much welcome.
David.

[NeoPa]

Ah. Even more difficult than I originally thought. Now it's written out clearly in one place it's far easier to understand. BTW Well done for the explanation in English. Not very easy but you've done a pretty good job of being clear.

Now, I assume from this that you're looking for a way of highlighting certain groups of records - those where the numeric part of [Field2] matches a certain value? I also assume that when a separate record is clicked and updated then you want the first set to return to normal. Is that correct? It's not exactly what you said but it seems to make more sense.

This will need to be managed by using Conditional Formatting due to the reasons explained in Why Values in Unbound Form Controls do not Persist. All the Controls are defined once only and all the different values are essentially shown in the same Control.

Anyway, please answer my question above so that I/we can direct you in a more helpful way than simply answering the question (which might lead you to a solution that is less useful than others).

[David57]

Hi NeoPa,
Your assumption is quite correct. I just need to highlight certain groups of records, namely those where the numeric part of [Field2] matches the value corresponding in [Field2] when I click any record within [Field1].
One more example. If I happen to click , within [Field1], the record ID7. Well, since it corresponds to number 135 in [Field2], I need all records in sbfrm1 showing the number 135 in [Field2] to be highlighted by changing their font format.
The change in these records format must be permanent, that is I don’t need them to resume their original format whenever a separate record is clicked and updated.
David

[twinnyfo]

David57,

Formats for fields are never changed permanently. They can only be displayed that way temporarily. This is because Table/Field formatting is at the Table level, not the Field. If you were to open a Table and click on any field or value and change the format to Bold, the entire Table would be changed to Bold.

If you are talking about changing the format of the field just while the form is open, that is another story. Then you would have to save all of these values for the fields in which you click and then cycle through any of those values to see if any fields match those values. Each time you would have to build that into a conditional formatting string that you would then apply to each the controls.

Hope this makes sense.

As NeoPa stated, much more complex than originally understood.

[David57]

Thank you Twinnyfo.
It really seems rather complex. I think it is much easier to select specific records rather than highlighting them.
Thanks to NeoPa, too.
David

[NeoPa]

Let's be clear here. We're discussing the formatting of Controls - not Fields.

We're certainly looking at updating the Conditional Formatting of a Control, or even a set of them if all the controls need to be highlighted, to reflect whether any record with a matching numeric value has been flagged this way. This is made more complicated than necessary by having a single value stored, with other data, within a field. That is poor design. If it's important enough to check and have meaning on its own then it should be stored separately. Joining data together is far simpler than splitting apart atomic data.

It's not impossible, but would certainly require a lot of fiddling about in the Conditional Formatting data.

If it were stored in a more sensible way you could have a simpler query to return the list of values that determine which records to highlight, thus leaving the Conditional Formatting alone. As I say, possible, but far from straightforward. If it's important then it could be explored further but design changes would be recommended first.