Loop through Report Controls collection

Discussion in 'Microsoft Access VBA Modules' started by Alan Z. Scharf, Aug 16, 2004.

  1. I want to loop through the collection of controls on a report so that I can
    set the font size of each depending on the [Pages] count in a Report Header
    OnPrint event.

    My code below produces "Object doesn't support this method"

    Can someone help with proper syntax please?

    Thanks.

    Alan

    Code
    ------
    Private Sub PageHeader0_Print(Cancel As Integer, PrintCount As Integer)
    ' Reduce font size to 10 from present 11 for all fields if page count >
    2

    If [Pages] > 2 Then
    Dim col As New Collection
    Dim ctl As Control

    For Each ctl In Me.Controls
    Me.Controls(ctl).FontSize = 18
    Next
    End If
    End Sub
     
    Alan Z. Scharf, Aug 16, 2004
    #1
    1. Advertisements

  2. Alan Z. Scharf

    Tim Ferguson Guest

    "Alan Z. Scharf" <> wrote in
    news::

    > My code below produces "Object doesn't support this method"


    You don't say which line causes the error, so I can only hazard a guess
    that it is this one:

    > Me.Controls(ctl).FontSize = 18


    Not all controls have a .FontSize property. Easiest thing is simply to dump
    the error by wrapping it in a On Error Resume Next... On Error Goto 0 pair;
    or else you could test explicitly by iterating the ctl.Properties
    collection.

    Incidentally, I have just noticed that the line as quoted is not even
    legal. You can index the .Controls collection using a number or a string
    (i.e Me.Controls(12) or Me.Controls("sfrmVisits") ) but ctl is dimmed as a
    Control. I assume this is a typo and you meant

    ctl.FontSize = 18

    HTH


    Tim F
     
    Tim Ferguson, Aug 16, 2004
    #2
    1. Advertisements

  3. Tim,

    Thanks for your reply.

    I got it using:

    For Each ctl In Controls
    ctlName = ctl.Name
    If Me.Controls(ctlName).ControlType = acLabel Or
    Me.Controls(ctlName).ControlType = acTextBox Then
    Me.Controls(ctlName).FontSize = 10
    End If
    Next

    "Tim Ferguson" <> wrote in message
    news:Xns9547C13F55424garbleme4455656@207.46.248.16...
    > "Alan Z. Scharf" <> wrote in
    > news::
    >
    > > My code below produces "Object doesn't support this method"

    >
    > You don't say which line causes the error, so I can only hazard a guess
    > that it is this one:
    >
    > > Me.Controls(ctl).FontSize = 18

    >
    > Not all controls have a .FontSize property. Easiest thing is simply to

    dump
    > the error by wrapping it in a On Error Resume Next... On Error Goto 0

    pair;
    > or else you could test explicitly by iterating the ctl.Properties
    > collection.
    >
    > Incidentally, I have just noticed that the line as quoted is not even
    > legal. You can index the .Controls collection using a number or a string
    > (i.e Me.Controls(12) or Me.Controls("sfrmVisits") ) but ctl is dimmed as a
    > Control. I assume this is a typo and you meant
    >
    > ctl.FontSize = 18
    >
    > HTH
    >
    >
    > Tim F
    >
     
    Alan Z. Scharf, Aug 16, 2004
    #3
  4. Your code is doing a lot more work than is required:

    For Each ctl In Controls
    If ctl.ControlType = acLabel Or ctl.ControlType = acTextBox Then
    ctl.FontSize = 10
    End If
    Next


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)



    "Alan Z. Scharf" <> wrote in message
    news:...
    > Tim,
    >
    > Thanks for your reply.
    >
    > I got it using
    >
    > For Each ctl In Controls
    > ctlName = ctl.Name
    > If Me.Controls(ctlName).ControlType = acLabel Or
    > Me.Controls(ctlName).ControlType = acTextBox Then
    > Me.Controls(ctlName).FontSize = 10
    > End If
    > Next
    >
    > "Tim Ferguson" <> wrote in message
    > news:Xns9547C13F55424garbleme4455656@207.46.248.16...
    > > "Alan Z. Scharf" <> wrote in
    > > news::
    > >
    > > > My code below produces "Object doesn't support this method"

    > >
    > > You don't say which line causes the error, so I can only hazard a guess
    > > that it is this one:
    > >
    > > > Me.Controls(ctl).FontSize = 18

    > >
    > > Not all controls have a .FontSize property. Easiest thing is simply to

    > dump
    > > the error by wrapping it in a On Error Resume Next... On Error Goto 0

    > pair;
    > > or else you could test explicitly by iterating the ctl.Properties
    > > collection.
    > >
    > > Incidentally, I have just noticed that the line as quoted is not even
    > > legal. You can index the .Controls collection using a number or a string
    > > (i.e Me.Controls(12) or Me.Controls("sfrmVisits") ) but ctl is dimmed as

    a
    > > Control. I assume this is a typo and you meant
    > >
    > > ctl.FontSize = 18
    > >
    > > HTH
    > >
    > >
    > > Tim F
    > >

    >
    >
     
    Douglas J. Steele, Aug 16, 2004
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Albert D. Kallal

    Re: How to loop through all keys of a collection?

    Albert D. Kallal, Jul 4, 2003, in forum: Microsoft Access VBA Modules
    Replies:
    1
    Views:
    674
    Francesc Hervada-Sala
    Jul 5, 2003
  2. Guest

    Iteration through controls collection

    Guest, Jun 27, 2005, in forum: Microsoft Access VBA Modules
    Replies:
    2
    Views:
    134
    Bas Cost Budde
    Jun 28, 2005
  3. Guest

    RE: Loop through TableDef collection using ADO?

    Guest, Dec 31, 2006, in forum: Microsoft Access VBA Modules
    Replies:
    0
    Views:
    1,693
    Guest
    Dec 31, 2006
  4. Guest

    Re: Loop through TableDef collection using ADO?

    Guest, Jan 1, 2007, in forum: Microsoft Access VBA Modules
    Replies:
    0
    Views:
    590
    Guest
    Jan 1, 2007
  5. Andrew

    Loop through controls

    Andrew, Apr 29, 2008, in forum: Microsoft Access VBA Modules
    Replies:
    2
    Views:
    119
    Andrew
    Apr 29, 2008
Loading...

Share This Page