Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    154
    Thanks
    29
    Thanked 3 Times in 2 Posts

    Access 2010: Setting Report Textbox Backcolor from Field Condition

    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

    If Me.Textbox_1 > 0 Then
    Me.Textbox_2.BackColor = vbYellow
    Else
    Me.Textbox_2.BackColor = vbWhite
    End If

    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

  2. #2
    Lounger
    Join Date
    Feb 2011
    Posts
    43
    Thanks
    0
    Thanked 6 Times in 6 Posts
    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.

  3. #3
    WS Lounge VIP Browni's Avatar
    Join Date
    Dec 2009
    Location
    Rochdale, UK
    Posts
    1,650
    Thanks
    38
    Thanked 161 Times in 139 Posts
    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.

  4. #4
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    154
    Thanks
    29
    Thanked 3 Times in 2 Posts
    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

    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

  5. #5
    New Lounger
    Join Date
    Jan 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  6. #6
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    154
    Thanks
    29
    Thanked 3 Times in 2 Posts
    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:
    Private Sub Textbox_1_Value_AfterUpdate()
    If Me.Textbox_1_Value> 0 Then ' Check the value that has been insert in the Textbox
    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
    Else
    Me.Label_Test_Value_Description.BackColor = vbWhite ' Otherwise make the Backcolor white
    End If
    End Sub
    For a Combobox, a suitable procedure in the Combobox "After Update" Event Procedure will do the trick. An example fo the VBA code is:
    Private Sub Combo1_AfterUpdate()

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

    End Sub
    Hope this helps.


    Cheers

    Trevor

  7. #7
    New Lounger
    Join Date
    Jan 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Formatting

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •