Need Conditional Formatting help

  • Thread starter Thread starter Rachael
  • Start date Start date
R

Rachael

I'm using Access 2000 and have a report that has a different logo at
the top depending on which model of our product the report is run on
(the model selection is made by a form passing the value to the
report's underlying query).

In addition to the logo changing, there are several text boxes on this
report that I would like to have the text color change to match the
different logos - based on the same product model selection. There
are too many products to use the Conditional Formatting that's located
on the Format menu...so I'm pretty sure it has to be done through
code. But I'm not sure where to start or where to locate the code -
with the text boxes themselves or in the Report_Open procedure.

I hope this is enough information....any suggestions would be greatly
appreciated.

Thanks,

Rachael
 
Rachael said:
I'm using Access 2000 and have a report that has a different logo at
the top depending on which model of our product the report is run on
(the model selection is made by a form passing the value to the
report's underlying query).

In addition to the logo changing, there are several text boxes on this
report that I would like to have the text color change to match the
different logos - based on the same product model selection. There
are too many products to use the Conditional Formatting that's located
on the Format menu...so I'm pretty sure it has to be done through
code. But I'm not sure where to start or where to locate the code -
with the text boxes themselves or in the Report_Open procedure.
Click to expand...

It would probably go in the report's Open event, but if the
logo can change from one detail to another, then is would be
in the Format event procedure of the section containing the
controls you're operating on.

Where is the logo data and the related color code stored?
Is the logo text or a graphic?

Assuming that you have a string variable containg the text
of a logo, the color code in a long variable, and you want
the logo to appear in a Label control, then the code could
be something like:
Me.lblLogo.Caption = strLogo
Me.txtfielda.ForeColor = lngLogoColor
Me.txtfieldb.ForeColor = lngLogoColor
. . .
or if the logo text and color code are in text boxes on the
form:
Me.lblLogo.Caption = Forms!theform.txtLogo
Me.txtfielda.ForeColor = Forms!theform.txtColor
Me.txtfieldb.ForeColor = Forms!theform.txtColor
. . .
 
It would probably go in the report's Open event, but if the
logo can change from one detail to another, then is would be
in the Format event procedure of the section containing the
controls you're operating on.

Where is the logo data and the related color code stored?
Is the logo text or a graphic?

Assuming that you have a string variable containg the text
of a logo, the color code in a long variable, and you want
the logo to appear in a Label control, then the code could
be something like:
Me.lblLogo.Caption = strLogo
Me.txtfielda.ForeColor = lngLogoColor
Me.txtfieldb.ForeColor = lngLogoColor
. . .
or if the logo text and color code are in text boxes on the
form:
Me.lblLogo.Caption = Forms!theform.txtLogo
Me.txtfielda.ForeColor = Forms!theform.txtColor
Me.txtfieldb.ForeColor = Forms!theform.txtColor
. . .
Click to expand...

Thanks for your reply...

I work for an airplane manufacturer, and we have a different logo for
each model of aircraft that we sell. In this particular database, the
logos for each model are stored as a jpg graphic file in a table. The
query that runs the report pulls in the appropriate model's logo into
an OLE object box in the header of the report. So the logo isn't a
problem (not at this point anyway...lol).

But each of the logo's has it's own color scheme. So I want to jazz
up the report a little and make some of the text boxes match the main
color of the logo. The color codes are not stored anywhere in the
database, but I do have the RGB or hex values for each of the colors I
need. I need to know how to code the report so it assigns those color
values to the text boxes based on the model that was selected for the
report.

As I mentioned in my original message, the model is selected by the
user via a form and that value is passed into the query that underlies
the report when a "Preview" button is clicked on the form. At that
point, they are viewing the report.

Hope that makes sense...it's hard to explain this stuff!

Thanks,

Rachael
 
Rachael said:
Thanks for your reply...

I work for an airplane manufacturer, and we have a different logo for
each model of aircraft that we sell. In this particular database, the
logos for each model are stored as a jpg graphic file in a table. The
query that runs the report pulls in the appropriate model's logo into
an OLE object box in the header of the report. So the logo isn't a
problem (not at this point anyway...lol).

But each of the logo's has it's own color scheme. So I want to jazz
up the report a little and make some of the text boxes match the main
color of the logo. The color codes are not stored anywhere in the
database, but I do have the RGB or hex values for each of the colors I
need. I need to know how to code the report so it assigns those color
values to the text boxes based on the model that was selected for the
report.
Click to expand...

The color codes WILL be in the database, somewhere. If they
weren't, you would not be able to utilize them. It's not a
good idea to just plug those values into lines of code - it
will work, but will also be a complete pain to change
sometime in the future. If that's what you mean by
"I do have the RGB or hex values", then, assuming you have a
bound text box on the report (named txtmodel) that tells you
which model, the code could be:

Dim lngLogoColor As Long
Select Case txtmodel
Case "abc"
lngLogoColor = anrgbcolor
Case "pqr"
lngLogoColor = anotherrgbcolor
Case "abc"
lngLogoColor = somergbcolor
. . .
Case Else
lngLogoColor = vbBlack
End Select
Me.txtfielda.ForeColor = lngLogoColor
Me.txtfieldb.ForeColor = lngLogoColor
. . .

It would be better if you had a table with the model data
including the color code to use for the model. If you did
this, you could Join that table into the report's record
source query and use the color code field to set the
ForeColor property.

As I mentioned in my original message, the model is selected by the
user via a form and that value is passed into the query that underlies
the report when a "Preview" button is clicked on the form. At that
point, they are viewing the report.
Click to expand...

If the only place where the model is available is on the
form, then you could use the above code by changing the
select statement to:

Select Case Forms!theform.txtmodel
 
The color codes WILL be in the database, somewhere. If they
weren't, you would not be able to utilize them. It's not a
good idea to just plug those values into lines of code - it
will work, but will also be a complete pain to change
sometime in the future. If that's what you mean by
"I do have the RGB or hex values", then, assuming you have a
bound text box on the report (named txtmodel) that tells you
which model, the code could be:

Dim lngLogoColor As Long
Select Case txtmodel
Case "abc"
lngLogoColor = anrgbcolor
Case "pqr"
lngLogoColor = anotherrgbcolor
Case "abc"
lngLogoColor = somergbcolor
. . .
Case Else
lngLogoColor = vbBlack
End Select
Me.txtfielda.ForeColor = lngLogoColor
Me.txtfieldb.ForeColor = lngLogoColor
. . .

It would be better if you had a table with the model data
including the color code to use for the model. If you did
this, you could Join that table into the report's record
source query and use the color code field to set the
ForeColor property.



If the only place where the model is available is on the
form, then you could use the above code by changing the
select statement to:

Select Case Forms!theform.txtmodel
Click to expand...


Thanks so much for your help! I ended up using the Select Case for
now (there are only 9 models...so it's not big deal to update the
colors if I need to). It wouldn't work in the Report Open event
though...I had to put it in the OnFormat event for the section of the
report the text boxes are in.

I tried putting the color numbers in a table like you suggested, but I
couldn't figure out how to change the formatting of the text boxes
based on the query. If you have a spare moment, could you explain
that one a bit further?

The main thing is, it works now. Thanks again for your help.

Rachael
 
Rachael said:
I tried putting the color numbers in a table like you suggested, but I
couldn't figure out how to change the formatting of the text boxes
based on the query. If you have a spare moment, could you explain
that one a bit further?
Click to expand...


The table where the color codes and logo caption could be
placed is the table with the model names and other model
specific data. If that were the case, then the report's
query would include the color codes and logo caption fields
in the report's record source data so your program would not
have to calculate them. Just assign them to the text boxes
ForeColor property:

Me.lblLogo.Caption = Logofield
Me.txtfielda.ForeColor = LogoColorfield
Me.txtfieldb.ForeColor = LogoColorfield

I'm sorry if that's too vague help here, but I would have to
see your table structure and the report's record source
query before I could provide more specific suggestions. At
this point I don't even see how the form is used to initiate
the report.
 
The table where the color codes and logo caption could be
placed is the table with the model names and other model
specific data. If that were the case, then the report's
query would include the color codes and logo caption fields
in the report's record source data so your program would not
have to calculate them. Just assign them to the text boxes
ForeColor property:

Me.lblLogo.Caption = Logofield
Me.txtfielda.ForeColor = LogoColorfield
Me.txtfieldb.ForeColor = LogoColorfield

I'm sorry if that's too vague help here, but I would have to
see your table structure and the report's record source
query before I could provide more specific suggestions. At
this point I don't even see how the form is used to initiate
the report.
Click to expand...


Nope, that wasn't vague at all... And it worked! Thanks. That was a
much easier way of doing it. I'm kinda embarrassed now that I
couldn't figure that out!

Thanks again!

Rachael
 
Rachael said:
Nope, that wasn't vague at all... And it worked! Thanks. That was a
much easier way of doing it. I'm kinda embarrassed now that I
couldn't figure that out!

Thanks again!

Rachael
Click to expand...


Hey, it's great that you got it working, but don't be
embarrassed. We all run into a mental block now and then.
 
Back
Top