Help With Repetitive Code 2

[SemperSalvus]

If I can, I need to streamline this code. I am using a tab control to manage several hundred fields for an inspection application. Many of the fields have a "green, yellow, red" state for the inspector to choose, depending on how good or bad the particular inspection item is. The table values are "G" for Green, "Y" for Yellow, and "R" for Red. I have code changing both the back and forecolor of the label on the afterupdate event of the list box control. The event will call 2 different functions, one for the backcolor and one for the forecolor.

My problem is that the code in the afterupdate event for each field, is still a little too long since it is taking me forever to develop the form. I have basically the same code being used for each field's afterupdate event. The name of the control is used 3 times within the code and is the only difference each time. Here is an example of the afterupdate event code and the functions:

Private Sub ctlDOCK_SURFACE_CRACKED_AfterUpdate()

Dim strARG As String

strARG = Me.ctlDOCK_SURFACE_CRACKED.Value

Call ChooseBackColor(strARG)
Me.lblDOCK_SURFACE_CRACKED.BackColor = QBColor(ChooseBackColor(strARG))

Call ChooseForeColor(strARG)
Me.lblDOCK_SURFACE_CRACKED.ForeColor = QBColor(ChooseForeColor(strARG))

End Sub

Private Function ChooseBackColor(strARG)

If strARG = "G" Then
ChooseBackColor = 2
ElseIf strARG = "Y" Then
ChooseBackColor = 14
ElseIf strARG = "R" Then
ChooseBackColor = 12
End If

End Function

Private Function ChooseForeColor(strARG)

If strARG = "G" Then
ChooseForeColor = 15
ElseIf strARG = "Y" Then
ChooseForeColor = 0
ElseIf strARG = "R" Then
ChooseForeColor = 0
End If

End Function

Is there some way I can trim down the afterupdate event code since I will have several hundred of these little procedures? I thought of using one call to another proc that passes on the name of the control as the argument? That other proc could call the 2 functions to determine the color. However, I could not figure out how to pass on the control name without having to retype that name within the afterupdate event. I could find no method to grab the name for this purpose though. Thanks.

 

[traingamer]

just modify something like this

Private Function colorit(strArg)

Dim ctl As Control
For Each ctl In Me.ActiveControl.Controls
    With ctl
        Select Case .ControlType
         Case acLabel
            If strArg = "g" Then
                .BackColor = QBColor(5)
                .ForeColor = QBColor(11)
            End If
'           rest of code here
         Case 1
         End Select
    End With
Next ctl
End Function

You may want to add more checks (or not).
This basically takes the active control, loops through its controls and sets the properties on any label associated with it.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[TheAceMan1]

How are ya SemperSalvus . . .

Note: all your color constants are black!

In any case in the code module of the form copy/paste the following common routine:

[blue]Public Sub SetLblColors(Lbl As Label)
   Dim Ctl As Control, BG As Long, FG As Long
   
   Set Ctl = Me(Screen.ActiveControl.Name)
   
   If Ctl = "G" Then
      BG = 2
      FG = 15
   ElseIf Ctl = "Y" Then
      BG = 14
      FG = 0
   ElseIf Ctl = "R" Then
      BG = 12
      FG = 0
   Else
      BG = 16777215 [green]'Default BackGround[/green]
      FG = 0        [green]'Default ForeGround[/green]
   End If
   
   Lbl.BackColor = BG
   Lbl.ForeColor = FG
   
   Set Ctl = Nothing
   
End Sub[/blue]

In the AfterUpdate events of interest the code should be:

[blue]   Call SetLblColors(Me![purple][b][i]LabelName[/i][/b][/purple])

