Results 1 to 8 of 8
  1. #1
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Looping through Controls on subform (2003)

    Some users of a db have limited rights, with security installed. These users have read--only rights. On a form with a number of subforms I want to hide or disable selected controls.
    I use the tag property of the controls to indicate what I want to happen. I have the code below. Instead of having to put code into each subform, I am hoping to put all the code in the main form.

    How do I refer to each of the controls on each subform. The line in red does not work.

    For Each ctrl In Me.Controls
    If ctrl.Tag = "hide" Then
    ctrl.Visible = False
    End If
    If (ctrl.Tag = "disable") Then
    ctrl.Enabled = False
    End If
    If (ctrl.ControlType = acSubform) Then
    <font color=red> For Each ctrl2 In ctrl.Form.Controls </font color=red>
    If (ctrl2.Tag = "disable") Then
    ctrl2.Enabled = False
    End If
    Next
    End If
    Next

    Another option I tried is
    Dim frm as form
    Set frm = ctrl.SourceObject
    SourceObject returns a string, not a form.

    My second question is related to the first.
    Some of these controls are Combo Boxes. Can I stop the users clicking the drop down in the combo to see all the options, other than by setting Enabled to false?
    Some of the combo boxes have a double click event to allow users to drill in to other data, and I want to allow them to do that.

    One option I tried is to set the listrows property to 1. This nearly does what I want, but if the combo is currently empty, it shows the first option when you click the dropdown. If it is not empty, it just shows the currently selected option which is OK.
    Regards
    John



  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Looping through Controls on subform (2003)

    What is the problem with the red line? A syntax error or a runtime error? What does it say?

    Wouldn't it be easier to provide a simplified form for limited users instead of trying to do it all in one form? You could use text boxes instead of combo boxes on the simplified form.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Looping through Controls on subform (2003)

    Thanks Hans

    The error with the red line is "run time error 424 Object Required"

    It may be easier to provide an alternative form. They have not stated using this db yet, but they have just asked for these limitations close the the point where they will start using it. I suspect that as they do use it, their requirements will continue to evolve. So once I have two forms, I would then have to maintain both of them.
    I hoped to find a relatively simple way of keeping just one form, at least until things settle down.
    Regards
    John



  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Looping through Controls on subform (2003)

    I don't understand the error - I copied/pasted your code into the On Click procedure of a command button on a form with a subform, and it ran without error.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Looping through Controls on subform (2003)

    Keeping the design of two forms synchronized is a hassle, but sometimes the effort required to do everything in one form is greater...

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Looping through Controls on subform (2003)

    You are right. I pasted it into another form and it worked.

    So I just went back to the original form, reinstated it (having commented it out) and it worked there as well. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Actually that is not true. What works for me is this: For Each ctrl2 In ctrl.Controls

    When I have For each ctrl2 in ctrl.Form.Controls, I get run time error 255 "You have an invalid reference to the property Form/Report"

    Sorry for this confusion, I have been trying various different versions.

    My other reason for using just one form is that I drill into this form from lots of other places, so I would need to find each of them change the code there as well. by doing it all within the form, I only have to change the one form.

    Thanks again.
    Regards
    John



  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Looping through Controls on subform (2003)

    I have come back to this to try to finish it off.

    It seems that
    <font color=red> For Each ctrl2 In ctrl.Form.Controls</font color=red>

    Is the correct syntax, but it throws error 2455 "You have an invalid reference to the property Form/Report" when I first open the form.
    Same thing happens whether I use the OnLoad, OnOpen or OnActivate events.
    If I trap that error, and put the code in the OnCurrent event instead it works fine.

    This syntax does not throw any errors, but does not seem find the controls either.
    For Each ctrl2 In ctrl.Controls
    Regards
    John



  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Looping through Controls on subform (2003)

    Ah - the notorious synchronization problem - opening/loading the main form and subform is intermingled, neither occurs fully before the other.

    The syntax

    For Each ctrl2 In ctrl.Controls

    could be used for a tab page, hence it doesn't cause a syntax error, but a subform as a control doesn't have any dependent controls.

Posting Permissions

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