访问 VBA - 我如何在 VBA 中有一个循环来允许我循环控制名称

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

提问by Justin

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

我在一个表单上有大约 10 个文本框，它们实际上用于显示而不是输入。它们被命名为txt_001_Name、 等txt_002_Title。为此使用了什么样的循环。

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

我应该使用什么样的 VBA 来实际循环文本框的名称？所以如果我要 debug.print 它看起来像：

txt_001_Title
txt_002_Title
txt_003_Title

This is probably pretty simple to do - all the more reason that I should learn how!

这可能很简单 - 我应该学习如何！

EDIT: Sorry, I should have been more descriptive about this.

编辑：抱歉，我应该对此进行更多描述。

Because of the above naming convention, I am looking to iterate through these text boxes so that I can perform something with each. What each of these 10 text boxes actually represent is numeric values, each having a SQL statement behind them in the form's onloadevent. 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 basically it ends up looking like a dashboard table.

由于上述命名约定，我希望遍历这些文本框，以便我可以对每个文本框执行某些操作。这 10 个文本框中的每一个实际上代表的是数值，在表单的onload事件中，每个文本框后面都有一个 SQL 语句。我还有另外一组十个，它们包含更加静态的数值，最后另外十个使用表达式简单地将前十个中的每一个除以相对的“第二个”十，并且该值以相对值结束3. 所以基本上它最终看起来像一个仪表盘。

'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 want to use this for the 'resulting' text boxes. I want to loop through the first ten and perform this easy calculation:

所以我实际上想将它用于“结果”文本框。我想遍历前十个并执行这个简单的计算：

 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.

所有命名约定“匹配”，因此我可以为此手动输入上面的 10 行，但我确信有更好的方法（循环遍历），我可能应该学习它。

回答by HansUp

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 Load event:

您可以从表单的 Load 事件中调用它：

Private Sub Form_Load()
    TextBoxNames Me
End Sub

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

但是，我不明白你想要完成什么。我意识到你想用ctl.Name做一些事情而不是Debug.Print，但我不知道那是什么。

Rather than computing a result for me.txt_001_resultand then assigning that value to the text box, consider setting the control source for txt_001_resultto txt_001_value / txt_001_calcand let Access put the proper value into txt_001_resultfor you.

与其计算me.txt_001_result的结果，然后将该值分配给文本框，不如考虑将txt_001_result的控制源设置为txt_001_value / txt_001_calc并让 Access为您将正确的值放入txt_001_result中。

In response to your comments, I'll suggest this procedure as a starting point for you to build upon:

针对您的意见，我建议将此过程作为您构建的起点：

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

回答by BradC

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)

我更喜欢使用 FOR EACH 来遍历文本框所在的控件集合（表单本身或面板控件）

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 othercontrols, you might need a typecheck in there (IF TYPEOF(myBox) = "TextBox" THEN ...)

也意味着您可以创建自定义组（通过将它们全部放在同一个容器中）。请注意，如果您有其他控件，则可能需要在其中进行类型检查 ( 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.

不过，我绝对更喜欢 For Each。

回答by David-W-Fenton

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.

我不能完全理解为什么你需要做你正在做的事情，但我有过这样的表格，我有一个未绑定的表格，我想显示任意数量的字段，所以我可以看到它。如果您只在表单的 OnOpen 事件中遍历控件集合，那很好。但是，如果您在绑定表单的 OnCurrent 中执行此操作，或者在未绑定表单中多次执行此操作，您可能会考虑我的一篇关于使用自定义集合管理控件组的长篇文章。