Access 2010: Setting Report Textbox Backcolor from Field Condition

Topic

New Reply

I have an Access 2010 Report in which I’d like to be able to set the Textbox Backcolor property based on the condition of another field value within the same record of the Report.

For example:

I’ve tried the following code in the Report Event Procedures “On Load” and “On Open” but it does not work:(

[INDENT]If Me.Textbox_1 > 0 Then[/INDENT]
[INDENT] Me.Textbox_2.BackColor = vbYellow[/INDENT]
[INDENT]Else[/INDENT]
[INDENT] Me.Textbox_2.BackColor = vbWhite[/INDENT]
[INDENT]End If[/INDENT]

Textbox_1 and Textbox_2 are fields within a single record from a Table or Query.

Any bright ideas that as to how I can do what I desire?

Thanks in anticipation

Trevor

Reply | Quote

Replies

Use conditional formatting. Make the background colour the default ie white and then in the Condition, select Expression is, type [Textbox_1]>0, and then set the Fill Colour to yellow.

To get the colour in the printed output, you may have to set a specific colour printer and the output to colour, not just use the default printer.

Reply | Quote

Does Conditional Formatting work with Foms?

I have tried to use it, but it is either not working or I am doing something wrong.

Bascially I am trying to do what Trevor is doing, but on a form.

ceb39usa

Reply | Quote

Hi Ceb39usa

Conditional formatting works just fine for me on Textboxes in Forms, regardless of whether the data is numeric or alphabetic. However it will not work on Labels or Comboboxes – to do that you need to use a VBA procedure.

For a Label, the VBA code will have to be related to the status of some Textbox which when updated triggers a VBA procedure. Here’s a code sample:
[INDENT]Private Sub Textbox_1_Value_AfterUpdate()
[/INDENT]
[INDENT=2]If Me.Textbox_1_Value> 0 Then ’ Check the value that has been insert in the Textbox
[/INDENT]
[INDENT=3]Me.Label_Test_Value_Description.BackColor = vbYellow ‘ If the value in the Textbox is greater than 0 make the Label (which may be any other Label on the Form) Backcolor yellow
[/INDENT]
[INDENT=2]Else[/INDENT]
[INDENT=3] Me.Label_Test_Value_Description.BackColor = vbWhite ‘ Otherwise make the Backcolor white
[/INDENT]
[INDENT=2]End If
[/INDENT]
[INDENT]End Sub
[/INDENT]
For a Combobox, a suitable procedure in the Combobox “After Update” Event Procedure will do the trick. An example fo the VBA code is:
[INDENT]Private Sub Combo1_AfterUpdate()

If Me.Combo1.Column(1) > 0 Then ’ Check the value of the Combobox column number as desired
[/INDENT]
[INDENT=2]Me.Combo1.BackColor = vbGreen ’ Set the Combobox Backcolor to the desired color if condition True[/INDENT]
[INDENT]Else[/INDENT]
[INDENT=2] Me.Combo1.BackColor = vbWhite ‘ Otherwise set teh Combobox Backcolor to white, in this case[/INDENT]
[INDENT]End If

End Sub
[/INDENT]
Hope this helps.

Cheers

Trevor

Reply | Quote

Thank for the code, it will be useful with what I am trying to do.

As for my Conditional Formatting problem I took a second look at it and found out it was a cockpit problem.

When you give a field a name and refer to it by a slightly different name in the Conditional Format statement, things
do not work. Now that I have entered the correct field name, everything works fine.

I wish all my programming problems were this simple to resolve.

Thanks

ceb39usa

Reply | Quote

Cronks suggestion will work (and is probably the easiest) but if you want to do it in VBA you will need to move your code to the “Detail Format” event procedure.

Reply | Quote

Thanks Cronk and Browni,

I’m still finding my way around Access and hadn’t come across the Conditional Formatting before, even though I should have realised it was probably available as I’ve used it frequently in Excel:o

I tried putting the conditional code I’d previously posted in the Report Detail Event Procedure as VBA but it didn’t want to work for some reason. Having found and tried the conditional formatting worked just right I didn’t bother pursuing that approach any further.

Thanks to both of you for your prompt replies. My problem is now solved

Cheers

Trevor

Reply | Quote