How do I use variable substitution in commands? 2

[mdav2]

I have some code that will tell me the name of field that I want to set the focus to:

EG
strField = "txtClientRef"

what I want to do is run a command like this:

me.strField.setfocus

I could build the string using the following syntax:
strCommand = "me." & strfield & ".setfocus"

How would I execute that command?

Mark Davies
Warwickshire County Council

 

[Remou]

How about:

Me(strField).SetFocus

 

[mdav2]

Yep that would work and is more efficeint than the code I came up with.

The first part of the loop works out the field I need to set the focus to. I did it using a second loop but your method makes more sense. I suppose (in theory) that if working with any kind of object if I know its name I can reference it in your way.

    ' Declare variables
    Dim strControl      As String   ' Holds the name of the control that focus is set to in view mode
    Dim lngTabIndexLow  As Long     ' Holds the tab index number of the control that focus is set to in view mode

    ' Initialise variables
    strControl = ""
    lngTabIndexLow = 999999         ' Set High so we can grab the lowest tab index value (assumes you have the tab indexes set correctly)
    
    ' Set the focus to the first field & disable all tab controls on the form
    For Each ctlFormControls In Me.Controls
    Select Case ctlFormControls.ControlType
        Case acTextBox, acComboBox, acCheckBox, acListBox, acOptionGroup, acOptionButton
            ' Work out the control with lowest tab index that can have have a value set to
            If ctlFormControls.Enabled = True And ctlFormControls.TabIndex < lngTabIndexLow Then
                strControl = ctlFormControls.Name
                lngTabIndexLow = ctlFormControls.TabIndex
            End If
        Case acTabCtl
            ' Disable all tab controls
            ctlFormControls.Enabled = False
    End Select
    Next ctlFormControls
    
    For Each ctlFormControls In Me.Controls
        If ctlFormControls.Name = strControl Then
            ctlFormControls.SetFocus
        End If
    Next ctlFormControls

Mark Davies
Warwickshire County Council

 

[MajP]

And this is why Remous code works
1) In vba you have collections which are like an array of objects
2) All collections can use a numerical index to refer to an item in the collection. For example the "controls" collection of the controls on a form
set myControl = me.controls.item(0)
returns the first control in the collection

3) Some collections have a named library so you can use a string index instead of the numerical index. The index is usually the same as the name property.

set myControl = me.controls.item("theControlName")

or in this case a variable

dim strName as string
strName = "theControlName"
set myControl = me.controls.item(strName)

4) Now for the confusing part. VB has default properties and if a property is a default you do not need to reference it. The item property is the default for all collections so
me.controls.item(strName)
becomes
me.controls(strName)

and the controls collection is the default property for a form so
me.controls(strName)
becomes
me(strName)