Change all label tag property using loop? (1 Viewer)

C

calvinle

Registered User.
Local time
Today, 14:21
Joined
Sep 26, 2014
Messages
332
Hi,

I added in my subform about 20 fields, and I want to change the tag property of that label to match the name of it's field.

Is there a way to do it via a code instead of manually doing it one by one?

Here is what I use but doesnt seems to work:
Code: 
Dim c As Access.Control
    For Each c In Me.Form
        c.Tag = c.Caption
    Next c

Any help is appreciated. Thanks
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:21
Joined
May 7, 2009
Messages
19,242
do you want it only on the subform?
 
C

calvinle

Registered User.
Local time
Today, 14:21
Joined
Sep 26, 2014
Messages
332
arnelgp said:
do you want it only on the subform?
Click to expand...

Yes, I add a command button on that subform to only create the tag within that subform.

Thanks
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 22:21
Joined
Sep 21, 2011
Messages
14,299
I think that should be

Me.Controls
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:21
Joined
May 7, 2009
Messages
19,242
put this in Standard module
close the form where the subform is.
and call it with the name of the form
where yoursubform is.
Code: 
Public Function fncChangeTag(sForm As String)
    Dim aO As AccessObject
    Dim fm As Access.Form
    Dim sf As Access.Form
    Dim ct As Access.Control
    Dim c As Access.Control
    For Each aO In CurrentProject.AllForms
        If aO.Name = sForm Then
            DoCmd.OpenForm aO.Name, acDesign, , , , acHidden
            Set fm = Forms(aO.Name)
            For Each ct In fm.Controls
                If TypeOf ct Is SubForm Then
                    Set sf = Forms(aO.Name)(ct.Name).Form
                    For Each c In sf
                        If TypeOf c Is Label Then
                            c.Tag = c.Caption
                        End If
                    Next c
                    Set sf = Nothing
                    Exit For
                End If
            Next ct
            Set fm = Nothing
            DoCmd.Close acForm, aO.Name, acSaveYes
            Exit For
        End If
    Next
End Function
on Immediate window of VBE:

Call fncChangeTag("the name of Main form")
 
The_Doc_Man

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:21
Joined
Feb 28, 2001
Messages
27,183
I suspect that it is not working because not all controls have captions. Test for the control type to be a label before attempt to do this. Something like:

Code: 
If c.Type = acLabel Then
    c.Tag = c.Caption
....
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:21
Joined
May 21, 2018
Messages
8,528
I am assuming (right or wrong) that the caption is different from the field name. In that case you can try to get the label from the control's controls collection.
Code: 
  'if a control has an attached label then it has a controls collection, and the first is the label
  Dim ctl As Access.Control
  Dim lbl As Access.Label
  On Error Resume Next
  For Each ctl In Me.Controls
   If ctl.ControlType = acTextBox Then ' you can add other control types here
    If ctl.Controls(0).ControlType = acLabel Then
      Set lbl = ctl.Controls(0)
      lbl.Tag = ctl.Name
      Debug.Print lbl.Name & " " & lbl.Tag
    End If
   End If
 Next ctl
 
C

calvinle

Registered User.
Local time
Today, 14:21
Joined
Sep 26, 2014
Messages
332
arnelgp said:
put this in Standard module
close the form where the subform is.
and call it with the name of the form
where yoursubform is.
Code: 
Public Function fncChangeTag(sForm As String)
    Dim aO As AccessObject
    Dim fm As Access.Form
    Dim sf As Access.Form
    Dim ct As Access.Control
    Dim c As Access.Control
    For Each aO In CurrentProject.AllForms
        If aO.Name = sForm Then
            DoCmd.OpenForm aO.Name, acDesign, , , , acHidden
            Set fm = Forms(aO.Name)
            For Each ct In fm.Controls
                If TypeOf ct Is SubForm Then
                    Set sf = Forms(aO.Name)(ct.Name).Form
                    For Each c In sf
                        If TypeOf c Is Label Then
                            c.Tag = c.Caption
                        End If
                    Next c
                    Set sf = Nothing
                    Exit For
                End If
            Next ct
            Set fm = Nothing
            DoCmd.Close acForm, aO.Name, acSaveYes
            Exit For
        End If
    Next
End Function
on Immediate window of VBE:

Call fncChangeTag("the name of Main form")
Click to expand...

I execute within Immediate Windows, went back to the design form of the subform, the label associated to the field still has empty tag?

Thanks
 
C

calvinle

Registered User.
Local time
Today, 14:21
Joined
Sep 26, 2014
Messages
332
MajP said:
I am assuming (right or wrong) that the caption is different from the field name. In that case you can try to get the label from the control's controls collection.
Code: 
  'if a control has an attached label then it has a controls collection, and the first is the label
  Dim ctl As Access.Control
  Dim lbl As Access.Label
  On Error Resume Next
  For Each ctl In Me.Controls
   If ctl.ControlType = acTextBox Then ' you can add other control types here
    If ctl.Controls(0).ControlType = acLabel Then
      Set lbl = ctl.Controls(0)
      lbl.Tag = ctl.Name
      Debug.Print lbl.Name & " " & lbl.Tag
    End If
   End If
 Next ctl
Click to expand...

The debug.print does give the right Label name and it's caption, but it does not change the Tag property of that Label. After executing, I went into design mode of that subform, and the Tag for the label is still blank.

Thanks
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:21
Joined
May 7, 2009
Messages
19,242
did you execute it against the "Main form" where your subform is?
 
C

calvinle

Registered User.
Local time
Today, 14:21
Joined
Sep 26, 2014
Messages
332
arnelgp said:
did you execute it against the "Main form" where your subform is?
Click to expand...

