Loop through Form Objects

[Yianni]

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]

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]

FYI I moved your thread out of the introductions forum.

 

[Yianni]

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_]

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]

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]

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