Change All Label Captions within a Form (1 Viewer)

silentwolf

Member
Local time
Today, 14:40
Joined
Jun 12, 2009
Messages
344
Hi,

I like to update label Captions within a form.

However I am only able to get the control Name and not the caption of that control.

The reason I like to update all Labels in a Form as I have always something like Cont_FirstName and so on..

I like to get rid of the "Cont_" so the caption is "Firstname"

Code:
Sub Test()
    Dim ctl As Access.Control
    Dim strOld As String
    
    strOld = "Cont_"
    Dim strNew As String
    
    strNew = ""
    
    For Each ctl In Forms!frm_Contacts  ' must be open in design view
        If ctl.ControlType = acLabel Then
            Debug.Print ctl.Name
            
            Dim strLabel As String
            strLabel = ctl.Name
            
            Debug.Print me.(ctl.Name).Caption)      'How To get this Property when not knowing the Control Name
            

        End If
    Next ctl
End Sub

Once I am able to receiving the Caption I am able to update that Label so I was hoping someone could help please.

Would be just nice as there are many Forms and Controls so it would be a nice thing to have.

Many thanks
 

bob fitz

AWF VIP
Local time
Today, 22:40
Joined
May 23, 2011
Messages
4,444
I haven't tried it but I would have expected that using:
Code:
Debug.Print ctl.Caption
in the loop would return each "Caption" property.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:40
Joined
Feb 19, 2013
Messages
14,434
Just for information, the associated label is identified in the parent control as .controls(0) - so something like (not tested)

