How do I get the index of a control for reference?

[jbrumbau]

Hello,

I'm trying to obtain a form control's index. For example, a form may have 30 or so controls on it. To refer to control 15, I can do:

frm.Controls(15)
 

My goal is to populate a listbox with the indexes of all controls that have a tag, executing on Form_Load. Then, I wish to reference all controls in the listbox by index on Form_BeforeUpdate. I do this for change tracking in a form. Currently I refer to the controls by their name (a string), but it would be faster if I can refer to them by index (a number) as shown above. This is important as some of my slowest forms are long winded datasheets with about 50 controls and 100 labels! Here is a simplified version of what I currently have:

 
'---FORM CODE---
 
Private Sub Form_Load()    'Place in each form with a listbox of control sources
    Call populateControlList(Me, Me!lstControlSources)
End Sub
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Not fillLastUpdated(Me, Me!txtItemKey, Me!lstControlSources, Me!cbbProcured.Value = True) Then
        Cancel = True
        Me.Undo
    End If
End Sub
 
'---GLOBAL MODULE CODE---
 
Sub populateControlList(ByRef frm As Form, ByRef listCtl As Control)
    Dim ctl As Control
 
    listCtl.RowSource = ""
    For Each ctl In frm.Controls
        If LenB(ctl.Tag) > 0 Then
            Call listCtl.addItem(ctl.Name)
        End If
    Next ctl
End Sub
 
Function fillLastUpdated(ByRef frm As Form, ByRef uniqueCtl As TextBox, ByRef listCtl As Control, Optional undoChanges As Boolean = False) As Boolean
    If frm.Dirty And Not frm.NewRecord Then
        Dim rsExist As Recordset
        Dim ctl As Control
 
        Set rsExist = CurrentDb.OpenRecordset(frm.RecordSource, dbReadOnly)
        ...
        For i = 0 To listCtl.ListCount - 1
            ...
            Set ctl = frm.Controls(listCtl(i))
            If rsExist.Fields(ctrlSource) = ctl.Value Then
                ...Do Stuff
            End If
            ...
        Next i
        ...
    Else
        fillLastUpdated = True
    End If
    ...
End Function
 

So essentially, how do I populate control indexes into a listbox instead of control names? Please let me know what to change

Call listCtl.addItem(ctl.Name)
 

into.

Thanks in advance.

[Stewart Ross]

Hi. The form's controls collection, in common with all other collection objects, does not have a method which returns the index for a given object. In part this is because the position of an item within a collection may vary over time, depending on whether or not new objects are added to the collection or deleted from it.

That being said, I think you can modify your sub PopulateControlList to do what you want fairly easily, by substituting a counted-FOR loop instead of a FOR EACH loop, then adding the FOR loop index value to the list control in place of the control's name:

Sub populateControlList(ByRef frm As Form, ByRef listCtl As Control) 
    Dim ctl As Control 
    Dim intCtlCount as Integer
    Dim intCurrentCtl as Integer
    intCtlCount = frm.Controls.Count
    listCtl.RowSource = "" 
    For intCurrentCtl = 0 to intCtlCount-1 'zero-based count
        set ctl = frm.controls(intCurrentCtl)
        If LenB(ctl.Tag) > 0 Then 
            Call listCtl.addItem(intCurrentCtl) 
        End If 
    Next intCurrentCtl 
End Sub

Because the value stored in the listbox will be treated as text (not an integer number) you will probably need to modify line 37 to ensure you are passing an integer value as the index to the controls collection:

Set ctl = frm.Controls(CInt(listCtl(i)))

-Stewart

[jbrumbau]

Hey, that is a good idea to use the For loop index to get the control index. I'm going to test it out and let you know how it works.

Thanks!

[jbrumbau]

Awesome it works! I then call it from the listCtl with the following line of code:

frm.Controls(CInt(listCtl.ItemData(i))).Value
 

Thanks a bunch, you're the man!