?
Solved

Iterating through the Controls collection.

Posted on 2004-03-22
3
Medium Priority
?
558 Views
Last Modified: 2007-12-19
Hello,

I have the following code I am using in a VBA module:

Private Sub Form_Close()
      'TabIndex for Approved Date fields = 15-26 and 39-50
      'TabIndex for Date fields = 3-14 and 27-38
      Dim ctl2 As Control
      Dim intIndex As Integer
      For Each ctl2 In Me.Controls
            If ctl2.ControlType = acTextBox Then
                  If Right$(ctl2.Properties("Name"), 8) = "Approved" Then
                  ‘ctl2.Properties("TabIndex") should correspond to the TabIndex of the textbox
                  intIndex = ctl2.Properties("TabIndex") - 12
                        If Not IsNull(ctl2.Value) Then
                        'need code here to set the corresponding Date = “”
                        End If
                  End If
            End If
      Next ctl2
End Sub

I will explain this code as best I can. I have Date fields and ApprovedDate fields. Each Date field corresponds to the date a specific item was initiated in a database. Each Date maps to a DateApproved by a difference of 12. That is to say, DateApproved TabIndex 15 <-> Date TabIndex 3, etc. Once the item is approved, the Date field needs to be set to “”. So, what I would like to accomplish is to cycle through the Controls collection, and, if the control is a Textbox, and the Name of the control ends in Approved (thus identifying all of the DateApproved textboxes), then I want to update the corresponding Date to “”, assuming that the DateApproved is Not Null (A null DateApproved indicates that the item has not yet been approved). I’m having trouble accessing the Value of the Date textbox that corresponds to the DateApproved in the Controls collection. I have tried indexing it using the intIndex in my subroutine (i.e., Me.Controls(intIndex)), but this gives me a runtime error. I’m sure there is something simple I am missing here, but the usual epiphany is escaping me. Any help that can be offered would be greatly appreciated.

Thanks.
0
Comment
Question by:GeauxVols
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 3

Assisted Solution

by:gmleeman
gmleeman earned 75 total points
ID: 10650819
Hi

I think you are misusing the TabIndex property.  The TabIndex defines the order that the controls get the focus when you press the TAB key, and do not reflect the controls' position in the Controls Collection. Thats why Me.Controls(intIndex)) crashes. I assume its an "Index out of bounds" error.

I think I get the gist of what you want to do and have two suggestions:

Create an array of text boxes (Use the COPY and PASTE feature on a Text Box, and choose YES when VB asks if you want a control array - then you can keep pasting more text boxes until you have enough text boxes in your array)
If fact - make two arrays of text boxes - one for the Date fields and the other for the DateApproved fields.  This way you can use the Index property of the Date field to index the DateApproved TextBox.  create the arrays so that the indexes map directly would seem more elegant.  ie. The Index for the Date text box is exactly the same Index for the DateApproved Text box.  

(Also - you get rid of the need to have your text box names end in "Approved" which limits your enhancement of the code and understanding years down the track...)

You will notice the Index property gets a value when you create a control array (assuming the Name property is the same).  You can change the Index value at run - time, but its easier to set them at design time in this case. (The Index values are automatically set when you PASTE new text boxes into an array)

Secondly - you will probably find that you can eliminate the need for iterating through the Controls Collection when you have the arrays in place, as all you need to do is iterate throuh the array of controls using - say - a FOR loop.


0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 300 total points
ID: 10672210
gmleeman is right so say that you are misusing the TabIndex property. You have assumed that it is the same as index item of the controls collection.
However the suggestion that you create an array of textbox controls is not applicable to VBA. Control arrays are a feature of VB (1 to 6) only.

You can still use the TabIndex property but you'd have to step through the controls collection to find the one with the relevant TabIndex

     Dim ctl As Control
     Dim ctl2 As Control
     Dim intIndex As Integer
     For Each ctl2 In Me.Controls
          If ctl2.ControlType = acTextBox Then
               If Right$(ctl2.Properties("Name"), 8) = "Approved" Then
               ‘ctl2.Properties("TabIndex") should correspond to the TabIndex of the textbox
               intIndex = ctl2.Properties.TabIndex - 12
                    If Not IsNull(ctl2.Value) Then
                       'look for matching control
                        For Each ctl In Me.Controls
                            If ctl.TabIndex = intIndex Then
                                ctl.Text = ""
                                Exit For
                            End If
                        Next
                    End If
               End If
          End If
     Next ctl2

An alternative suggestion would be to name the controls appropriately; e.g.: txtDate_01, txtDate_02... and txtApproved_01, txtApproved_02...

     Dim ctl2 As Control
     Dim strSuffix as String
     For Each ctl2 In Me.Controls
          If ctl2.ControlType = acTextBox Then
               If Left$(ctl2.Name, 11) = "txtApproved" Then
                     If Not IsNull(ctl2.Value) Then
                        strSuffix = Mid$(ctl2.Name,13,2)
                        Me.Controls("txtDate_" & strSuffix).Text = ""
                     End If
               End If
          End If
     Next ctl2

0
 

Author Comment

by:GeauxVols
ID: 10680044
Thanks to you both. I implemented the following code, a variation of that submitted by GrahamSkan:

Dim ctl2 As Control
     Dim strPrefix As String
     For Each ctl2 In Me.Controls
          If ctl2.ControlType = acTextBox Then
               If Right$(ctl2.Name, 12) = "DateApproved" Then
                     If Not IsNull(ctl2.Value) Then
                        strPrefix = Replace(ctl2.Name, "DateApproved", "")
                        Me.Controls(strPrefix & "Date").SetFocus
                        Me.Controls(strPrefix & "Date") = ""
                     End If
               End If
          End If
    Next ctl2
End Sub

This way, I did not have to rename the controls in the form. The original code also would not function because the control being changed was not in focus, so I added that as well. Curiously, the code does not work on a Close event (I'm guessing that the form is unbinding itself from the table on which it is bound). It seems to run well from LostFocus, or a Command Button.

Thanks for the suggestions.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month14 days, 10 hours left to enroll

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question