ctl.controls(0).caption=replace(ctl.controlsource,","Cont_","")


where ctl is the textbox, whatever

you will need error handling in the event that control does not have an associated label

You can use the caption property in table design or query design to display what it is you want as a caption but will only work for newly created labels

I'm not aware of a method for identifying a parent control from the label - lblControlName.parent will return the name of the form
 
Last edited:

silentwolf

Member
Local time
Today, 14:40
Joined
Jun 12, 2009
Messages
344
Hi,
thanks for both of you!
I'll be able to work it out from here!

Thanks
 

bob fitz

AWF VIP
Local time
Today, 22:40
Joined
May 23, 2011
Messages
4,444
Hi,
thanks for both of you!
I'll be able to work it out from here!

Thanks
I'm sure that Chris will be as pleased as I am in being of some help.
Perhaps, when you've found your solution you'll be good enough to post it here, for the benifit of all viewers.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:40
Joined
Feb 28, 2001
Messages
22,855
I see I'm late here, but CJ is absolutely right. You don't identify the control from the label, you identify the label from the control. The label is the control that has its associated control as its parent.
 

silentwolf

Member
Local time
Today, 14:40
Joined
Jun 12, 2009
Messages
344
Hi bob,

Sorry did take a little..

So this is what I got at present and it seams to work.
Properbly a little long for what needs to be done but at least saves me some time to edit those labels..

I am sure someone here has a much better solution to that.

@CJ_London..

I do get or did get a Runtime Error 2455...

Unfortunatelly I was not sure how to solve it.
I just have to form open and then run it so it is a little akward but works.

Happy to see a better Solution to mine.. however it would be cool to just leave to form closed.

But with referencing to the closed form I am not to exactly sure how to go about it.
Code:
Sub ChangeLabelName()
    Dim ctl As Access.Control
    Dim strOld As String
    
    strOld = "Cont_"
    Dim strNew As String
    
    strNew = ""
    
    For Each ctl In Forms!frm_Contacts  ' must be open in design view
        If ctl.ControlType = acLabel Then
            Debug.Print ctl.Name
            
            Dim strLabel As String
            strLabel = ctl.Name
            
            Dim intPos As Integer
            intPos = InStr(ctl.Caption, "_")
          
            strOld = Left(ctl.Caption, intPos)
            
            ctl.Caption = Replace(ctl.Caption, strOld, strNew)

        End If
    Next ctl
End Sub
 

silentwolf

Member
Local time
Today, 14:40
Joined
Jun 12, 2009
Messages
344
Hi Doc_Man,

thanks for your input too.

I am a little lost with what you mean exactly.

Also with CJ_London I do have a little difficulty to understand how to set it up correctly.
As mentioned it is a bit akward how I do it or done it.

Cheers
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:40
Joined
Feb 19, 2013
Messages
14,434
But with referencing to the closed form I am not to exactly sure how to go about it.
you can't edit a closed form, it needs to be open. You can iterate through the allforms collection, open in design view, make your changes then close and move onto the next one. See this link https://docs.microsoft.com/en-us/office/vba/api/access.allforms

with regards applying my suggestion, your code would be along the lines of


Code:
For Each ctl In Forms!frm_Contacts  ' must be open in design view
        If ctl.ControlType = acTextBox Then
            Debug.Print ctl.Name
            Debug.Print ctl.controls(0).Name, ctl.controls(0).caption 'may need error code
            ctl.controls(0).caption=replace(ctl.controlsource,","Cont_","")
            Debug.Print ctl.controls(0).caption
 

silentwolf

Member
Local time
Today, 14:40
Joined
Jun 12, 2009
Messages
344
Thanks CJ,

And many thanks for taking the time to show your code and presenting a link!
I will look into it!


Cheers!
 

LarryE

Active member
Local time
Today, 14:40
Joined
Aug 18, 2021
Messages
180
You can try the following to open each form in design mode. Then go through each control and if the control caption = "Cont_" then change it to "":

Code:
Dim ActiveFrm As Object
Dim ActiveCntrl As Control
For Each ActiveFrm In CurrentProject.AllForms
    DoCmd.OpenForm ActiveFrm.Name, acDesign, , , acFormPropertySettings
    Set ActiveFrm = Screen.ActiveForm
    With ActiveFrm
        For Each ActiveCntrl In ActiveFrm
                If ActiveCntrl.ControlType = acLabel Then
                    'Debug.Print ActiveCntrl.Properties("Caption")
                    If ActiveCntrl.Properties("Caption") = "Cont_" Then
                        ActiveCntrl.Properties("Caption") = ""
                    End If
                End If
        Next 'Control
    DoCmd.Close 'Open Form
    End With
Next 'Form
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 07:40
Joined
Jan 20, 2009
Messages
12,504
You could avoid the problem entirely by not using silly names for the controls.
 

moke123

AWF VIP
Local time
Today, 17:40
Joined
Jan 11, 2013
Messages
2,882
If you use Camel Case in your naming convention this procedure is helpful. It will correct LastName to Last Name.

Code:
'---------------------------------------------------------------------------------------
' File   : basLabelTuner
' Author : davegri
' Date   : 7/3/2021
' Purpose: Change default form label names created by Access
'   Routine will modify label captions following "Camel" conventions.
'   Example: if label caption is LastName it will be changed to Last Name
'   ZipPostalCode will be changed to Zip Postal Code
'   Essentially every Capitol letter will be replaced by Space & Capital Letter (except the first).
'   Double spaces (and greater) in label captions will be reduced to one space.
'   Captions that contain "ID", "PK" or "FK" will not have a space added to center or prefix.
'---------------------------------------------------------------------------------------


'---------------------------------------------------------------------------------------
' Method : fcnFormatAllLabels
' Author : davegri
' Date   : 6/29/2021
' Purpose: For existing form
'---------------------------------------------------------------------------------------
Public Function fcnFormatAllLabels(frmName As String)
    Dim ctl As Control, frm As Form
    Dim C As String, i As Integer, QED As String
    '    DoCmd.Close acForm, frmName
    '    DoCmd.OpenForm frmName, acDesign
    Set frm = Forms(frmName)
    For Each ctl In frm.Detail.Controls
        If ctl.ControlType = acLabel Then
            C = ctl.Caption
            For i = 65 To 90    'Cap A to cap Z
                C = fcnReplaceAll(C, Chr$(i), " " & Chr$(i))
                C = fcnReplaceAll(C, " I D", "ID")
                C = fcnReplaceAll(C, " P K", "PK")
                C = fcnReplaceAll(C, " F K", "FK")
                C = fcnReplaceAll(C, Space(2), Space(1))
            Next i
            ctl.Caption = Trim(C)
        End If
    Next ctl
    'DoCmd.Close acForm, frm.Name, acSaveYes
End Function


'---------------------------------------------------------------------------------------
' Method : fcnFormatSingleLabel
' Author : davegri
' Date   : 6/29/2021
' Purpose:  Called by code while creating new form programatically.
'   arg is the field name (which normally becomes the textbox's label caption).
'---------------------------------------------------------------------------------------
Public Function fcnFormatSingleLabel(arg As String)

    Dim C As String, i As Integer
    C = arg
    For i = 65 To 90
        C = fcnReplaceAll(C, Chr$(i), " " & Chr$(i))
        C = fcnReplaceAll(C, " I D", "ID")
        C = fcnReplaceAll(C, " P K", "PK")
        C = fcnReplaceAll(C, " F K", "FK")
        C = fcnReplaceAll(C, Space(2), Space(1))
    Next i
    C = Trim(C)
    fcnFormatSingleLabel = C
End Function


Function fcnReplaceAll(ByVal Target As String, ByVal arg As String, ByVal NewArg As String) As String
    If InStr(1, NewArg, arg, vbBinaryCompare) > 0 Then
        Target = fcnReplaceAllOnce(Target, arg, NewArg)
    Else
        Do While InStr(1, Target, arg, vbBinaryCompare) > 0
            Target = fcnReplaceAllOnce(Target, arg, NewArg)
        Loop
    End If
    fcnReplaceAll = Target
End Function


Function fcnReplaceAllOnce(ByVal Target As String, ByVal arg As String, ByVal NewArg As String) As String
    Dim i As Long
    If fcnIsNothing(arg) Then
        fcnReplaceAllOnce = Target
    Else
        If StrComp(arg, NewArg, vbBinaryCompare) = 0 Then
            fcnReplaceAllOnce = Target
        Else
            i = InStr(1, Target, arg, vbBinaryCompare)
            Do While i > 0
                Target = Left(Target, i - 1) & NewArg & Mid(Target, i + Len(arg))
                i = i + Len(NewArg)
                i = InStr(i, Target, arg, vbBinaryCompare)
            Loop
            fcnReplaceAllOnce = Target
        End If
    End If
End Function


Function fcnIsNothing(ByVal Target As String) As Boolean
    If Target & "" = "" Then
        fcnIsNothing = True
    Else
        fcnIsNothing = False
    End If
End Function
 

silentwolf

Member
Local time
Today, 14:40
Joined
Jun 12, 2009
Messages
344
Hi all,

many thanks for all your input!
I am currently just working on something but find all the Infos already more then helpful!

Appologies regarding Namingconventions!
I did not realize that in VBA is camelCase Conventions .. thought Hungarien well .. I guess not lol

But that should be a easy enough taks do change that.

@moke123

Why are you beginning the Counter by 65 to 90? Did not quite understand the comment.

Otherwise this looks pretty interesting and will deffinatelly like to use some of your code if I am allowed to?

Sorry for not beeing able to comment and try all your suggestions right away as there is also other work which needs to be done.

However I will get back to you all once I find more time to continiou with this.
Skills that make me a nightmare for people like you

Not exactly sure why you did through that one into your comments?

Cheers and again thanks !
 
Last edited:

LarryE

Active member
Local time
Today, 14:40
Joined
Aug 18, 2021
Messages
180
You can try the following to open each form in design mode. Then go through each control and if the control caption = "Cont_" then change it to "":

Code:
Dim ActiveFrm As Object
Dim ActiveCntrl As Control
For Each ActiveFrm In CurrentProject.AllForms
    DoCmd.OpenForm ActiveFrm.Name, acDesign, , , acFormPropertySettings
    Set ActiveFrm = Screen.ActiveForm
    With ActiveFrm
        For Each ActiveCntrl In ActiveFrm
                If ActiveCntrl.ControlType = acLabel Then
                    'Debug.Print ActiveCntrl.Properties("Caption")
                    If ActiveCntrl.Properties("Caption") = "Cont_" Then
                        ActiveCntrl.Properties("Caption") = ""
                    End If
                End If
        Next 'Control
    DoCmd.Close 'Open Form
    End With
Next 'Form
Looking at it again, I think DoCmd.Close should be:
DoCmd.Close acForm, ActiveFrm.Name, acSaveYes
 

moke123

AWF VIP
Local time
Today, 17:40
Joined
Jan 11, 2013
Messages
2,882
Not my code, @davegri is the author. I'm sure he's fine with you using it.

CamelCase is only one of many naming conventions.

65 through 90 are the ascii values of the letters A through Z.
 

moke123

AWF VIP
Local time
Today, 17:40
Joined
Jan 11, 2013
Messages
2,882
Come to think of it, you may be able to modify it for your purposes. (untested)

Code:
        If ctl.ControlType = acLabel Then
            C = replace(ctl.Caption, "Cont_", "")   ' Add replace function to this line.
            For i = 65 To 90    'Cap A to cap Z
 

silentwolf

Member
Local time
Today, 14:40
Joined
Jun 12, 2009
Messages
344
Hi,

Ok thanks again for explaination!
As I said I will try it all out but bare with me for a little .. time is a little short at present.

Thanks
 

Users who are viewing this thread

Top Bottom