Results 1 to 15 of 15
  1. #1
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296

    For each loop with an array of controls but the controls aren't being set?

    I am trying to cycle through a specific set of controls. I do this 2 or 3 times on the form so I figured it best to create an array with the controls I want and cycle through that.


    Here is me setting the array up.
    Code:
    Dim DebugControls(0 To 7) As Control
    
    
    Private Sub Form_Open(Cancel As Integer)
    Set DebugControls(0) = Me.ImmediateTxtFront
    Set DebugControls(1) = Me.ImmediateLblFront
    Set DebugControls(2) = Me.ImmediateTxtBack
    Set DebugControls(3) = Me.ImmediateLblBack
    Set DebugControls(4) = Me.RecreateConnectionsBttn
    Set DebugControls(5) = Me.ClearImmediateWindowBttn
    Set DebugControls(6) = Me.ClearFormBttn
    Set DebugControls(7) = Me.HideDebugMenuBttn
    End Sub
    
    Private Sub DebugBttn_Click()
        Dim name As Variant, i As Integer: i = 0
        For Each name In DebugControls
            Select Case i
            Case 2
            Case 3
            Case Else
                DebugControls(i).Visible = True
            End Select
            i = i + 1
        Next
    End Sub
    I have tried a couple different variations but it seems like the problem lies with the way I am setting the controls in the array?
    I tried it without the "Set" but it would just throw me an error.

    Edit: Also would it be better to use something like "For i = 0 to 7"?

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    OK, adapt this:

    Code:
    Dim DebugControls(7) As Control
    
    Private Sub Form_Open(Cancel As Integer)
    Set DebugControls(0) = Me.Text0
    Set DebugControls(1) = Me.Label0
    Set DebugControls(2) = Me.Text1
    Set DebugControls(3) = Me.Label1
    Set DebugControls(4) = Me.button4
    Set DebugControls(5) = Me.button5
    Set DebugControls(6) = Me.button6
    Set DebugControls(7) = Me.Button7
    End Sub
    
    Private Sub DebugBttn_Click()
        Dim i As Integer, ctl As Control
        For i = 0 To UBound(DebugControls)
            Select Case i
                Case 2
                Case 3
                Case Else
                    Set ctl = DebugControls(i)
                    ctl.Visible = True
            End Select
        Next
    End Sub

  3. #3
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by davegri View Post
    OK, adapt this:

    Code:
    Dim DebugControls(7) As Control
    
    Private Sub Form_Open(Cancel As Integer)
    Set DebugControls(0) = Me.Text0
    Set DebugControls(1) = Me.Label0
    Set DebugControls(2) = Me.Text1
    Set DebugControls(3) = Me.Label1
    Set DebugControls(4) = Me.button4
    Set DebugControls(5) = Me.button5
    Set DebugControls(6) = Me.button6
    Set DebugControls(7) = Me.Button7
    End Sub
    
    Private Sub DebugBttn_Click()
        Dim i As Integer, ctl As Control
        For i = 0 To UBound(DebugControls)
            Select Case i
                Case 2
                Case 3
                Case Else
                    Set ctl = DebugControls(i)
                    ctl.Visible = True
            End Select
        Next
    End Sub
    I did this but get an error. I made sure to freshly open the form and click the button.
    It says ctl.visible is null and that the "Object doesn't support this property or method" because it can't set visible to null I assume.

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I did this but get an error. I made sure to freshly open the form and click the button.
    It says ctl.visible is null and that the "Object doesn't support this property or method" because it can't set visible to null I assume.



    The code is example code for my sample form. You need to change the form control names to your form's names like in your original post.

    Click image for larger version. 

Name:	frmSample.png 
Views:	20 
Size:	11.7 KB 
ID:	49938

  5. #5
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by davegri View Post
    The code is example code for my sample form. You need to change the form control names to your form's names like in your original post.

    Click image for larger version. 

