Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2005
    Posts
    43

    Unanswered: vba code in Access 2000 report to format back color of txt box

    greetings, all:

    in Access 2000, i am working on a report based off of one query. i have a bound text box in the detail section of the report, where, based on 3 other bound text boxes in the header section of the report (this is sectioned/grouped), i'd like the back color to be populated 1 of 3 colors. i believe this can be done w/vba, but i'm pretty new to vba, so am unsure exactly of the code. here's my guess, with the given text:

    txtBox = the text box i want to the back color to change
    number1, number2, number 3 = the 3 text boxes with numbers.

    Code:
    private sub [i'm not sure what to put here?] Detail_Print?()
    
        if me.txtBox.Value <= me.number1.Value then
           me.txtBox.BackColor = vbRed
       elseIf me.txtBox.Value >= me.number2.Value and me.txtBox.Value < me.number3.value then
           me.txtBox.BackColor = vbYellow
       elseIf me.txtBox.Value > me.number3.Value then
           me.txtBox.BackColor = vbGreen
       else me.txtBox.BackColor = vbGray (or some default color)
    
    end sub
    i actually have some code to work off of, but i don't understand the syntax very well:

    Code:
    private sub groupFooter0_Format(cancel as integer, formatCount as integer)
    
    select case reverseCalc
    case False
    
    if me!mth1 <= number1 then
    me!mth1.BackColor = 255
    elseIf me!mth1 > number1 and me!mth1 < number2 then
    me!mth1.BackColor = 65535
    elseIf me!mth1 > number2 then
    me!mth1.BackColor = 65280
    else 
    me!mth1.BackColor = 16777215
    end if
    i guess another question is, i've read in a book the . notion, but not this ! notion. when i've the . - it's in reference to coding behind a form. this ! is behind a report. is there is a difference?
    Mos

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    one of the 'joys' of developing in the VBA arena is that VBA is very forgiving. It makes assumptions about what you are trying to do , it make assumptions about the most likely action a developer would want to make use of on a control

    strictly speaking you probably should use
    me.txtbox.value = me.number1.value
    but you can infact use me.txtbox = me.number1
    or even txtbox=number1
    the exclamation mark ! can be interchangeable with the full stop . as an object iterator. Persoanlly Ive always used ! in recordset manipulation and full stops elsewhere.. becasue thats the way it used to be done in Access.

    there isn't any significant difference betweent he two code samples, except the first one uses the intrinsic vb colour constants, the other supplies the RGB colour constants. Effectively Access doesn't care which style you use, it will resolve any issues internally

    there is no doubt that using the vb colour constants is more legible to the human.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2005
    Posts
    43
    ok, so i'm kinda sorta on the right track?

    my next question is then, how do i start my code?

    Code:
    private sub [i'm not sure what to put here?] Detail_Print?()
    is it

    private Sub Detail_Print()

    private Sub Detail_Format()

    private Sub Report_Load()

    --> and are _Print, _Format, and _Load key words for vba?

    the txtBox is in the Detail section of the report, but the three other numberBoxes are in the Detail'sHeader section of the report. also, these are bound boxes; do i need to create unbound boxes (which doesn't make sense to me)?
    Mos

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Not to change the subject but I found a solution to your other post regarding the height delima get.mos (sorry to interupt you and healdem here). You may want to take a look at it if you get a chance (see the last 2 posts - disregard all my other ones.)
    Last edited by pkstormy; 09-27-07 at 15:44.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Jun 2005
    Posts
    43
    pkstormy, i truly appreciate you taking the time to read my posts! thanks, from a newbie...
    Mos

  6. #6
    Join Date
    Jun 2005
    Posts
    43
    hey, i figured out how to do it using conditional formatting -

    i selected the txtBox, then go to Format > Conditional Formatting...
    Mos

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Yes. Also keep in mind that with the Format > Conditional Formatting you can also set a specific condition to an expression and enter any kind of expressions (ie. =Forms!MyFormName!SomeFieldValue). Also keep in mind that your limited to only 3 conditions so it's sometimes easier to set the colors via vba code if you have more than 3 types (actually 4 with the default.) I will often use the conditional formatting to set the background to light green (indicating the value is a "go"), light yellow (indicating the value is "caution" or in question, or an interum value), and red (or light pink - yuch) (indicating the value is a "no-go" or is blank (which really stands out if you use red.)).

    I only wished Access had a different shade of a "standard" red color (but I can always set it to different red shades.) But bright red or light pink as standard colors - yuch.
    Last edited by pkstormy; 09-27-07 at 20:35.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    conditional formatting is fine, it works
    however you are limited to 4 states (3 conditions + one default)
    you cannot set a whole block of elements based on one test

    say you wanted to change the colour of the text in several controls based on one test.... you can't in conditional formatting.

    I have other gripes with conditional formatting
    >its a pig to debug, mebbe its just me but I prefer to see the code altogether.. that way round its easy to spot a typo in a code window than in the cf boxes
    >if your users decide they want to use different colours you have to do a redesign, if you use code to set the conditions you can pull the colours from a table.

    Im not a fan of cf but if it works for you fine. might be a smart idea to put some comments in the code to say that conditional formatting has been used. It may save you or your successor tearing their hair out trying to work out whats happening.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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