CODEWITHSUNDEEP
vbams-access
AJK
AJK

Reputation: 87

Is there a way in VBA to iterate through specific objects on a form?

I would like to have a subroutine in VBA that conditionally changes the Enabled property of each of 20+ buttons on a form via iteration rather than code them all by hand. These buttons are named similar to tables that they process. For example: A table to process is called "CUTLIST"; its corresponding button is called "but_CUTLIST". There is another table that holds the list of tables to be processed (used for iteration purposes in other subs).

What I have so far...

Private Sub txt_DataSet_GotFocus()

Dim sqlQry as String
Dim butName As String
Dim tableList As Recordset
Dim tempTable As Recordset

Set tableList = CurrentDb.OpenRecordset("TableList") 'names of tables for user to process
tableList.MoveFirst 'this line was corrected by moving out of the loop

Do Until tableList.EOF
    sqlQry = 'SQL query that determines need for the button to be enabled/disabled
    Set tempTable = CurrentDb.OpenRecordset(sqlQry)
    If tempTable.RecordCount > 0 Then 
        'begin code that eludes me
        butName = "but_" & tableList!tName
        Me(butName).Enabled False
        'end code that eludes me
    End If
    tableList.MoveNext
Loop  

End Sub

If I remember correctly, JavaScript is capable of calling upon objects through a variable by handling them as elements of the document's object "array." Example: this[objID]=objVal Is such a thing possible with VBA or am I just going about this all wrong?

Viewing other questions... is this what's called "reflection"? If so, then this can't be done in VBA. :(

In case more explanation helps to answer the question better... I have a utility that runs SQL queries against a pre-defined set of tables. Each table has its own button, so that the user may process a query against any of the tables as needed. Depending on circumstances happening to data beforehand, any combination of the tables may need to be queried via pressing of said buttons. Constantly referring to the log, to see what was already done, gets cumbersome after processing several data sets. So, I'd like to have the buttons individually disable themselves if they are not needed for the currently focused data set. I have another idea on how to make that happen, but making this code work would be faster and I would learn something.

Upvotes: 0

Views: 181

Answers (1)

Michael Daniloff
Michael Daniloff

Reputation: 725

I'm not an expert on VBA, but I would re-arrange the code to take advantage of the fact that you can iterate through the control collection in the user form

Something like this:

Dim ctrl as Control

For Each ctrl in UserForm1.Controls

  If TypeName(ctrl) = "Button" Then
    ctrl.Enabled = True
  End If
Next

You can pass the button name to some other function (from this loop) to determine whether the button in question should be enabled / disabled.

Upvotes: 1

Related Questions