CODEWITHSUNDEEP
ms-accessvba
finjo
finjo

Reputation: 376

Loop through every control in a form, with the form name defined as a variable

I am trying to create a basic search which looks for a partial keyword match of any control in a specified form. The form name is selected via combo box and is stored as a variable.

How do I use this to loop through the controls of the selected form?

I can easily loop through the controls of the current form with the following:

For Each ctrl In Me.Controls
    Debug.Print ctrl.Name
Next ctrl

But I can't figure out how to reference an external form, with the variable essentially replacing Me.

I've tried using:

Dim ctrl as Control
Dim variableName as String
variableName = Me.cmboFormName

For each ctrl in Forms(variableName).Controls
    Debug.Print ctrl.Name
Next ctrl

But this just returns error 438 (Object doesn't support this property or method).

Upvotes: 0

Views: 2074

Answers (4)

Krish
Krish

Reputation: 5917

Forms contains collection of opened forms. Unfortunately you cannot access a closed form using a variable name.

Just check whether the form is available. if not open the form either in nomal or design view and continue your code like this.

Dim ctrl as Control
Dim variableName as String
variableName = Me.cmboFormName

If Not (FN_FORM_ISLOADED(variableNAme)) Then
    'Open your form in desired view.
    DoCmd.OpenForm variableNAme, acNormal
End If

For each ctrl in Forms(variableName).Controls
    Debug.Print ctrl.Name
Next ctrl

Place this function in your public module

Public Function FN_FORM_ISLOADED(iFormName As String) As Boolean
    Dim I As Integer

    FN_FORM_ISLOADED = False
    For I = 0 To Forms.count - 1
        If Forms(I).name = iFormName Then
            FN_FORM_ISLOADED = True
            Exit Function
        End If
    Next I
End Function

Upvotes: 0

Mathieu Guindon
Mathieu Guindon

Reputation: 71247

Since the error is occurring on Forms(variableName).Controls, the instruction is doing too many things to be easily debuggable. Split it up.

Declare a variable to contain a Form object - note, I'm no Access dev, I usually work with Excel and MSForms, so for me that would be a UserForm, but I very strongly suspect that your Access forms aren't from the MSForms type library (you'd have to work pretty hard to get a UserForm in Access), so I'm guessing the type to use is called Form, but I could be wrong - will be happy to edit if corrected in a comment.

Dim theForm As Form

Now Set the form object:

Set theForm = Forms(variableName)

If your code didn't blow up yet, then you've successfully retrieved a form instance. That wouldn't surprise me, because if that step failed you'd probably be facing a subscript out of range error instead.

If your library has a Controls collection class, declare a variable of that type:

Dim theControls As Controls

And assign it:

Set theControls = theForm.Controls

That could possibly blow up with run-time error 438, if Form doesn't have a Controls member... which wouldn't really add up, given Me.Controls seems to work.

So go back and declare theForm As Object, and let VBA's late-binding magic query the object's interface, instead of working with the specific Form interface - again I don't know much Access, but it's fairly possible that Me being a specific form type exposes a Controls collection, but not the general-purpose Form type - VBA UI framework internals are that kind of a mess.

So, to speak COM-gibberish, by declaring it as Object you allow the run-time to query IDispatch and locate the Controls member.

If you can get that instruction (the Controls collection assignment) to run without throwing, then you should be able to iterate its content without problems.

Also keep in mind that it seems the Forms collection only includes open forms.

Upvotes: 0

Gustav
Gustav

Reputation: 56026

You need to Dim ctrl as well, and you may have spaces in the form name:

Dim ctrl As Control
Dim variableName as String

variableName = "[" & Me.cmboFormName & "]"

For Each ctrl In Forms(variableName).Controls
    Debug.Print ctrl.Name
Next

Upvotes: 2

pokemon_Man
pokemon_Man

Reputation: 902

Try this:

For Each ctrl In UserForm1.Controls 'or use your form name.
     Debug.Print ctrl.Name
Next ctrl

Upvotes: 0

Related Questions