All the form are closed and I just execute within the Immediate Windows.

Call fncChangeTag("pdo_prod_reg_info_rrsp_subfrm")

I also added the msgbox at the end of the function to ensure it's completed properly, the prompt does appear.

I went back to pdo_prod_reg_info_rrsp_subfrm, right click Design View, but the label tag are still blank.

Thanks
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:21
Joined
May 7, 2009
Messages
19,242
is "pdo_prod_reg_info_rrsp_subfrm" the Main form?
i am asking for the main form.
if you cannot do it in the Main form
here do it in any form in the navigation pane.
try it with "pdo_prod_reg_info_rrsp_subfrm".
Code: 
Public Function fncChangeTag(sForm As String)
    Dim aO As AccessObject
    Dim fm As Access.Form
    Dim sf As Access.Form
    Dim ct As Access.Control
    For Each aO In CurrentProject.AllForms
        If aO.Name = sForm Then
            DoCmd.OpenForm aO.Name, acDesign, , , , acHidden
            Set fm = Forms(aO.Name)
            For Each ct In fm.Controls
                If TypeOf ct Is Label Then
                    ct.Tag = ct.Caption
                End If
            Next ct
            Set fm = Nothing
            DoCmd.Close acForm, aO.Name, acSaveYes
            Exit For
        End If
    Next
End Function
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 22:21
Joined
Sep 21, 2011
Messages
14,299
I accept that it is a good exercise to learn, but you could have changed those labels manually ten times over by now. :D
 
C

calvinle

Registered User.
Local time
Today, 14:21
Joined
Sep 26, 2014
Messages
332
Gasman said:
I accept that it is a good exercise to learn, but you could have changed those labels manually ten times over by now. :D
Click to expand...

Not really, I have about 15 subform with at least 15 fields on each, thats about 225 to manually do them. I won't do it manually ;)
 
C

calvinle

Registered User.
Local time
Today, 14:21
Joined
Sep 26, 2014
Messages
332
arnelgp said:
is "pdo_prod_reg_info_rrsp_subfrm" the Main form?
i am asking for the main form.
if you cannot do it in the Main form
here do it in any form in the navigation pane.
try it with "pdo_prod_reg_info_rrsp_subfrm".
Code: 
Public Function fncChangeTag(sForm As String)
    Dim aO As AccessObject
    Dim fm As Access.Form
    Dim sf As Access.Form
    Dim ct As Access.Control
    For Each aO In CurrentProject.AllForms
        If aO.Name = sForm Then
            DoCmd.OpenForm aO.Name, acDesign, , , , acHidden
            Set fm = Forms(aO.Name)
            For Each ct In fm.Controls
                If TypeOf ct Is Label Then
                    ct.Tag = ct.Caption
                End If
            Next ct
            Set fm = Nothing
            DoCmd.Close acForm, aO.Name, acSaveYes
            Exit For
        End If
    Next
End Function
Click to expand...

I was able to run the function, but now, after close it and reopen, I can't run it anymore. It says variable not define.
Is there a way to decompile Access to reset all memory in the database? Looks like something its messing with my database now.
 
C

calvinle

Registered User.
Local time
Today, 14:21
Joined
Sep 26, 2014
Messages
332
So now, I changed the code to the following to target the form directly:
Code: 
Public Function fncChangeTag()
    Dim aO As AccessObject
    Dim fm As Access.Form
    Dim sf As Access.Form
    Dim ct As Access.Control
    'For Each aO In CurrentProject.AllForms
    '    If aO.Name = sForm Then
            aO.Name = "pdo_prod_reg_info_rrsp_subfrm"
            DoCmd.OpenForm aO.Name, acDesign, , , , acHidden
            Set fm = Forms(aO.Name)
            For Each ct In fm.Controls
                If TypeOf ct Is Label Then
                    ct.Tag = ct.Caption
                End If
            Next ct
            Set fm = Nothing
            DoCmd.Close acForm, aO.Name, acSaveYes
            Exit For
    '    End If
    'Next
End Function

In Immediate Windows, when I run the function, this is the error I received:
"Can't assign to a read-only propery" and the portion highlighted is:
aO.Name = "pdo_prod_reg_info_rrsp_subfrm"

What's going on??
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:21
Joined
May 7, 2009
Messages
19,242
"Can't assign to a read-only propery"
Click to expand...
becoz you don't know which Object it has grabbed.
AccessObject can be any form, report, tables, etc.

to do it directly:
Code: 
Public Function fncChangeTag()
    Const FORM_NAME As String = "pdo_prod_reg_info_rrsp_subfrm"
    Dim fm As Access.Form
    Dim ct As Access.Control
    DoCmd.OpenForm FORM_NAME, acDesign, , , , acHidden
    Set fm = Forms(FORM_NAME)
    For Each ct In fm.Controls
        If TypeOf ct Is Label Then
            ct.Tag = ct.Caption
        End If
    Next ct
    Set fm = Nothing
    DoCmd.Close acForm, FORM_NAME, acSaveYes
End Function
 
C

calvinle

Registered User.
Local time
Today, 14:21
Joined
Sep 26, 2014
Messages
332
Thank you very much!!! This saves me tons of time!!
 
You must log in or register to reply here.

Similar threads

oxicottin
Solved Multiselect listbox loop showing there isn't data
Replies
4
Views
253
pbaldy
pbaldy
P
Remove font face RTF tags
Replies
10
Views
331
pdanes
P
J
Update field on calling form
Replies
6
Views
471
MajP
MajP
M
Solved Problem calling Form (Class Object) Code from a Module.
Replies
8
Views
547
Pat Hartman
P
I
Combo box shows HTML tags
Replies
6
Views
281
ilanray
I

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom