Access report VBA Conditional Formatting 'Procedure Too Large'

[Deeb74]

I have a report which has 73 text boxes in a row, each record that is pulled up is going to be one of 9 results, I need to color each box according to the result. I started putting together an If then Else statement referencing each box so I did (where A, B etc would be the text boxes)

If Me!A = "0-1" Then
Me!A.ForeColor = 329171
Me!A.BackColor = 329171
ElseIf Me!A = "1-2" Then
Me!A.ForeColor = 33023
Me!A.BackColor = 33023
ElseIf Me!A = "2-3" Then
Me!A.ForeColor = 251574
Me!A.BackColor = 251574
ElseIf Me!A = "3-4" Then
Me!A.ForeColor = 16645487
Me!A.BackColor = 16645487
ElseIf Me!A = "4-5" Then
Me!A.ForeColor = 8453888
Me!A.BackColor = 8453888
ElseIf Me!A = "5-6" Then
Me!A.ForeColor = 12615680
Me!A.BackColor = 12615680
ElseIf Me!A = "6-7" Then
Me!A.ForeColor = 16744703
Me!A.BackColor = 16744703
ElseIf Me!A = "7-8" Then
Me!A.ForeColor = 65535
Me!A.BackColor = 65535
ElseIf Me!A = "8-9" Then
Me!A.ForeColor = 32896
Me!A.BackColor = 32896
Else
Me!A.ForeColor = 0
Me!A.BackColor = 16777215

End If

If Me!B = "0-1" Then
Me!B.ForeColor = 329171
Me!B.BackColor = 329171
ElseIf Me!B = "1-2" Then
Me!B.ForeColor = 33023
Me!B.BackColor = 33023
ElseIf Me!B = "2-3" Then
Me!B.ForeColor = 251574
Me!B.BackColor = 251574
ElseIf Me!B = "3-4" Then
Me!B.ForeColor = 16645487
Me!B.BackColor = 16645487
ElseIf Me!B = "4-5" Then
Me!B.ForeColor = 8453888
Me!B.BackColor = 8453888
ElseIf Me!B = "5-6" Then
Me!B.ForeColor = 12615680
Me!B.BackColor = 12615680
ElseIf Me!B = "6-7" Then
Me!B.ForeColor = 16744703
Me!B.BackColor = 16744703
ElseIf Me!B = "7-8" Then
Me!B.ForeColor = 65535
Me!B.BackColor = 65535
ElseIf Me!B = "8-9" Then
Me!B.ForeColor = 32896
Me!B.BackColor = 32896
Else
Me!B.ForeColor = 0
Me!B.BackColor = 16777215

End If

Etc, for each of the 73 boxes then I get the message Procedure too large. Obviously there is a lot of repetition in my code - I am totally new to this - so I wondered if there is a way to use the code once but reference all of the 73 boxes?

Hoping someone can help - Thanks

 

[xlbo]

Basic logic

Loop through controls in the report / form
test type of each control
if textbox then
apply colouring logic
else
do nothing
end if
End Loop

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

also you will get much better responses in forum707 and

if this is MS Access, then forum705.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

It looks like you should have a table of values and colors:
[tt][blue]
tblValueColors
TheValue TheColor
0-1 329171
1-2 33023
2-3 251574[/blue][/tt]
You can then use code to loop through the controls something like the following. I'm not sure this will work but it should provide the basic structure.

Dim intI as integer
Dim intA as Integer
Dim lngColor as Long
intA = Asc("A")  '65
For intI = intA to intA + 73
     Me(Chr(intI)).ForeColor = DLookup("TheColor","tblValueColors","TheValue='" & Me(Chr(intI)) & "'")
     Me(Chr(intI)).BackColor = Me(Chr(intI)).ForeColor
Next

Duane
Hook'D on Access
MS Access MVP

 

[Deeb74]

Thank you everyone for your fantastic comments, I'll try all of them and let you know what works.

 

[dhookom]

I just tried this and amazingly it worked based on my tables which I think match yours:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim intI As Integer
    Dim intA As Integer
    Dim lngColor As Long
    intA = Asc("A")  '65
    For intI = intA To Asc("G")  'change the G to your max
         Me(Chr(intI)).ForeColor = DLookup("TheColor", "tblValueColors", "TheValue='" & Me(Chr(intI)) & "'")
         Me(Chr(intI)).BackColor = Me(Chr(intI)).ForeColor
    Next
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[Deeb74]

Thank you everyone for your amazing help. I totally got it to work. I'm so happy, thank you thank you.