[purple]Example:[/purple]
   Call SetLblColors(Me!lblDOCK_SURFACE_CRACKED)[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[SemperSalvus]

traingamer: Thanks, but that seems like too many iterations. I don't need it to check on each control when one control is updated. I just want it to immediately change the color for the control that had a value changed. Most fields will default to green which assumes that most everything is good-to-go as you start the inspection and then the inspector only has to change the things that are unsat. I want the colors to change right after they click so the choice will be readily apparent in case they make a choice they did not intend.

TheAceMan1: Yep, that code worked. I had to add in QBColor though -

[COLOR=blue]Lbl.BackColor = QBColor(BG)[/color blue]

[COLOR=blue]Lbl.ForeColor = QBColor(FG)[/color blue]

My problem was I didn't realize I could simply just pass the control name (Lbl) as an argument to the proc determining the colors. The Me(Screen.ActiveControl.Name) is crucial too and I tried to use that property in some earlier code, but couldn't get it to work.

What do you mean by all my color constants are black? QBColor(15) is Bright White, QBColor(2) is Green, etc. QBColor(0) is the Black.

I also didn't need the defaults since the fields are in the desired default state each time the form is brought up.

This code rocks since each afterupdate event will only have a one-liner, the call to the public proc, which is exactly what I wanted to simplify to. Thanks.

 

[traingamer]

...that seems like too many iterations. I don't need it to check on each control when one control is updated.

You would only be checking the active control. You would call it the same as you were calling your two functions in your example. It would be simpler for you to code than AceMan's example, but his is much more efficient (but requires you to know the name of the label for each text box). My version could be called by simply plugging the same line of code

 Call colorit(Me.ActiveControl.Value)

into each AfterUpdate event.

In any case, glad you got it solved.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[puforee]

This may be a simplistic approach but.....when I need color control for a field based on the fields value I simply use the conditional formatting feature. I find it very easy to use and it can control background and foreground colors.

 

[traingamer]

Yes, but this is coloring the label for a text field, not the text field, itself.

 

[SemperSalvus]

traingamer: Ok, I see now. I will give that a try too.

puforee: traingamer is right. The conditional formatting won't work since I am changing the colors of the label. I tried to format the list box values, but because of the way access uses highlights, it didn't look good at all. For example, if I choose red, the words Yellow and Green have a red background, but the word Red is highlighted (since that is the chosen value for that field) and its background is purple. Note: My list boxes show all 3 words at once so the user can just click on what they want instead of expanding a dropdown, etc. I looked for a highlight color property but couldn't find one, so I went with changing the colors for each label.

 

[TheAceMan1]

[blue] . . . is much more efficient (but requires you to know the name of the label for each text box)[/blue]

The entire thread indicates were talking a few labels and not all! Otherwise I would've done the same . . .

See Ya! . . . . . .

 

[TheAceMan1]

[blue]What do you mean by all my color constants are black?[/blue]

I didn't realize you were using the [blue]QBColor[/blue] constants.

QBColor returns a subset of [blue]RGB color codes[/blue]. To get the code for any color of your choice perform the following:
[ol][li]In form design view put the cursor on the [blue]BackColor[/blue] or [blue]ForeColor[/blue] property of any control.[/li]
[li]Click the three elipses

just to the right. This brings up the [blue]Color Picker[/blue].[/li]
[li]When your done the rgb constant is returned to the property. This is the value you use![/li][/ol]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[SemperSalvus]

traingamer: Ok, I got it to work. Here is what I used.

Private Function colorit(strArg)

Dim ctl As Control
For Each ctl In Me.ActiveControl.Controls
    With ctl
        Select Case .ControlType
         Case acLabel
            If strArg = "G" Then
                .BackColor = QBColor(2)
                .ForeColor = QBColor(15)
            ElseIf strArg = "Y" Then
                .BackColor = QBColor(14)
                .ForeColor = QBColor(0)
            ElseIf strArg = "R" Then
                .BackColor = QBColor(12)
                .ForeColor = QBColor(0)
            End If
         End Select
    End With
Next ctl
End Function

I used the function call you had in one of your previous posts in the afterupdate event for the control.

I have 2 things I don't understand. I am not that used to using select statements, so what was the "Case 1" line for? I took it out and it still worked just fine. Also, more importantly, I don't understand what is going on here with the .ControlType and acLabel. How does this bit of code know which label is associated with the active control? For example, a listbox is named ctlDOCK_SURFACE_CRACKED and the label for it is named lblDOCK_SURFACE_CRACKED. Of course this code doesn't need to use those names, but I can't see how it ties the two together. It works perfectly though.

Using your code, I guess I don't have to use these elaborate names either. That would also save me some time in making these fields and their code.

Thanks.

 

[SemperSalvus]

TheAceMan1: Ok sweet! I never realized I could use the .backcolor and .forecolor properties so simply. Thanks.

 

[PHV]

How does this bit of code know which label is associated with the active control?
The magical stuff is here:
For Each ctl In [!]Me.ActiveControl.Controls[/!]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

Howdy PHV!

I thought about that but [blue]if the labels ae not attached?[/blue] . . .

See Ya! . . . . . .

 

[traingamer]

If the labels are not attached, it won't work.

 

[traingamer]

...so what was the "Case 1" line for?

Old programming (or is that old programmer's) habit. (The case statement is in there because I took the bulk of that code from the Access help files, by the way. An if statement would work as well.)
Whenever I use a Case statement, I always include a Case 1 clause. It says 'for every case not found above' do this. In this case, if the control is not a label do nothing (which works out the same whether or not you use the Case 1 in this instance).
It would have been clearer if I had included my typical comment

...
         Case 1
'           Do nothing
         End Select
    End With

That's a long-winded way of saying it isn't needed.

 

[TheAceMan1]

[blue]If the labels are not attached, it won't work.[/blue]

Just my point . . . [blue]the reason I pass the label name! . . .[/blue]

See Ya! . . . . . .

 

[SemperSalvus]

Ok, the code works because the labels are in fact attached to each control. I know when I created the first field on this form I pulled it from the field list, so it had a label attached to it then. Every field after that has been a copy of the first since I have some different font stuff going on. Still, where in properties does the label get "attached" to the control, or is that just something behind the scenes in Access. Also, you know that if you bring in something from the field list you can delete its attached label. So how would you "attach" a different label to it later if you so desired?

 

[TheAceMan1]

[blue] . . . where in properties does the label get "attached" to the control . . .[/blue]

[ol][li]In form design view callup the [blue]Properties Window[/blue] any way you like and select the [blue]Format Tab[/blue].[/li]
[li]Now . . . pay attention to the [blue]Title Bar[/blue] of the properties window and click the [blue]Text Box[/blue] toolbar button

. Note the Title Bar changes to [blue]Default Text Box![/blue]. These defaults determine what the textbox looks like (and attached label) when install on the form. See the properties:
[blue]Auto Label
Label X
Label Y[/blue][/li][/ol]

[blue]So how would you "attach" a different label to it later if you so desired?[/blue]

[ol][li]Cut the Label.[/li]
[li]Select the control of interest.[/li]
[li]Paste the Label[/li][/ol]

[blue]Cheers![/blue]

See Ya! . . . . . .

 

[SemperSalvus]

That is sweet. Thanks!