How to highlight cell in subtable

[togo]

Ok, there is one main form and a subform which is linked directly to a table. A VBA routine checks entries and flags certain rows for errors. We want to highlight specific cells that are out of tolerance. Been playing around with the Me.ActiveControl.BackColor = vbYellow but no idea if this can be done to a particular cell on a table, and then no idea if this can be done using VBA. Can it be done? thanks.

[rpeare]

What you want is called conditional formatting. Conditional formatting allows you to check a value and if it meets the criteria you can change pretty much any visible feature of your text box based on the contents.

[togo]

I'm not sure if that'll work for this application. The option is grayed out when selecting the sub form.

[rpeare]

Not the subform itself, the detail section of the subform, that's where you want to do your conditional formatting.

[togo]

thanks, you got it. Now I have to expand my tables or something in order to include spec data for each cell to test against.

[rpeare]

I'm not sure what you mean, if you have individual fields within each record that have a tolerance then you'd have to do conditional formatting on each item with the tolerance for each one in the statement. If you want to highlight the entire record if even one item is out of tolerance that's another question but it can still be done and you wouldn't have to do each field individually you'd just make all of your current fields have a transparent background put in a text box in the back ground (move it to the back) and put conditional formatting on that blank text box with a different conditional format for each possible item that could cause a failure.

[togo]

Well this has gotten confusing. I thought for some reason you could test each cell in this table against another cell in that row. The number in the cell is tested against one of two sets of tolerances. I tried to make another table of the same size and format to hold these tolerance values to be tested against in table #1 using conditional formatting, but that doesn't work. Can conditional formatting be controlled from VBA? for a table? every example of VBA control I've seen has to do with text boxes or other specific controls in forms, not a real data table.

[rpeare]

You can test for multiple criteria in conditional formatting. Let's say you want to highlight Field3 but you want to check to make sure Field1 is > .5 and Field2 > .25 you'd create a conditional formatting formula on Field 3 that read:

Expression IS ([Field1]>0.5) And ([Field2])>0.25

Then set the background color etc for anything that meets the criteria

[togo]

yes but the values I want to test against are in another table.

[rpeare]

If the values are in another table you can link them in to the query driving your report/form via their primary key. For instance, if have an Item A, in your main form you store a value MeasurementA_1, in your 'tolerances' table you'd have the primary key for Item_A and say the MeasurementA_1_Upper and MeasurementA_1_Lower, so you'd link your main table to your tolerances table via the Item_ID (primary key) then in your form you'd just check

Expression IS ([MeasurementA_1] between [MeasurementA_1_Lower] And [MeasurementA_1_Upper]) or something similar

[togo]

unfortunately your explanation kind of leaves me lost.

[rpeare]

Then give an example of your tables (contents) and the primary keys in them

[togo]

Ok the item list goes something like this:

ID - number, given in VBA
DrawingNum - drawing number
UNS - UNS number
and then a list of elements follows:

Cr - double
Ni - double
Fe - double
Mn - double
...
Accept - text

so on and so forth. But in the main form there is an option to enter specs for two different UNS numbers. The VBA routine checks the value of the element against the tolerances for that UNS and changes the accept field into accept, reject or N/A. If any of the elements are out of tolerance, that row(item) is rejected. I notice that the edit feature on this forum doesn't save all the time.

[rpeare]

You mentioned a table with tolerances didn't you? Is the tolerances table connected through the UNS number?

So for instance if your UNS number is X and it has CR, NI, FE and MN measurements in another table you'd have the UNS number, the upper and lower bound for each of the four elements. for the purposes of your question you would link your main table to the tolerances table through the UNS number so your query would have your CR from your main table and the CR_Upper and CR_Lower (just making up field names for the sake of example) so when you check your conditional formatting you would have a formula that says

EXPRESSION IS ([CR] between [CR_Lower] and [CR_Upper]) and have the background color or whatever other properties you want changed with the conditional formatting.

[togo]

the only thing is there is multiple specs sitting in the main form and you can't just link it directly to one set because the link is done using VBA. VBA makes the decision of which row corresponds to which spec when deciding to accept or reject.