We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Iterating through the Controls collection.

GeauxVols
GeauxVols asked
on
Medium Priority
575 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.
Comment
Watch Question

Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Retired
CERTIFIED EXPERT
Top Expert 2012
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.