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.
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?Code:Private Sub Form_Current() Me.tbBoxDescription.ForeColor = Nz(DLookup("ColorValue", "Colors", "ColorName = """ & Me.BoxDesColor & """")) End Sub
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.
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.
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 trywill apply to all instances of control on form
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
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.
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.Code:record selector on left side of form
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.
Your suggestion works fine to position the cursor to the beginning of the text. Or, if I instead use: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
the cursor will appear at the end of the current text string.Code:Me.tbBoxDescription.SelStart = Len(Me.tbBoxDescription.Text)
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.
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.
The app is essentially a label creation app. The contents of the boxes are color coded to aid in finding "things" quickly.
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.
So, that's why the user needs control over the color. The "DblClick" event is what pops up the color chart.
ShopBoxes DB is attached. Zip includes small image library. (Remember this is all new and in the process of development)
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.