Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Mar 2004
    Location
    Arizona
    Posts
    37
    Thanks
    2
    Thanked 1 Time in 1 Post

    Access 2010 VBA syntax for conditional formating

    I have a form frmRMA_Summary with a sub form sbfRMAs. My subform sbfRMAs is set to Datasheet view. I have been successful using the Conditional Formatting Rules Manager with this statement and desired formatting.

    Code:
    ([bolRepair]=True And (Abs([me.txtReceivedDate]-Date())>14) And IsNull([me.txtShipDate]))
    When I delete the rule and attempt this in VB of the sbfRMAs Form_Current the backcolor and forecolor settings do not take effect. However, the applicable Msgbox appears for each time a record meets the criteria.

    Code:
        If ([bolRepair] = True And (Abs([txtReceivedDate] - Date) > 14) And IsNull([txtShipDate])) Then
    'MsgBox "Overdue, so color should change"
            Me.txtShipDate.BackColor = vbRed
            Me.txtShipDate.ForeColor = vbWhite
    'MsgBox [Forms]![frmRMA_Summary]![sbfRMAs]![txtShipDate].ForeColor
        Else
    'MsgBox "Not overdue, so no color change"
            Me.txtShipDate.BackColor = vbWhite
            Me.txtShipDate.ForeColor = vbBlack
        End If
    The purpose of doing this in code rather than the Rules Manager is that we'd rather all settings, values, etc be controlled from VB. Possibility also exists that we implement more complex code at a later date.

    Thank you,

    Ken
    Dell Precision Mobile Workstation M6500, Intel Core i7-940XM Extreme Edition, 2.13GHz, 8MB cache, 16GB RAM, 500GB HD, 1GB NVIDIA Quadro FX 3800M graphics
    Windows 7 Pro 64-bit, Office 2010 64-bit
    www.CaptainKen.us

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    If you use VBA to change the properties of a control in a continuous form, the change will apply to all records not just the current one. There is only set of properties.

    Conditional formatting is the only way around that, which provides a very good reason to use conditional formatting rather than VBA.

    I can't see anything wrong with the code..but I wondered if the argument above may make the code irrelevant.
    Regards
    John



  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    You can use VBA to manage conditional formatting (as distinct from using code and conditions to control formatting.)

    Here is a lengthy post with some code showing how to do it.
    Regards
    John



  4. The Following User Says Thank You to johnhutchison For This Useful Post:

    CaptainKen (2012-03-05)

  5. #4
    Lounger
    Join Date
    Mar 2004
    Location
    Arizona
    Posts
    37
    Thanks
    2
    Thanked 1 Time in 1 Post
    Awesome John, thank you!!
    Dell Precision Mobile Workstation M6500, Intel Core i7-940XM Extreme Edition, 2.13GHz, 8MB cache, 16GB RAM, 500GB HD, 1GB NVIDIA Quadro FX 3800M graphics
    Windows 7 Pro 64-bit, Office 2010 64-bit
    www.CaptainKen.us

Tags for this Thread

Posting Permissions

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