For Each Label in a form - any way to select by object type?

[IR_Moon]

Hi folks!

Query:
What I want to know is do objects in an access form have a "type" element that can be accessed through VBA? In essence what I'm trying to do is assign a colour to every label in a form as it opens. In the on open I currently have:

For Each label In Me.Form
    If Left(label.Name, 3) = "lbl" Then
        label.ForeColor = 16777215
    End If
Next

Which works well while all the labels are named lbl, but I'm dealing with an application that someone else has made and I'm tweaking.

Background:
(to save you all asking simple questions):

For one reason or another I've got 9 separate yet essentially identical databases - one for each client. I'm creating a new unified front end which links all the backend tables to the front end for a given client based on a client selection combo box on the opening form. Once this is done I want the database to assign a colour scheme as a visual cue for a user so they don't forget which database they are using. I'm sick of using pastels (which black text looks nice on) and want to use some dark back colours with light or pastel text for the labels. The back color I can do, but basically I wanted to know if I could create a For Each loop to pick out each label by its type (rather than part of its name). As stated, its original design was by someone else and theres literally dozens of forms to wade through and she's not used any sort of naming convention. Call me lazy, but its repeatable code that I'd like to apply to other projects too.

And... One single database for all clients you say? Just you try and convince my bosses. I'm working towards ditching Access in favour of SQL, but they're paranoid about a.) the amount of time (i.e. money) it will take and b.) the possible downtime when I mess it up for the first seven version (they are not very tech savvy)

 

[mhartman]

Hello:

Here it is:
Example
The following example examines the ControlType property for all controls on a form. For each label and text box control, the procedure toggles the SpecialEffect property for those controls. When the label controls' SpecialEffect property is set to Shadowed and the text box controls' SpecialEffect property is set to Normal and the AllowAdditions, AllowDeletions, and AllowEdits properties are all set to True, the intCanEdit variable is toggled to allow editing of the underlying data.

Sub ToggleControl(frm As Form)
Dim ctl As Control
Dim intI As Integer, intCanEdit As Integer
Const conTransparent = 0
Const conWhite = 16777215
For Each ctl in frm.Controls
With ctl
Select Case .ControlType
Case acLabel
If .SpecialEffect = acEffectShadow Then
.SpecialEffect = acEffectNormal
.BorderStyle = conTransparent
intCanEdit = True
Else
.SpecialEffect = acEffectShadow
intCanEdit = False
End If
Case acTextBox
If .SpecialEffect = acEffectNormal Then
.SpecialEffect = acEffectSunken
.BackColor = conWhite
Else
.SpecialEffect = acEffectNormal
.BackColor = frm.Detail.BackColor
End If
End Select
End With
Next ctl
If intCanEdit = IFalse Then
With frm
.AllowAdditions = False
.AllowDeletions = False
.AllowEdits = False
End With
Else
With frm
.AllowAdditions = True
.AllowDeletions = True
.AllowEdits = True
End With
End If
End Sub


Regards
Mark

 

[IR_Moon]

Hay that's awesome, thanks for your hasty reply. the .ControlType was what I was after...

My total code (just to simplify it for anyone else trying the same) is:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open
    'HideInvoices
    Dim ctl As Control
    Me.Detail.BackColor = Forms![frmOpen]![cboPickClient].Column(4) 'from open form client combo
    Me.OrderByOn = True
    
    'for each object starting with lbl (a label) make text color white
    For Each ctl In Me.Form
        With ctl
            If .ControlType = acLabel Then
                ctl.ForeColor = Forms![frmOpen]![cboPickClient].Column(5) 'from open form client combo
            End If
        End With
    Next
    
Exit_Form_Open:
    Exit Sub
    
Err_Form_Open:
    MsgBox Err.Number & ", " & Err.Description
    Resume Exit_Form_Open
    
End Sub

 

[mhartman]

Hello:

Your welcome. That came directly from the help file. What you condensed it to is what I use also.

Good luck with this.

Regards
Mark