问题:

I need to requery only specific combo boxes in a form, which I want to have in an array. The combo boxes are not in order and I do not want to loop through all combo boxes in the form.

The best way I can compare it to is the following in Excel for looping through specified worksheets:

Dim ws As Worksheet
For Each ws In Worksheets("Sheet1", "Sheet4", "Sheet7")
    ' do something
Next ws

I cannot figure out how to do this in Access. I have tried variations of this:

For Each Control In Me.Controls(Me.cbo1, Me.cbo4, Me.cbo7)
    Control.Requery
Next Control

回答1:

For a lightweight approach, you can put the combo box names in a comma-separated string, Split() the string into an array, and then loop through the array members.

Const cstrNames As String = "cbo1,cbo4,cbo7"
Dim varName As Variant

For Each varName In Split(cstrNames, ",")
    Me.Controls(varName).Requery
Next

If you want to share that list between other procedures in your form, you can make the string a module-level constant instead of declaring it again in each procedure.

回答2:

There are a couple of approaches, but you could put them in a Collection:

Private Sub Command7_Click()
    Dim coll As New Collection
    Dim item As ComboBox

    coll.Add Me.Combo1
    coll.Add Me.Combo5
    coll.Add Me.Combo3

    For Each item In coll
        item.Requery
    Next item
End Sub