Access VBA - How would I have a loop in VBA that allows me to loop through control names

Question

So I have like 10 text boxes on a form that are actually used for display not entry. They are are named like txt_001_Name, txt_002_Title, etc..what kind of loop is used for this.

I was wondering what kind of VBA would I use to actually loop through the names of the text boxes. So if I was to debug.print it would look like:

txt_001_Title
txt_002_Title
txt_003_Title

probably pretty simple to do....although the more reason i should learn how!

EDIT: sooo I should have been more descriptive about this....apologies.

because of the above naming covention, I am looking to iterate through these text boxes so that I can preform something with each. So what each of these 10 text boxes actually represent is numeric values that each have a sql statement behind them on the forms load event. I also have another set of ten that hold numeric values that are much more static, and finally another ten that use an expression to simply divide each of the first ten, against the relative "second" ten, and the value ends up in the relative 3. So bascially it ends up looking like a dashboard table.

'first ten'       'second ten'     'resulting ten'
---------------------------------------------------
txt_001_value      txt_001_calc     txt_001_result
txt_002_value      txt_002_calc     txt_002_result

etc.

So I actually wanting to use this for the 'resulting' text boxes. I want to loop through the first ten, preform the little easy calc of:

 me.txt_001_result = me.txt_001_value / me.txt_001_calc     

all the naming conventions "match up", so I can manually type out the 10 lines of the above for this, but i am sure there is a better way (loop through this), and I should probably learn it.

Thanks Justin

Answer 1

I prefer to use a FOR EACH to iterate through the controls collection of whatever the textboxes are on (either the form itself or a panel control)

dim myBox as Textbox
For each myBox in myForm
    myBox.Text = "hello"
Next

Also means you can make custom groups (by putting them all on the same container). Note that if you have other controls, you might need a typecheck in there (IF TYPEOF(myBox) = "TextBox" THEN ...)

You could also do it like:

dim i as integer
For i = 1 to 10
     myForm.Controls("txt_00" & i & "_Title").Text = "hello"
Next i

I definitely prefer the For Each, though.

Answer 2

You can list the names of textbox controls with a simple procedure like this:

Public Sub TextBoxNames(ByRef pfrm As Form)
    Dim ctl As Control
    For Each ctl In pfrm.Controls
        If ctl.ControlType = acTextBox Then
            Debug.Print ctl.Name
        End If
    Next ctl
    Set ctl = Nothing
End Sub

You could call it from the form's Open event:

Private Sub Form_Open(Cancel As Integer)
    TextBoxNames Me
End Sub

However, I don't understand what you're trying to accomplish. I realize you want to do something with ctl.Name other than Debug.Print, but I don't know what.

Rather than computing a result for me.txt_001_result and then assigning that value to the text box, can't you just set the control source for txt_001_result to txt_001_value / txt_001_calc and let Access put the proper value into txt_001_result for you?

Update: Can you build on this procedure?

Public Sub MyTextBoxValues()
    Const cintLastTextBoxNum As Integer = 10
    Dim i As Integer
    Dim strValueControl As String
    Dim strCalcControl As String
    Dim strResultControl As String
    Dim strPrefix As String

    For i = 1 To cintLastTextBoxNum
        strPrefix = "txt_" & Format(i, "000")
        'txt_001_value      txt_001_calc     txt_001_result '
        strValueControl = strPrefix & "_value"
        strCalcControl = strPrefix & "_calc"
        strResultControl = strPrefix & "_result"
        'me.txt_001_result = me.txt_001_value / me.txt_001_calc '
        'Debug.Print strResultControl, strValueControl, strCalcControl '
        Me.Controls("strResultControl") = Me.Controls("strValueControl") / _
            Me.Controls("strCalcControl")
    Next i
End Sub

Answer 3

I can't entirely understand why you need to do what you're doing, but I've had forms like that where I had an unbound form that I wanted to display an arbitrary number of fields, so I can see it. If you're walking the collection of controls only in the form's OnOpen event, that's fine. But if you're doing it in the OnCurrent of a bound form, or multiple times in an unbound form, you might consider a long post of mine on using custom collections to manage groups of controls.