Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    For each next loop (97)

    For each next loop
    I typed the following but get the error that

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: For each next loop (97)

    Hi,
    Not all controls have the same properties - for example, labels do not have Locked or Enabled properties. Therefore, if you have any labels on your forms you will get errors with that code. You may want to use ctl.ControlType to ensure that your code only runs on the relevant controls.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    New Lounger
    Join Date
    Nov 2001
    Location
    Twickenham, UK
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: For each next loop (97)

    or put in some error trapping that moves to the next control

  4. #4
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: For each next loop (97)

    ...or put a value in the Tag property of controls you wish to manipulate in this fashion, and test for the presence of that value inside the loop.
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: For each next loop (97)

    You also need to change your code to this:

    For Each ctl in [Forms]![frmDataEntry].Controls

    And then you can use something like this:

    Select ctl.ControlType
    Case acTextBox, acComboBox, acToggleButton, acListBox
    ctl.Locked = True

    End Select
    Charlotte

  6. #6
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: For each next loop (97)

    Thanks, but I got to it using this way:

    'lock record if submitted...
    If IsNull(Me.DateSentToAccounting) = False Or IsNull(Me.DateSentToJon) = False Then
    For Each ctl In Me.Controls
    If ctl.ControlType <> acTabCtl Then
    If ctl.ControlType <> acPage Then
    If ctl.ControlType <> acLabel Then
    If ctl.ControlType <> acCommandButton Then
    ctl.Locked = True
    ctl.Enabled = False
    End If
    End If
    End If
    End If
    Next ctl
    End If

    The above will lock the controls on the form nicely (basically all of them) but when I try to unlock I

  7. #7
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: For each next loop (97)

    It looks like Shane probably had the right idea in using theTag Property to let you check for info abut the control, then decide if to leave locked or unlocked!

    Peter

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: For each next loop (97)

    Hi,
    I'd suggest you use the select case method that Charlotte suggested as it will be quicker than multiple If statements. Something like:
    For Each ctl in [Forms]![frmDataEntry].Controls
    with ctl
    Select Case .ControlType
    Case acTextBox, acComboBox, acToggleButton, acListBox
    .Locked = True
    .enabled = false
    .tag = "LockedByMe"
    case else
    end select
    end with
    next ctl

    then to unlock them you can use:

    for each ctl in me.controls
    with ctl
    if .tag = "LockedByMe" then
    .locked = false
    .enabled = true
    .tag = ""
    end if
    end with
    next ctl

    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •