Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,352

    Set Conditional formatting dynamically

    Are there any facilities within Access to set "Conditional Formatting" dynamically? I'd like to author a simple Function that returns a "Fore Color" value to the Rule of a text box setting the "Font Color".



    Something like:
    Click image for larger version. 

Name:	000.jpg 
Views:	16 
Size:	43.1 KB 
ID:	42339

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,629
    VBA can manipulate Conditional Formatting rules. This is a fairly old and common topic. https://docs.microsoft.com/en-us/off...ormatcondition

    AFAIK, VBA can address only 3 rules.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,352
    Code:
    Private Sub Form_Current()
    Me.tbBoxDescription.ForeColor = Nz(DLookup("ColorValue", "Colors", "ColorName = """ & Me.BoxDesColor & """"))
    End Sub
    The above code works fine but all records used the same color as the first record. The only way I could see this working is to make the text box unbound and set its value from the RecordSource at runtime. Or, am I missing something more fundamental here?

    Post Edit: I think the simple answer here is the difference between single and continuous forms. In this case, I'm talking about a "Continuous" form, so the OnCurrent is only going to fire on the first record. It's more like I need an OnFormat like we have with reports.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,629
    Well, this code is not setting Conditional Formatting rules.

    VBA setting property will apply to all instances of control on form, regardless if control is bound or not. You're right, doesn't work like on report.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,352
    will apply to all instances of control on form
    Yes, that has always been my understanding. I just thought there might be a way to in effect mimic an OnFormat event to set a rule. Oh well, nice try

  6. #6
    ssanfu is online now Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,870
    I did a little searching and found Conditional Formats on Access Forms
    At first, the article refers to only 3 conditional format per control (pre A2007??), but lower in the article, it seems maybe more than 3 conditions can be created.

    Below Figure 3, Conditional format object model is where it gets interesting. (info on the "FormatConditions collection")

    And don't forget to read the 2 links at the bottom of the article:

    This Article Continues Here
    Listing Conditional Formats
    Eventful Formatting for Access Forms
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  7. #7
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,352
    Thanks Steve. I will save and study the references you've suggested. For the time being, I simply created 6 rules to satisfy the immediate needs of the app.
    Click image for larger version. 

Name:	001.jpg 
Views:	20 
Size:	64.7 KB 
ID:	42341

    BTW, a bit off the OP but related, after one changes and requires the affected control, how does one move the focus to the "Record Selector"?

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,629
    Not sure how it is related. Sounds like a new topic to me.

    How does one 'changes and requires the affected control'? Do you mean the record selector on left side of form or navigation control at bottom?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,352
    Code:
    record selector on left side of form
    Yes. After the Requery of the control affected by the record update that controls color of the font, all the text is highlighted undesirably so I simply want to move the focus away from that control.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,629
    That action is not possible. Can set focus to another control or use SelLength = 0 to deselect text.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,352
    Code:
    Private Sub tbBoxDescription_DblClick(Cancel As Integer)
    Dim strHold As String
    strHold = Me.tbBoxDescription
    DoCmd.OpenForm "frmColorChart", , , , , , Me.BoxID     'Give user option to change color of text
    Me.tbBoxDescription.Requery
    Me.tbBoxDescription.SelLength = 0
    End Sub
    Your suggestion works fine to position the cursor to the beginning of the text. Or, if I instead use:
    Code:
    Me.tbBoxDescription.SelStart = Len(Me.tbBoxDescription.Text)
    the cursor will appear at the end of the current text string.

    However, the change to color of the text being displayed does not take affect until I click somewhere else on the form. I've tried moving the focus to other controls but such action had no effect of the conditional formatting of the text box. Since there's no "RePaint" method for text box controls, I'm at a loss as to what to add to the "DblClick" event code to get the text box re-paint reflecting the color change.

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,629
    I did some tests and cannot replicate issue. As soon as data is updated, color changes.

    I don't understand purpose of what you are doing. Why should user be allowed to select color?

    If you want to provide db ...
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,352
    The app is essentially a label creation app. The contents of the boxes are color coded to aid in finding "things" quickly.
    Click image for larger version. 

Name:	004.jpg 
Views:	11 
Size:	25.4 KB 
ID:	42343
    As each box is inventoried and pictured, a continuous form provides a point of entry for the box including a description of its contents. Additionally, the user can select the appropriate color which designates the "class" of content.
    Click image for larger version. 

Name:	002.jpg 
Views:	11 
Size:	42.7 KB 
ID:	42344

    So, that's why the user needs control over the color. The "DblClick" event is what pops up the color chart.

  14. #14
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,352
    ShopBoxes DB is attached. Zip includes small image library. (Remember this is all new and in the process of development)
    Attached Files Attached Files

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,629
    So user is selecting class not color - color associated with class is pre-defined, not set by user.

    Open form with acDialog so code in this procedure is suspended until frmColorChart closes.
    DoCmd.OpenForm "frmColorChart", , , , , acDialog, Me.BoxID

    Then consolidate code in frmColorChart.
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = vbKeyEscape Then DoCmd.Close
    End Sub
    
    Private Sub Black_Click()
    CloseChart "Black"
    End Sub
    
    Private Sub Blue_Click()
    CloseChart "Blue"
    End Sub
    
    Private Sub Green_Click()
    CloseChart "Green"
    End Sub
    
    Private Sub Red_Click()
    CloseChart "Red"
    End Sub
    
    Private Sub Brown_Click()
    CloseChart "Brown"
    End Sub
    
    Private Sub Purple_Click()
    CloseChart "Purple"
    End Sub
    
    Private Sub CloseChart(Color As String)
    CurrentDb.Execute "UPDATE BoxCatalog SET BoxDesColor='" & Color & "' WHERE BoxID=" & Me.OpenArgs
    DoCmd.Close
    End Sub
    

    I can't get SelStart nor SelLength to work. It seems to work when I step code but not otherwise.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. conditional formatting
    By mannyy12 in forum Access
    Replies: 21
    Last Post: 04-05-2020, 10:17 AM
  2. Replies: 2
    Last Post: 11-12-2015, 10:46 AM
  3. Replies: 0
    Last Post: 02-25-2015, 08:09 AM
  4. Replies: 6
    Last Post: 05-26-2013, 09:10 PM
  5. conditional formatting right(..)
    By bbeernaert in forum Access
    Replies: 3
    Last Post: 08-25-2010, 12:33 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums