Solved

Iterating through the Controls collection.

Posted on 2004-03-22
3
549 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
3 Comments
 
LVL 3

Assisted Solution

by:gmleeman
gmleeman earned 25 total points
Comment Utility
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 100 total points
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now