BackColor on a Report

[Secret Squirrel]

I'm trying to change the backcolor of my text control on my report. I have
this code in the format event but it doesn't seem to be working.

Select Case Me!ThresholdScore
Case "150"
Me!ThresholdScore.BackColor = 10092543
Case "140"
Me!ThresholdScore.BackColor = 12632256
Case "130"
Me!ThresholdScore.BackColor = 5540756
Case "120"
Me!ThresholdScore.BackColor = 65535
Case "110"
Me!ThresholdScore.BackColor = 255
Case Else
Me!ThresholdScore.BackColor = 255
End Select

Can anyone shed some light on what I'm doing wrong?

 

[Allen Browne]

Suggestions:

1. What data type is ThresholdScore?
If Number, drop the quotes around the values, .e.g:
Case 150
If you are not sure, Access Access:
Debug.Print TypeName(Me!ThresholdScore.Value)

2. Format event of what?
It needs to be the Format event of the section that contains the
ThreshholdScore control.

3. Is there anything else that could be messing up the BackColor?
For example, is the control using Conditional Formatting?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news[email protected]...

 

[Secret Squirrel]

The data type of the thresholdscore is a number. What I want to do is have
the backcolor of the threshold control change depending on the value in it.
The code is in the format event of the detail section of the report.

I removed the "" and still got the same error "Type Mismatch"

I don't see anything else that would be messing up the backcolor. There
isn't any conditional formatting being used for this control.

 

[fredg]

I'm trying to change the backcolor of my text control on my report. I have
this code in the format event but it doesn't seem to be working.

Select Case Me!ThresholdScore
Case "150"
Me!ThresholdScore.BackColor = 10092543
Case "140"
Me!ThresholdScore.BackColor = 12632256
Case "130"
Me!ThresholdScore.BackColor = 5540756
Case "120"
Me!ThresholdScore.BackColor = 65535
Case "110"
Me!ThresholdScore.BackColor = 255
Case Else
Me!ThresholdScore.BackColor = 255
End Select

Can anyone shed some light on what I'm doing wrong?

What datatype is ThreshholdScore?
Sounds like a Number to me.
If that is so, remove the quotes around the value:
Case 150
etc.

Why do you have the same color value for 110 and Case Else?
Just ignore Case 110 and go right to Case Else.

 

[Secret Squirrel]

Hi Fred,

The datatype is a number. I tried removing the quotes and the Case 100 but
still getting the "Type Mismatch" error.

 

[Allen Browne]

That's disturbing. The quotes are wrong for a number, so somewhere along the
line the data type is the problem.

Try asking Access how it understands the data type.
Temporarlily add a text box to the same section of the report, setting its
Control Source property to:
=TypeName([ThresholdScore].[Value])

Trace the field back. If the report is based on Query1, open the Immediate
Window (Ctrl+G), and enter:
? CurrentDb.QueryDefs("Query1").Fields("ThresholdScore").Type
A number between 2 and 7 indicates numeric.
10 is Text.
9 means JET can't tell.
Other values are in the DAO (decimal) column here:
http://allenbrowne.com/ser-49.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

 

[Secret Squirrel]

It must have something to do with the data type because I just tested it
using a number that will trigger the "Case Else" portion and it worked fine
and changed the backcolor to 255. But when I try some of the other cases it
fails. I added that text box as you mentioned and it's telling me it's a
"Long" data type. Isn' t that considered a number type? I cannot run the
query trace since this field is pulling a number from my subreport.

That's disturbing. The quotes are wrong for a number, so somewhere along the
line the data type is the problem.

Try asking Access how it understands the data type.
Temporarlily add a text box to the same section of the report, setting its
Control Source property to:
=TypeName([ThresholdScore].[Value])

Trace the field back. If the report is based on Query1, open the Immediate
Window (Ctrl+G), and enter:
? CurrentDb.QueryDefs("Query1").Fields("ThresholdScore").Type
A number between 2 and 7 indicates numeric.
10 is Text.
9 means JET can't tell.
Other values are in the DAO (decimal) column here:
http://allenbrowne.com/ser-49.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

The data type of the thresholdscore is a number. What I want to do is have
the backcolor of the threshold control change depending on the value in
it.
The code is in the format event of the detail section of the report.

I removed the "" and still got the same error "Type Mismatch"

I don't see anything else that would be messing up the backcolor. There
isn't any conditional formatting being used for this control.

 

[Allen Browne]

Yes, Long is a number (4-byte signed whole number.)

If this control is fetching a number from the subreport, post the expression
you use to get the number from the subreport. I recomment an expression like
this:
http://allenbrowne.com/casu-18.html

Also, set the Format property of this text box to General Number. That can
help Access understand the type.

It is possible that there is a timing issue here.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

It must have something to do with the data type because I just tested it
using a number that will trigger the "Case Else" portion and it worked
fine
and changed the backcolor to 255. But when I try some of the other cases
it
fails. I added that text box as you mentioned and it's telling me it's a
"Long" data type. Isn' t that considered a number type? I cannot run the
query trace since this field is pulling a number from my subreport.

