CODEWITHSUNDEEP
vbaformsloopsms-access
junaid bashir
junaid bashir

Reputation: 17

how to loop through selected list of forms in Ms Access

I am trying to loop through a selected list of forms in Ms Access. Like we can do it with sheets in Excel in this way

For Each shtSheet In ThisWorkbook.Worksheets(Array("Sheet2", "Sheet1"))

Is there a way to do some similar thing in Access for forms?

Thanks in advance.

Upvotes: 0

Views: 577

Answers (3)

Gustav
Gustav

Reputation: 55806

You can list them as objects:

Public Function ListSomeForms()

    Dim Form        As Object
    Dim FormNames   As Variant
    Dim Item        As Variant
    
    FormNames = Array("FormName1", "FormName2")
    
    For Each Item In FormNames    
        Set Form = CurrentProject.AllForms(Item)
        Debug.Print Form.Name, Form.IsLoaded

        ' Loop controls.
        DoCmd.OpenForm Form.Name, acDesign
        ' For Each Control In Forms(Form.Name) 
        '     Do stuff ...
        ' Next
        DoCmd.Close acForm, Form.Name

    Next

    Set Form = Nothing
    
End Function

Upvotes: 1

junaid bashir
junaid bashir

Reputation: 17

@storax @gustav Both of you guys gave just the perfect solution I asked for.

As for me, I ended up putting form names in a table and then calling those through recordsets.

Set db = CurrentDb
Set rs = db.OpenRecordset("tsubforms")

Do Until rs.EOF
Set frm = rs!SubForm

With frm
    DoCmd.OpenForm frm, acViewDesign, , , , acHidden
    For Each ctrl In Forms(frm).Controls
            Debug.Print ctrl.Name
End With
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set db = Nothing

just putting my solution so if it could help out anyone else.

I would appreciate if somebody finds any of my mistakes in my code or can let me know of something better than this.

Upvotes: 0

Storax
Storax

Reputation: 12167

For example

Dim f As Form
Dim arr
arr = Array("Form1", "Form2")
Dim element

For Each element In arr
    Set f = Forms(element)
Next

But set f= Forms(element) will fail if the form is not loaded.

Upvotes: 1

Related Questions