Access 2016 Continuous Form Change Color One Box
Format continuous forms
Format continuous forms
(OP)
Is it possible to change a backgound color of the text boxes of a single record based on conditions in continuous forms without effecting the other records
Source: https://www.tek-tips.com/viewthread.cfm?qid=1248876
RE: Format continuous forms
You bet! In design view of your form, select the text box you want to check for conditions. Click the format menu and then click conditional formatting.
Hope this helps!
Tom
Live once die twice; live twice die once.
RE: Format continuous forms
(OP)
The only problem is I only have 4 conditions. Can I code it for more.
RE: Format continuous forms
How are ya dendic . . .
Have a look at the FormatConditions Collection and the FormatCondition Object . . .
RE: Format continuous forms
(OP)
Thank you. I looked it up but can't figure how to implement it. I'm using a tab form named "newdispatch" and each tab has a sub_form. Each sub_form is a continuous form. Can you help me with the code that would check a field named "boxsize" for a condition and then make the background color for all fields within that record a specific color. The form refreshs every minute, as well.
RE: Format continuous forms
. . . and what is the conditions for boxsize?
RE: Format continuous forms
(OP)
boxsize = 20 or 40 but I'm going to use other conditions as well. If you can just give me a kick start I'll understand after that. I need to know where to put the event procedure. Ex: Opening main form or opening the sub_form I'm just confussed.
RE: Format continuous forms
To implement any of your changes, you should just be able to input a Case statement or a set of imbedded If statements. And, you might want to consider putting all of that into a Public Function or Sub-Procedure in a separate module, which could be called from any even in your form. Then, you could call it from your refreshing code (timer event I guess you're using), as well as any other event related to updating those fields.
You could just do something like:
CODE
Public Sub UpdateFormat()
Dim frm as Form
Dim ctl as Control
Set frm = Forms!MyFormName
For Each ctl in frm.Controls
If TypeOf ctl Is TextBox Then
Select Case condition
Case Condition 1
'Format changes here according to your needs
Case Condition 2
'Format changes here according to your needs
Case Condition 3
'Format changes here according to your needs
Case Condition 4
'Format changes here according to your needs
Case Else
MsgBox "Oops! Doesn't match any of my prescribed conditions!"
End Select
End If
Next ctl
Set ctl = Nothing
Set frm = Nothing
End Sub
Then you could call it from your various events:
CODE
Private Sub Form_Timer()
UpdateFormat
'Other Code
End Sub
Private Sub Text1_AfterUpdate()
UpdateFormat
'Other Code
End Sub
Private Sub Text2_AfterUpdate()
UpdateFormat
'Other Code
End Sub
'and so on
RE: Format continuous forms
dendic . . .
Sorry to get back so late!
First have a look here Adding Additional Conditional Formatting in Access with VBA
. . . and sample code to highlite entire lines (note: you must add a question mark ? to the Tag Property of each textbox that makes up the line!):
CODE
Dim ctl As Control, n As Integer
For Each ctl In Me.Controls
If ctl.Tag = " ? " Then
ctl.FormatConditions.Delete
For n = 1 To 3
With ctl.FormatConditions _
.Add(acExpression, , Choose(n, "[BoxSize]=20", "[BoxSize]=40", "[BoxSize]=60"))
.BackColor = Choose(n, 16773360, 16383986, 15790335)
.ForeColor = Choose(n, 8388608, 16384, 128)
End With
Next
End If
Next
RE: Format continuous forms
(OP)
I tried both codes and they work but I'm using a continuous form so I need to check each record. How can that be done? Thanks everyone
RE: Format continuous forms
dendic . . .
Conditional Formatting itself checks each record! Now I'm confused when you say it worked!
The code I gave comes from one of my db's so I know it works! Also, the code sets up the format conditions for each textbox with a question mark in its Tag property.
The format conditions are . . .
BoxSize = 20: DarkBlue foreground on LightBlue background.
BoxSize = 40: DarkGreen foreground on LightGreen background.
BoxSize = 60: DarkRed foreground on LightRed background.
If not change the BoxSize = to values you know you have.
RE: Format continuous forms
(OP)
Hi Aceman1,
The following code works fine. But how can I add another condition.
boxsize = 20 and not boxtype = "ref" set backcolor to blue
boxsize = (Allsizes) and boxtype = "ref" set backcolor to red.
Dim ctl As Control, n As Integer
For Each ctl In Me.Controls
If ctl.Tag = "?" Then
ctl.FormatConditions.Delete
For n = 1 To 1
With ctl.FormatConditions _
.Add(acExpression, , Choose(n, "[BoxSize]=20"))
.BackColor = Choose(n, 16737843)
End With
Next
End If
Next
Thanks you are very helpful
RE: Format continuous forms
dendic . . .
They key elements are:
Look at my example for adding 3 and your example for adding 1 . . . it should become apparent! If you havn't already, take a look at Choose in VBA help so you fully understand.
Any problems . . . let me know! . . .
RE: Format continuous forms
(OP)
This is where my problem comes in because I have 4 conditions.
RE: Format continuous forms
dendic . . .
The Web reference i gave earlier is as close as I can come . . .
RE: Format continuous forms
(OP)
What reference is that please?
RE: Format continuous forms