Mutliple Condictional Formats on 1 Form

[Bantler]

Hello,

I am currently developing some scorecards for work which monitor and collate people performance score.

So far these scorecard have been developed in Excel using VB code to assit in the condictional formatting. I am now thinking it is time to more these spreadsheet into access under one location.

This brings me to my query. I can't seem to find away to have multiple condicational format using if statements. I have a grid (10x10) of text box's which you will enter a scoring number. The condictional format then formats the background colour of the box dependant on the value <>...

If anyone can help me incoroprate the code below into access would really appreciate it.

//Code used in Excel//

For Each cel In Range("B8:B20").Cells

If IsNumeric(cel.Value) And cel.Value <> "" Then
If cel.Value >= 0.905 Then
cel.Interior.ColorIndex = 44
ElseIf cel.Value >= 0.865 And cel.Value <= 0.905 Then
cel.Interior.ColorIndex = 50
ElseIf cel.Value >= 0.825 And cel.Value <= 0.865 Then
cel.Interior.ColorIndex = 35
ElseIf cel.Value >= 0.765 And cel.Value <= 0.825 Then
cel.Interior.ColorIndex = 40
ElseIf cel.Value <= 0.765 Then
cel.Interior.ColorIndex = 22
Else 'default conditions
cel.Interior.ColorIndex = 2
End If
Else
cel.Interior.ColorIndex = 2
End If

Many Thanks

Bantler

Edit....

I have been tired figuring this out but so far no joy.

Here is the code I have.

Private Sub TxtBox_Score_AfterUpdate()
If Me.TxtBoxScore.Value = "8" Then Me.TxtBox_Score.BackColor = vbRed

End If
End Sub

It doesn't seem to like the .Backcolor! Anyone any ideas how to fix this.

Thanks Again

Bantler

 

[Alisa]

You have to right click on the control in design view and choose conditional formatting. You can apply up to 3 conditional formats in Access.
HTH . . .

 

[Bantler]

Hi,

Thanks for your reply.

I know that you can add 3 conditional formats but I need to add at least 4, more in some fields.

Any other ideas?

Bantler

 

[Alisa]

One of the many frustrating constraints in Access . . . you weren't very specific about the type of form you are using, is it a datasheet view? If so, you are constrained by the fact that each control is used multiple times on the form, so if you change the color of the control in code, it is going to change the color for every instance of that control (i.e., the whole column). For datasheet view, you are stuck with conditional formatting. On the other hand, if you are not doing a datasheet view, then you can use code like you were using in excel and just implement the color changes manually in the after update event of each control - me.mycontrolnamehere.backcolor=whatever

 

[Alisa]

This thread might give you some ideas:
http://www.utteraccess.com/forums/showflat.php?Cat=&Number=1640333&Main=1322404