That's disturbing. The quotes are wrong for a number, so somewhere along
the
line the data type is the problem.

Try asking Access how it understands the data type.
Temporarlily add a text box to the same section of the report, setting
its
Control Source property to:
=TypeName([ThresholdScore].[Value])

Trace the field back. If the report is based on Query1, open the
Immediate
Window (Ctrl+G), and enter:
? CurrentDb.QueryDefs("Query1").Fields("ThresholdScore").Type
A number between 2 and 7 indicates numeric.
10 is Text.
9 means JET can't tell.
Other values are in the DAO (decimal) column here:
http://allenbrowne.com/ser-49.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

The data type of the thresholdscore is a number. What I want to do is
have
the backcolor of the threshold control change depending on the value in
it.
The code is in the format event of the detail section of the report.

I removed the "" and still got the same error "Type Mismatch"

I don't see anything else that would be messing up the backcolor. There
isn't any conditional formatting being used for this control.

:

Suggestions:

1. What data type is ThresholdScore?
If Number, drop the quotes around the values, .e.g:
Case 150
If you are not sure, Access Access:
Debug.Print TypeName(Me!ThresholdScore.Value)

2. Format event of what?
It needs to be the Format event of the section that contains the
ThreshholdScore control.

3. Is there anything else that could be messing up the BackColor?
For example, is the control using Conditional Formatting?

message
I'm trying to change the backcolor of my text control on my report.
I
have
this code in the format event but it doesn't seem to be working.

Select Case Me!ThresholdScore
Case "150"
Me!ThresholdScore.BackColor = 10092543
Case "140"
Me!ThresholdScore.BackColor = 12632256
Case "130"
Me!ThresholdScore.BackColor = 5540756
Case "120"
Me!ThresholdScore.BackColor = 65535
Case "110"
Me!ThresholdScore.BackColor = 255
Case Else
Me!ThresholdScore.BackColor = 255
End Select

 

[Secret Squirrel]

I figured it out. The record I was using to test this didn't have any data on
the subreport so it was throwing the #Error into my text control. The Select
Case code couldn't understand so that's why it was erroring out. I modified
my control source to show "0" instead of the #Error so that it will always
have a number.

Thanks for your help Allen. After reading your link about subreports and how
to pull the data to the main report is when I noticed the problem.

Yes, Long is a number (4-byte signed whole number.)

If this control is fetching a number from the subreport, post the expression
you use to get the number from the subreport. I recomment an expression like
this:
http://allenbrowne.com/casu-18.html

Also, set the Format property of this text box to General Number. That can
help Access understand the type.

It is possible that there is a timing issue here.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

It must have something to do with the data type because I just tested it
using a number that will trigger the "Case Else" portion and it worked
fine
and changed the backcolor to 255. But when I try some of the other cases
it
fails. I added that text box as you mentioned and it's telling me it's a
"Long" data type. Isn' t that considered a number type? I cannot run the
query trace since this field is pulling a number from my subreport.

That's disturbing. The quotes are wrong for a number, so somewhere along
the
line the data type is the problem.

Try asking Access how it understands the data type.
Temporarlily add a text box to the same section of the report, setting
its
Control Source property to:
=TypeName([ThresholdScore].[Value])

Trace the field back. If the report is based on Query1, open the
Immediate
Window (Ctrl+G), and enter:
? CurrentDb.QueryDefs("Query1").Fields("ThresholdScore").Type
A number between 2 and 7 indicates numeric.
10 is Text.
9 means JET can't tell.
Other values are in the DAO (decimal) column here:
http://allenbrowne.com/ser-49.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
The data type of the thresholdscore is a number. What I want to do is
have
the backcolor of the threshold control change depending on the value in
it.
The code is in the format event of the detail section of the report.

I removed the "" and still got the same error "Type Mismatch"

I don't see anything else that would be messing up the backcolor. There
isn't any conditional formatting being used for this control.

:

Suggestions:

1. What data type is ThresholdScore?
If Number, drop the quotes around the values, .e.g:
Case 150
If you are not sure, Access Access:
Debug.Print TypeName(Me!ThresholdScore.Value)

2. Format event of what?
It needs to be the Format event of the section that contains the
ThreshholdScore control.

3. Is there anything else that could be messing up the BackColor?
For example, is the control using Conditional Formatting?

message
I'm trying to change the backcolor of my text control on my report.
I
have
this code in the format event but it doesn't seem to be working.

Select Case Me!ThresholdScore
Case "150"
Me!ThresholdScore.BackColor = 10092543
Case "140"
Me!ThresholdScore.BackColor = 12632256
Case "130"
Me!ThresholdScore.BackColor = 5540756
Case "120"
Me!ThresholdScore.BackColor = 65535
Case "110"
Me!ThresholdScore.BackColor = 255
Case Else
Me!ThresholdScore.BackColor = 255
End Select