I'm using Access 2010.

I want to use VBA to change the back color of labels in the footer of all
continuous forms in my application.
Using:
For Each ctrl In Frm.Section(acFooter).Controls
debug.print ctrl.Name, ctrl.ControlType
Next
The loop runs and picks up the names of text boxes and buttons, but doesn't
pick up any labels.

If I use:
For Each ctrl In Frm.Section(acHeader).Controls
it picks up the labels in the form header.

Can anyone give me an idea what I'm doing wrong?

--
Bob Darlington
Brisbane

On 28/07/2011 09:11:57, "Bob Darlington" wrote:
I think you need
ctl.controls.item(0).backcolor

Anyway this typed in the immediate window gibes the label's name
?forms!clubs.Clubname.controls.item(0).name
Phil

"Phil" <phil (AT) stantonfamily (DOT) co.uk> wrote

Thanks Phil.
I tried your suggestion using:
With Frm.Section(acFooter).Controls
vCount = .Count
For i = 0 To vCount - 1
Debug.Print .Item(i).ControlName 'Note - '.Name' gives same
result


vCount returns 44 controls which is correct. But only 17 print out
(including only 1 of I need to be able to access every control in the
footer. But how?

--
Bob Darlington
Brisbane

On 29/07/2011 06:15:33, "Bob Darlington" wrote:
Hi Bob

This seems to work. Think the label is always Controls.Item(0), not item(i)
as inyour post. Not sure whether you need to check the ControlType.
You may have a problem if the labels are not actually "bound" to the controls

Function Test()

Dim Frm As Form
Dim Ctl As Control

Set Frm = Screen.ActiveForm
On Error Resume Next
For Each Ctl In Frm.Section(acDetail).Controls
If Ctl.ControlType = acTextBox Then
Debug.Print Ctl.Name; " "; Ctl.Controls.Item(0).Name
End If
Next

End Function

Phil

On Jul 28, 3:12*am, "Bob Darlington" <b... (AT) notheredpcman (DOT) com.au>
wrote:
The code below works in 2007. It cycles thru each form section and
cycles thru all controls in the section. Seems to work for me. It
does not associate a label to the text box...if that is what you
needed. The c.ControlType spit back either 100 or 109 since the form
only had labels and textboxes. Anyway, I didn't have a issue. To
run, change "YourFormName" to your formname's name.

Public Sub ListFormsControlsBySection()
Dim c As Control
Dim s As String
Dim frm As Form
Dim i As Integer
Dim cSection As Integer
Dim ic As Integer

Set frm = Forms("YourFormName")

For i = 1 To 3
Select Case i
Case 1
cSection = acHeader
s = "Header"
Case 2
cSection = acDetail
s = "Detail"
Case Else
cSection = acFooter
s = "Footer"
End Select

For Each c In frm.Section(cSection).Controls
ic = ic + 1
Debug.Print c.Name, s, c.ControlType
Next
Next

MsgBox "Done " & ic

End Sub

Thanks Patrick.
That did it.

--
Bob Darlington
Brisbane
"Patrick Finucane" <patrickfinucanetx (AT) gmail (DOT) com> wrote

On Jul 28, 3:12 am, "Bob Darlington" <b... (AT) notheredpcman (DOT) com.au>
wrote:
The code below works in 2007. It cycles thru each form section and
cycles thru all controls in the section. Seems to work for me. It
does not associate a label to the text box...if that is what you
needed. The c.ControlType spit back either 100 or 109 since the form
only had labels and textboxes. Anyway, I didn't have a issue. To
run, change "YourFormName" to your formname's name.

Public Sub ListFormsControlsBySection()
Dim c As Control
Dim s As String
Dim frm As Form
Dim i As Integer
Dim cSection As Integer
Dim ic As Integer

Set frm = Forms("YourFormName")

For i = 1 To 3
Select Case i
Case 1
cSection = acHeader
s = "Header"
Case 2
cSection = acDetail
s = "Detail"
Case Else
cSection = acFooter
s = "Footer"
End Select

For Each c In frm.Section(cSection).Controls
ic = ic + 1
Debug.Print c.Name, s, c.ControlType
Next
Next

MsgBox "Done " & ic

End Sub

Thanks Patrick.
That did it.

--
Bob Darlington
Brisbane