Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2011

    Unanswered: help in formating in vba code

    i have a report build using query and query contains two tables linked

    if a feild in table2 is "good" or "bad" or "normal" then all the report field of table1 and table2 should change backcolour, fontcolour,fontsize,fontstyle,fontweight,

    according to the table2 field value the format should change.

    i tried conditional formating it is not working for all fileds of table1 and table2 how do ia write vba code to this

    or any solution
    pls help thankyou

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    in the reports on format event insert some code

    dim backcolour as long
    dim fontcolour as long
    dim fontsize as double 'use double to allow for decimal point fontheight
    dim fontstyle as 'dunno the correct  datatype
    dim fontweight as 'dunno the correct  datatype. may be best as an enumeration
    select case ucase(mycolumn)
      case = "BAD"
        backcolour = vbred
        fontcolour = vbwhite
        fontsize = 12
        fontstyle = ...
        fontweight = ...
      case = "GOOD"
    end select
    with mycontrol1
      .backcolor = backcolour
      .fontcolor = fontcolour
      .fontsize = fontsize
      .fontstyle = fontstyle
      .fontweight = fontweight
    end with
    with mycontrol2
    with mycontroln
    however setting 5 properties is in my books going over the top. do you believe your users are so thick they wont see say a change in background colour and font colour that you need to individually set all these parameters?

    Im pretty certain that you can set the colours at form level rather than individual control level assuming you want all controls the same style (or lack of)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2011

    Thanks healdem but still some problem

    Private Sub Command89_Click()
    Dim stDocName As String
    stDocName = "PLAN"
    DoCmd.OpenReport stDocName, acPreview
    End Sub

    this was my old code

    new one

    Private Sub Command89_Click()
    Dim stDocName As String
    stDocName = "PLAN" ' name of the report to open
    DoCmd.OpenReport stDocName, acPreview

    Dim backcolour As Long
    Dim fontcolour As Long
    Dim fontsize As Double 'use double to allow for decimal point fontheight
    Select Case UCase(STATUSCER) ' "statuscer" this is the field name of table2 which has values format
    Case Is = "OK"
    backcolour = vbRed
    fontcolour = vbWhite
    fontsize = 12
    Case Is = "pending"
    End Select
    With mycontrol1
    .BackColor = backcolour
    .fontcolor = fontcolour

    End With
    With mycontrol2
    With mycontroln

    End Sub

    here i have error in Default then with mycontrol1
    and other fields like plant, shop, statuscer these 3 are not getting formated
    based on statuscer field

    can pls help

Posting Permissions

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