Format continuous forms

[dendic]

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

 

[ThomasLafferty]

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.

 

[dendic]

The only problem is I only have 4 conditions. Can I code it for more.

 

[TheAceMan1]

How are ya dendic . . .

Have a look at the [blue]FormatConditions Collection[/blue] and the [blue]FormatCondition Object[/blue] . . .

See Ya! . . . . . .

 

[dendic]

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.

 

[TheAceMan1]

[blue] . . . check a field named "boxsize" for a condition and then make the background color for all fields within that record a specific color.[/blue]

[blue] . . . and what is the conditions for boxsize?[/blue]

See Ya! . . . . . .

 

[dendic]

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.

 

[kjv1611]

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:

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:

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

 

[TheAceMan1]

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 [purple]?[/purple] to the [blue]Tag Property[/blue] of each textbox that makes up the line!):

[blue]   Dim ctl As Control, n As Integer
   
   For Each ctl In Me.Controls
      If ctl.Tag = "[purple][b]?[/b][/purple]" 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[/blue]

See Ya! . . . . . .

 

[dendic]

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

 

[TheAceMan1]

dendic . . .

[blue]Conditional Formatting[/blue] 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 [blue]question mark[/blue] in its [blue]Tag[/blue] property.

The format conditions are . . .
[blue]BoxSize = 20: DarkBlue foreground on LightBlue background.
BoxSize = 40: DarkGreen foreground on LightGreen background.
BoxSize = 60: DarkRed foreground on LightRed background.[/blue]

[ol][li]Are any of the above conditions being met?
If not change the [blue]BoxSize =[/blue] to values you know you have.[/li]
[li]Did you add the question mark [purple]?[/purple] to the [blue]Tag[/blue] property of the textboxes of interest?[/li]
[li]The code should go in the [blue]OnLoad[/blue] event of the form![/li][/ol]

See Ya! . . . . . .

 

[dendic]

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

 

[TheAceMan1]

dendic . . .

They key elements are:
[ol][li]The limit of n in For n = 1 to [purple]?[/purple] ([blue]max = 3[/blue]).[/li]
[li]The [blue]Choose[/blue] functions. For increasing [blue]n[/blue] above, you add additional selections.[/li][/ol]
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.

[blue]Any problems . . . let me know! . . .[/blue]

See Ya! . . . . . .

 

[dendic]

This is where my problem comes in because I have 4 conditions.

 

[TheAceMan1]

dendic . . .

The Web reference i gave earlier is as close as I can come . . .

See Ya! . . . . . .

 

[dendic]

What reference is that please?

 

[TheAceMan1]

Adding Additional Conditional Formatting in Access with VBA

See Ya! . . . . . .