Need Help With Conditional Formatting

[Ursa2312]

Hello all, rather new to Access.

So I have a subform where the user is supposed to put the length of the object he is checking. Most of these items have a tolerance, and they all vary by size.

We have over 100 different items, I been using the Conditional Formatting but I did not know it had a limit of only 50 conditions.

This is the condition I used

(Right([Forms]![main test]![Part#],3)="813" And [length]<31.5) Or (Right([Forms]![main test]![Part#],3)="813" And [length]>32.5)

Each part has it's size in it's name in millimeters, that's the 813mm. If the object is out of bounds, the text color should change to red.



So I tried doing this in code builder but I think I'm doing something wrong as the text color isn't changing.

This is what I used, only did one as an example to see if it makes the changes but it doesn't.

If (Right([Forms]![main test]![Part#], 4) = "1320" And Me.length < 51.45) Or (Right([Forms]![main test]![Part#], 4) = "1320" And Me.length > 52.45) Then Me.length.ForeColor = vbRed
End If

I put this in the textbox length, in the afterUpdate() section of the Data tab.

Thank you in advance.

[June7]

Is the form in continuous or datasheet view? Programmatically setting properties will be reflected in ALL records. This is why Conditional Formatting is intended for dynamic formatting of textboxes.

I think you will have to use a table of these 100 different items with a field for the desired color and include that table in the form RecordSource and then you need only one Conditional Format rule.

Don't need the End If with a one-line If Then - or is that End If mate for an outer If Then?

If Right([Forms]![main test]![Part#], 4) = "1320" And (Me.length < 51.45 Or Me.length > 52.45) Then Me.length.ForeColor = vbRed

[Ursa2312]

It was in Single Form view.

I thought I might have to do that, I'll go give it a try then.

So then the table should have 3 fields then right? One for the name of the part, one for minimal and one for maximum tolerance?

[June7]

Should be okay in Single view. But would still use a table instead of code with 100 conditional statements (a very long Select Case structure). Table allows flexibility for modifying specifications.

Length is a reserved word. Should not use reserved words for names. Better textbox name would be tbxLength. Also, should not have spaces and special characters/punctuation (underscore is exception) in names. Better would be PartNum.

Include the table in the form's RecordSource (don't use INNER JOIN).

If Me.tbxLength < Me.MinTol Or Me.tbxLength > Me.MaxTol Then Me.tbxLength.ForeColor = vbRed

Could probably use Conditional Formatting for that criteria and then don't need VBA.