Access Userform Conditional Formatting

[Enchantix]

Hi guys,

i need help to get the following to work...

Private Sub Thickness_1_BeforeUpdate(Cancel As Integer)


If Me.Target_Thickness.Value = 2.6 Then

If Me.Thickness_1.Value < 1.5 Then

Me.Thickness_1.BackColor = vbRed

Me.Thickness_1.ForeColor = vbWhite


End If
End If

If Me.Target_Thickness.Value = 2.6 Then

If Me.Thickness_1.Value > 2.9 Then

Me.Thickness_1.BackColor = vbRed

Me.Thickness_1.ForeColor = vbWhite


ElseIf Me.Thickness_1 > 2.9 And Me.Thickness_1 < 1.5 Then

Me.Thickness_1.BackColor = vbGreen

Me.Thickness_1.ForeColor = vbBlue

End If
End If


End Sub

The part in Red doesnt want to work

Any ideas would be greatly appreciated

please and thanks

 

[Joe4]

[COLOR=#0000FF]If Me.Thickness_1.Value > 2.9 Then

Me.Thickness_1.BackColor = vbRed

Me.Thickness_1.ForeColor = vbWhite[/COLOR]


[COLOR=#FF0000]ElseIf Me.Thickness_1 > 2.9 And Me.Thickness_1 < 1.5 Then[/COLOR]

A few things.

How can Me.Thickness_1 be greater than 2.9 AND less than 1.5 at the same time? That is not possible. Maybe you meant to use OR instead of AND?

Also, in the part of code I cut from your post above, the first IF is checking to see if Me.Thickness_1 is greater than 2.9. However, within that same IF statement, you have an ELSEIF (your first line in red), which is again, checking to see if Me.Thickness_1 is greater than 2.9 (and checking another thing).

The way an IF statement works, is if the initial IF statement is TRUE, it will not go to any ELSE clause under that IF statement. So you will never get to the red part.

 

[Joe4]

Also, note that Access Forms offers Conditional Formatting (no VBA required).
See: Conditional Formatting Microsoft Access Data | Database Solutions for Microsoft Access | databasedev.co.uk

 

[Enchantix]

[COLOR=#0000FF]If Me.Thickness_1.Value > 2.9 Then

Me.Thickness_1.BackColor = vbRed

Me.Thickness_1.ForeColor = vbWhite[/COLOR]


[COLOR=#FF0000]ElseIf Me.Thickness_1 > 2.9 And Me.Thickness_1 < 1.5 Then[/COLOR]

A few things.

How can Me.Thickness_1 be greater than 2.9 AND less than 1.5 at the same time? That is not possible. Maybe you meant to use OR instead of AND?

Also, in the part of code I cut from your post above, the first IF is checking to see if Me.Thickness_1 is greater than 2.9. However, within that same IF statement, you have an ELSEIF (your first line in red), which is again, checking to see if Me.Thickness_1 is greater than 2.9 (and checking another thing).

The way an IF statement works, is if the initial IF statement is TRUE, it will not go to any ELSE clause under that IF statement. So you will never get to the red part.

Thank You for your response Joe,


Apologies for the rookie mistakes, im still learning.

its clear what the issue was.

I've managed to get it to work and id like to continue using code instead of the condtional formatting access has.



Private Sub Thickness_1_BeforeUpdate(Cancel As Integer)


Where: Me.Target_Thickness.Value = 2.6

If Me.Thickness_1.Value > 2.9 Or Me.Thickness_1.Value < 1.5 Then

Me.Thickness_1.BackColor = vbRed

Me.Thickness_1.ForeColor = vbWhite


ElseIf Me.Thickness_1.Value < 2.9 Or Me.Thickness_1.Value > 1.5 < 2.9 Then

Me.Thickness_1.BackColor = vbWhite

Me.Thickness_1.ForeColor = vbBlack

End If

End Sub


Now say if i was to add more conditions for Me.thickness_1 based on the value of Me.Target Thickness. How would i do that?

I.e.

Where: Me.Target_Thickness.Value = 3.2

If Me.Thickness_1.Value > 3.9 Or Me.Thickness_1.Value < 2.7 Then

Me.Thickness_1.BackColor = vbRed

Me.Thickness_1.ForeColor = vbWhite


ElseIf Me.Thickness_1.Value < 3.9 Or Me.Thickness_1.Value > 2.7 < 3.9 Then

Me.Thickness_1.BackColor = vbWhite

Me.Thickness_1.ForeColor = vbBlack

End If


Kind regards,
Ibbz

 

[Joe4]

Note that you can often use CASE statements in place of IF THEN (or in conjunction with them). See this links for details on this: MS Access: Case Statement. These are great when you are checking something, and it could be many different values that you want to all handle differently.

So I cleaned up your code a bit. You were having some issues with how you wrote the between checks. See if this works for you:

Private Sub Thickness_1_BeforeUpdate(Cancel As Integer)

    Select Case Me.Target_Thickness.Value
    
        Case 2.6
            If (Me.Thickness_1.Value > 2.9) Or (Me.Thickness_1.Value < 1.5) Then
                Me.Thickness_1.BackColor = vbRed
                Me.Thickness_1.ForeColor = vbWhite
            ElseIf (Me.Thickness_1.Value > 1.5) And (Me.Thickness_1.Value < 2.9) Then
                Me.Thickness_1.BackColor = vbWhite
                Me.Thickness_1.ForeColor = vbBlack
            End If

        Case 3.2
            If (Me.Thickness_1.Value > 3.9) Or (Me.Thickness_1.Value < 2.7) Then
                Me.Thickness_1.BackColor = vbRed
                Me.Thickness_1.ForeColor = vbWhite
            ElseIf (Me.Thickness_1.Value > 2.7) And (Me.Thickness_1.Value < 3.9) Then
                Me.Thickness_1.BackColor = vbWhite
                Me.Thickness_1.ForeColor = vbBlack
            End If
    
    End Select

End Sub