Loop through Form Objects (1 Viewer)

Yianni

Registered User.
Local time
Today, 06:36
Joined
May 15, 2017
Messages
40
Hi,

I am looking for suggestions and direction, so I can develop a function to allow
me to loop through my forms and change names on labels etc. Any suggestions
are welcome.

Thanks

yianni
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:36
Joined
May 7, 2009
Messages
19,169
Code:
Private Sub t()
Dim f As AccessObject
Dim frm As Form
Dim ctl As control
For Each f In CurrentProject.AllForms
        DoCmd.OpenForm f.Name, acDesign, , , , acHidden
        Set frm = Application.Forms(f.Name)
        For Each ctl In frm.Controls
            If TypeOf ctl Is Label Then
                If ctl.Name = "theNameOfLabel" Then
                    frm("theNameOfLabelHere").Caption = "xxxx"
                End If
            End If
        Next
        Set frm = Nothing
        DoCmd.Close acForm, f.Name, acSaveYes
    End If
Next
End Sub
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:36
Joined
Aug 30, 2003
Messages
36,118
FYI I moved your thread out of the introductions forum.
 

Yianni

Registered User.
Local time
Today, 06:36
Joined
May 15, 2017
Messages
40
I want to thank you for the code. I so study it and adapt as I can. Can you help me understand these lines
If ctl.Name = "theNameOfLabel" Then
frm("theNameOfLabelHere").Caption = "xxxx"
End If
I don't understand the first 2 of the 3 lines above.

For example I have a label name label13 with a Caption of 'First Name'. I want to change the label Name to lblFirstName (I know that I have to remove the space)
What I want to do is concatenate lbl and the Caption for each label in the form.

Thanks

John
 

Mark_

Longboard on the internet
Local time
Today, 03:36
Joined
Sep 12, 2017
Messages
2,111
Yianni,

Important question, since you are looking to "change names on labels", is this something you will be doing based on values in a table? If yes, will this be based on the text in the existing label's caption OR will this be based on which control the label is associated with?
 

Yianni

Registered User.
Local time
Today, 06:36
Joined
May 15, 2017
Messages
40
Hi Mark,

Thanks for the quick reply. I have been working today on modifying code to change labels on a new form. I am not a masterful programmer, but I am enclosing what I have done for your review. It may be crude, but now does what I am looking to do. I am sure it can be more elegant, but that may come.

Function CheckFormControls()
Dim f As AccessObject
Dim frm As Form
Dim ctl As Control
Dim captionstr As String
Dim spacepos As Integer 'position of space
Dim l As String ' left
Dim r As String ' right
Dim NewLabel As String
Dim namestr As String
For Each f In CurrentProject.AllForms
DoCmd.OpenForm f.Name, acDesign, , , , acHidden
Set frm = Application.Forms(f.Name)
For Each ctl In frm.Controls

If TypeOf ctl Is Label Then
If ctl.Name = "theNameOfLabel" Then
frm("theNameOfLabelHere").Caption = "xxxx"
End If
'---------------------------
Debug.Print "Form:" & " " & f.Name
captionstr = ctl.Caption
namestr = ctl.Name
Debug.Print ctl.Caption
'Debug.Print ctl.Name
'Debug.Print Len(Trim(ctl.Caption))
spacepos = (InStr([captionstr], " "))
'Debug.Print spacepos
If spacepos > 0 Then
l = Left([captionstr], spacepos - 1)
r = Right([captionstr], Len(ctl.Caption) - spacepos)
NewLabel = "lbl" & Trim(l) & Trim(r)
Debug.Print NewLabel
ctl.Name = NewLabel
Debug.Print "-------------" 'this line is between labels
End If ' if spacepos > 0
If spacepos = 0 Then 'we know it is single word
l = Trim([captionstr])
NewLabel = "lbl" & Trim(l)
Debug.Print NewLabel
ctl.Name = NewLabel
End If

End If ' if TypeOf ctl is Label
' write it here
Next 'next ctl

Set frm = Nothing
DoCmd.Close acForm, f.Name, acSaveYes
Debug.Print "________________________" ' This line is Between Forms
Debug.Print "------------------------"
Next
'Next f
End Function


Thanks, please feel free to make it better. Thanks John
 

Yianni

Registered User.
Local time
Today, 06:36
Joined
May 15, 2017
Messages
40
Hi Again,

I have modified your code. I am not a skilled programmer, so it may be crude, but it does what I was looking to do. I am enclosing it for your review. I know it needs to be more elegant but, hopefully that will come. Please feel free to send ways to make it better.

Function CheckFormControls()
Dim f As AccessObject
Dim frm As Form
Dim ctl As Control
Dim captionstr As String
Dim spacepos As Integer 'position of space
Dim l As String ' left
Dim r As String ' right
Dim NewLabel As String
Dim namestr As String
For Each f In CurrentProject.AllForms
DoCmd.OpenForm f.Name, acDesign, , , , acHidden
Set frm = Application.Forms(f.Name)
For Each ctl In frm.Controls

If TypeOf ctl Is Label Then
If ctl.Name = "theNameOfLabel" Then
frm("theNameOfLabelHere").Caption = "xxxx"
End If
'---------------------------
Debug.Print "Form:" & " " & f.Name
captionstr = ctl.Caption
namestr = ctl.Name
Debug.Print ctl.Caption
'Debug.Print ctl.Name
'Debug.Print Len(Trim(ctl.Caption))
spacepos = (InStr([captionstr], " "))
'Debug.Print spacepos
If spacepos > 0 Then
l = Left([captionstr], spacepos - 1)
r = Right([captionstr], Len(ctl.Caption) - spacepos)
NewLabel = "lbl" & Trim(l) & Trim(r)
Debug.Print NewLabel
ctl.Name = NewLabel
Debug.Print "-------------" 'this line is between labels
End If ' if spacepos > 0
If spacepos = 0 Then 'we know it is single word
l = Trim([captionstr])
NewLabel = "lbl" & Trim(l)
Debug.Print NewLabel
ctl.Name = NewLabel
End If

End If ' if TypeOf ctl is Label
' write it here
Next 'next ctl

Set frm = Nothing
DoCmd.Close acForm, f.Name, acSaveYes
Debug.Print "________________________" ' This line is Between Forms
Debug.Print "------------------------"
Next
'Next f

End Function

thanks for all your help John
 

Users who are viewing this thread

Top Bottom