Name:	frmSample.png 
Views:	20 
Size:	11.7 KB 
ID:	49938
    I did that. All the control names were already setup exactly like you had it.
    Code:
    Private Sub Form_Open(Cancel As Integer)
    Set DebugControls(0) = Me.ImmediateTxtFront
    Set DebugControls(1) = Me.ImmediateLblFront
    Set DebugControls(2) = Me.ImmediateTxtBack
    Set DebugControls(3) = Me.ImmediateLblBack
    Set DebugControls(4) = Me.RecreateConnectionsBttn
    Set DebugControls(5) = Me.ClearImmediateWindowBttn
    Set DebugControls(6) = Me.ClearFormBttn
    Set DebugControls(7) = Me.HideDebugMenuBttn
    End Sub
    
    Private Sub DebugBttn_Click()
    Dim i As Integer, ctl As Control
    For i = 0 To UBound(DebugControls)
        Select Case i
            Case 2
            Case 3
            Case Else
                Set ctl = DebugControls(i)
                ctl.Visible
        End Select
    
    Next
    End Sub
    If I put a code break on the variable setting it says that the controls are null while the form is opening.
    Edit: Upon opening the ImmediateTxtBack is a null textbox upon opening the form. Though setting it to a ZLS doesn't seem to fix the issue. That just seems to make ctl = "".

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Maybe this would be easier if you used the control .Tag property and looped through, looking for the Tag value? If the labels are attached to a control, you don't have to worry about labels. Also wondering why the Select Case block.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Don't know why code isn't working in your DB. Maybe related to Access 2002 vs Access 2019. Here's the sample DB.

    formSample-davegri.zip
    Last edited by davegri; 03-21-2023 at 11:29 AM. Reason: rev

  8. #8
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by davegri View Post
    Don't know why code isn't working in your DB. Maybe related to Access 2002 vs Access 2019. Here's the sample DB.

    formSample-davegri.zip
    Oddly enough I was trying this in Access 2019. I will download the DB and take a look. It seems like its trying to take the value/text of the control rather than assign the control itself.

  9. #9
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    Control values are not available on form open. Try the load event.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  10. #10
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by Micron View Post
    Maybe this would be easier if you used the control .Tag property and looped through, looking for the Tag value? If the labels are attached to a control, you don't have to worry about labels. Also wondering why the Select Case block.
    I'm unfamiliar? I am trying to hide or show a set group of controls. A loop would work but there are a lot of other controls that it would have to go through and therefore be unoptimized and I want to make it optimized and it would bug me to not to know why this doesn't work.

    The select case is unique to that particular sub. I am using a trick to create a console on the form where the front window shows the text and will keep staying at the bottom of the text no matter how many lines are appended and just show a "..." at the top of the window. clicking on the front view shows the back while hiding the front. The rough reason for this was cause the cursor wouldn't jump to the bottom of the text window when selected or something like that. I can't remember the exact issue. So when I initially click the DebugBttn and have the controls show I want the back to be hidden/have the visible property stay false. So I was using the same variable set and I just put in a select case so that it does nothing on those controls. Its a trick I picked up reading someone else's code. I uh never thought I'd get to this point where I was using tricks like this to be honest. I hope its a good one to have on my toolbelt/good practice.

  11. #11
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by Welshgasman View Post
    Control values are not available on form open. Try the load event.
    I tried this and get the same result. Also interestingly enough I was able to set the value of the control in the open event.
    I am only trying to assign the control to a variable upon open. I don't need to read their ".Visible" property until I press the button.

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    the cursor wouldn't jump to the bottom of the text window when selected
    Possibly because SelStart property was not used/set.
    Looping over controls and checking for the Tag property is common. You should be able to find code if you need it. Perhaps it won't be suitable seeing as how you're omitting some of the array objects.

    I doubt it is ctl.Visible that would be null as opposed to ctl. Suggest you step through your code if you're sticking with it and check your variables as you go. If I'm not sure of an object being set (i.e. if mousing over isn't clear) then testing by invoking Name property is pretty useful since almost every object has a name. So
    ?ctl.Name
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Unless you have many dozens of controls the tag loop will be unnoticeable in it's timing, and is as efficient as any other method of hiding/enabling controls.

    I use it frequently, you can assign multiple "switches" to a controls tag property (e.g. A B C D Z) and simply use Instr() to determine if it's present or not.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  14. #14
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 11 Office 365
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    I agree with minty and micron regarding using the Tag.

    Another option similiar to your original post would be to use a collection.

    something along the lines of

    Code:
    Dim CtlCol As New Collection
    
    CtlCol.Add Me.ImmediateTxtFront
    CtlCol.Add Me.ImmediateLblFront
    CtlCol.Add Me.ImmediateTxtBack
    CtlCol.Add Me.ImmediateLblBack
    CtlCol.Add Me.RecreateConnectionsBttn
    CtlCol.Add Me.ClearImmediateWindowBttn
    CtlCol.Add Me.ClearFormBttn
    CtlCol.Add Me.HideDebugMenuBttn
    
    CtlCol(3).Visible = False
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  15. #15
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by davegri View Post
    Don't know why code isn't working in your DB. Maybe related to Access 2002 vs Access 2019. Here's the sample DB.

    formSample-davegri.zip
    I got this method working.
    I think it was because I was just missing an "= True"

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Text box controls aren't populated in PrintPreview
    By GraeagleBill in forum Reports
    Replies: 30
    Last Post: 10-23-2021, 05:45 PM
  2. Form Me. Values aren't getting captured in Loop
    By Enrightt in forum Programming
    Replies: 1
    Last Post: 10-05-2018, 02:51 PM
  3. Replies: 6
    Last Post: 06-19-2017, 03:33 PM
  4. Replies: 3
    Last Post: 04-29-2017, 12:09 PM
  5. Replies: 5
    Last Post: 08-08-2011, 02:57 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums