Conditional Formatting Using An Expression Comparing A Control Against A Record

[JoshuaRogers]

So here's my latest conundrum.

I have a table that keeps track of when particular types of parts need to be rebuilt or thrown away- each part type has a 'caution' and an 'nfg' value (NFG being the term our engineers use for a part that's outlived its usefulness- I'll let you ponder what it means).

I also have forms set up to display individual parts by serial number as well as their total mileage (and various other metrics).

What I'd like to do is have the control (a text box) that displays the total mileage for a given part to change the back color to yellow if the mileage is greater than the caution value in the table, and then if it's past the NFG value, change the back color again to red.



I've attempted to use the conditional formatting tool with a query (setting up sequential rules for caution and nfg) but that doesn't appear to work, possibly because I'm building the expression wrong. I've tested the query and it returns the correct values.

I can just set up the controls directly by entering the mileage thresholds numerically in the conditional formatting tool, but I'd much rather have a table I can quickly edit.

[June7]

Try:

In the RecordSource for form, include a join (probably a RIGHT outer join) to the table with the threshhold limits for the part and drag the field with caution value to the query grid. Then conditional statement for the mileage textbox can refer to that field.

[JoshuaRogers]

I'd try to do that but the threshold table and the parts tables are unrelated, due to how I've got the database set up. Unfortunately as a side-effect of having part types as individual tables, those tables don't include what type of part that is- most of the time. Some of them do have a Part Type field, but that's for higher granulation, such as identifying whether a Caliper is a Front Left, Front Right, Rear Left, or Rear Right. As a result I've been structuring the query to look for a particular text string in [Mileage Threshold].[Part Type] dependent on the particular form. Which is to say, one query written for each form.

I'm starting to regret not throwing all the parts into one large table. I really am. On the upside this project is doing wonders for brushing up on my Access skills, what with all the repetition...

[June7]

If they aren't related how do you associate a caution value with a part? Show the query you used.

Parts in separate tables - ugh!

[JoshuaRogers]

I don't have access (no pun intended) to the database at the moment, but if I recall the query is more or less like this:

SELECT [Mileage Threshold].[Caution Miles] FROM [Mileage Threshold] WHERE [MileageThreshold].[Part Type] = "Caliper";

And then I've tried setting up the conditional formatting to compare [Forms]![Calipers]![TotalMileageText] to the field value returned by the query ([Query].[Caliper Caution Query].[Caution Miles] I think it is?).

[June7]

Oh no! I see now - parts are segregated into separate type tables and thresholds are by type, not individual part (that aspect does make sense), cannot do join to [Mileage Threshold]. Yes, this does complicate.

Option in lieu of join:

1. Create a field in query of each part type table using a DLookup up expression. Or since you have 2010 use a calculated type field in table. Every record in the query/table will then have this value available.

2. Restructure database.