Iterating through the Controls collection.
Posted on 2004-03-22
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 = “”
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.