Looping thru all forms (1 Viewer)

abenitez77

Registered User.
Local time
Today, 06:27
Joined
Apr 29, 2010
Messages
141
I am looping thru all forms...and i have more than 1,000. looking for the forms that start with the prefix "DYN_" & MyReportListID. Then when I find one, I am copying those forms and resetting properties on them. Looping thru all forms will take a little less than a minute. Is there a way to put the forms I need into an array and loop thru the array?

Code:
    odelfrm = "DYN_" & MyReportListID
    lenfrm = Len(modelfrm)
    frmCnt = CurrentProject.AllForms.count
    With CurrentProject
        For Each aob In .AllForms
            nr = nr + 1
            If UCase(Mid(aob.Name, 1, lenfrm)) = "DYN_" & MyReportListID Then
	       ' Do something here.....
           Endif		
	Next aob
    
    End With
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:27
Joined
May 7, 2009
Messages
19,169
you can
Code:
    Dim aob As AccessObject
    Dim a(1 To 2) As String
    Dim i As Integer
    
    ' name of forms
    a(1) = "form1"
    a(2) = "form2"
    
    For i = 1 To UBound(a)
        Set aob = CurrentProject.AllForms(a(i))
        ' do something here
    Next
    Erase a
 

plog

Banishment Pending
Local time
Today, 05:27
Joined
May 11, 2011
Messages
11,611
Even better than a custom list you have to maintain--all your forms are already in a table:

https://www.devhut.net/2010/06/12/ms-access-listing-of-database-objects/

Paste this into a Query object and run it:

Code:
SELECT *
FROM MSysobjects
WHERE (((MSysobjects.Type)=-32768));

Then, look at it in Design View and add whatever criteria you need to it (e.g. [Name] Like "DYN_*"), save it adn then load it into a Recordset in your VBA and you can loop through it.

If you ever add a new form, it will be in that table and you won't have to change any code.
 

abenitez77

Registered User.
Local time
Today, 06:27
Joined
Apr 29, 2010
Messages
141
Even better than a custom list you have to maintain--all your forms are already in a table:

https://www.devhut.net/2010/06/12/ms-access-listing-of-database-objects/

Paste this into a Query object and run it:

Code:
SELECT *
FROM MSysobjects
WHERE (((MSysobjects.Type)=-32768));

Then, look at it in Design View and add whatever criteria you need to it (e.g. [Name] Like "DYN_*"), save it adn then load it into a Recordset in your VBA and you can loop through it.

If you ever add a new form, it will be in that table and you won't have to change any code.

I was using system tables first, but I heard it wasn't good to use them because it is not always reliable...if i delete a form or create one , it doesn't always update the system tables right away? is that true?
 

plog

Banishment Pending
Local time
Today, 05:27
Joined
May 11, 2011
Messages
11,611
I just ran a test--copied a form, ran the query and it was there. Deleted a form, ran the query and it wasn't there.

Thinking over your initial post more, I have a question---Why? What's the big picture on all this? Setting properties on one form seems possible, but to have so many that require it smells of poor design. Then there's copying going on as well? It just seems inefficeint

Why do you have so many similarly named forms? How similar are they? Do they all capture the same data? What differentiates them from another? Why not just one form and set the properties as needed?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:27
Joined
Feb 19, 2002
Messages
42,970
The reason for not using the MSys tables is because MS says NOT to. The tables are for MS internal use and are subject to change from one version of Access to another. That is why they are undocumented. Use the MSys tables at your own risk. I happen to speak from experience on this one. A long time ago, I built a utility based on the MSys tables and in the next version of Access, the table I was using disappeared.

That said, I do use the MSysObjects table for some things. That seems to be safe but who knows.
 

Users who are viewing this thread

Top Bottom