VBA to list all open objects in MS Access

Question

I'm wondering if it is possible for Access VBA to give a listing of all the open Document Tabs in the current instance of Access. I have code that can check every Access object and report if it is loaded. It's fast but feels like a backwards approach. It also is unable to list unsaved objects, like a new query. Also, I can't tell which objects are showing a tab. A popup window does not have a tab.

Is there a VBA routine that can list all of the Tabbed Documents?

Public Sub ListAllOpenObjects()
' This will list all open Access objects.  It will not list
' objects that are new that have not be saved, like Query1.

    Dim aob As AccessObject

    With CurrentData
        ' "Tables"
        For Each aob In .AllTables
            If aob.IsLoaded Then
                Debug.Print aob.name
            End If
        Next aob

        ' "Queries"
        For Each aob In .AllQueries
            If aob.IsLoaded Then
                Debug.Print aob.name
            End If
        Next aob
    End With

    With CurrentProject
        ' "Forms"
        For Each aob In .AllForms
            If aob.IsLoaded Then
                Debug.Print aob.name
            End If
        Next aob

        ' "Reports"
        For Each aob In .AllReports
            If aob.IsLoaded Then
                Debug.Print aob.name
            End If
        Next aob

        ' "Pages"
        For Each aob In .AllDataAccessPages
            If aob.IsLoaded Then
                Debug.Print aob.name
            End If
        Next aob

        ' "Macros"
        For Each aob In .AllMacros
            If aob.IsLoaded Then
                Debug.Print aob.name
            End If
        Next aob

        ' "Modules"
        For Each aob In .AllModules
            If aob.IsLoaded Then
                Debug.Print aob.name
            End If
        Next aob

    End With

End Sub

ComputerVersteher · Answer 1 · 2019-07-26T00:14:29.993

2

There is a shortcut for forms and reports as Application.Forms contains all opened forms and Application.Reports contains the opened reports (no matter if they are popups or not visible).

As users shouldn't be able to open tables or queries, this is sufficent in production.

If you need that information during development, you have to loop the corresponding collections and check each forIsLoaded.

As alternative, you should be able to detect open tabs by their handles, but that needs extensive use (and knowledge) of WinAPI functions likeFindWindowandGetClassName. This can be used to improve your WinAPI skills, but is not more elegant than looping the collections.

edited Jul 26 '19 at 00:14

answered Jul 25 '19 at 23:05

ComputerVersteher

2,638
1
10
20

I built a developer add-in and I wanted a way to track all of the open tabs. It is an enhancement to [this](https://stackoverflow.com/a/53125040/1898524). It seems like there is not a way to do what I want (list tabs of unsaved objects) without a WinAPI. – Ben Aug 28 '19 at 16:06
[Application.SysCmd](https://learn.microsoft.com/en-us/office/vba/api/access.application.syscmd) provides object state new, maybe an option? – ComputerVersteher Aug 28 '19 at 16:18
Add info and link to add-in to question! In https://stackoverflow.com/questions/57103395/how-to-get-accurate-lastupdated-date-time-from-objects-in-access too! – ComputerVersteher Aug 28 '19 at 16:20
I don't mind sharing my add-ins but I don't have a website where I host them. If you want me to PM you, put your contact info in your profile. – Ben Aug 29 '19 at 18:49
My comment should make you add the comments information to the question. Same with https://stackoverflow.com/questions/57103395/how-to-get-accurate-lastupdated-date-time-from-objects-in-access. Of course sharung code is always welcome (create a github gist?), but I found your db just 2 weeks ago, when explaining that issue [here](http://office-loesung.de/p/viewtopic.php?f=167&t=757853) (german). – ComputerVersteher Sep 04 '19 at 17:38
Did you try Application.SysCmd to find unsaverd objrects? – ComputerVersteher Sep 04 '19 at 17:40
**Application.SysCmd** does have a way to tell you if an object is Open, Dirty, New, but you need to pass it the object name. If I start to design a new query, this statement will return True: `SysCmd(acSysCmdGetObjectState, acQuery, "Query1") = acObjStateOpen + acObjStateDirty + acObjStateNew`. Unfortunately I don't know how to programmatically feed it the name of an unsaved object. It's not listed in `CurrentDb.QueryDefs` or `Application.CurrentData.AllQueries` until it is saved. Sample code/db [here](http://azlihassan.com/apps/articles/microsoft-access/vba/access-vba-syscmd) – Ben Sep 05 '19 at 18:06
Are there other possibilities than`ObjectnameCounter`(e.g. Query2) or existing ObjectnameCounter` for Forms/Reports created by Wizard/Form-tool, depending on the selected object in nav-pane? Ok Objectnames are language dependend and if you have more than 100 Tables/Queries the iteration would be huge and not beatiful;). Maybe I have some time the next months to gain some API knowledge as I am seeking for Suibforms-Scrollbar (NuiScrollbar) handle to read its position, for repositioning on requery (user never clicked on subform, just moved the bar, what prevents usual repositioning code). – ComputerVersteher Sep 11 '19 at 23:50

VBA to list all open objects in MS Access

1 Answers1