MS Access objects (forms, reports) display created or modified date

Question

Referencing this question: MS Access 2013 objects (tables, queries) display created or modified date Matt answered with this code: https://stackoverflow.com/a/36712839/6342925

Which worked the first time. How do you add to the code to also list forms, reports, and macros - and display created and modified date?

score 3 · Answer 1 · answered May 17 '16 at 05:17

3

Use the CurrentProject.All* collections: AllForms; AllReports; and AllMacros.

Here is an example in the Immediate window:

for each obj in CurrentProject.AllForms : ? obj.Name, obj.DateCreated, obj.DateModified : next
frmClaims     8/11/2015 12:11:46 PM       10/6/2015 1:08:28 PM 
frmInvoices   3/8/2015 5:04:24 PM         10/6/2015 1:08:28 PM 
frmChoosePerson             3/28/2015 1:11:51 PM        2/28/2016 8:29:28 PM 

for each obj in CurrentProject.AllReports : ? obj.Name, obj.DateCreated, obj.DateModified : next
rptPassThruQuery            3/9/2016 1:04:38 PM         3/9/2016 2:23:38 PM 
rptChangeSort 7/27/2015 10:45:38 AM       10/6/2015 1:08:30 PM 

for each obj in CurrentProject.AllMacros : ? obj.Name, obj.DateCreated, obj.DateModified : next
Macro2        6/17/2015 10:21:58 AM       6/17/2015 10:21:58 AM

answered May 17 '16 at 05:17

HansUp

95,961
11
77
135

Thanks Hans, I got it to work. As a newbie to VBA, I don't understand the theory or difference between how Access treats tables and queries vs. forms and reports. Alas, your code helped! – Mike Morrison May 17 '16 at 14:26
Yeah, there is not one "route" to get at all the different database object types. That can be confusing and frustrating. Once you know suitable routes for each of the types ... you're off to a great start. :-) – HansUp May 17 '16 at 14:32
I've added the entire VBA code below - to remember how to do this again! – Mike Morrison May 17 '16 at 14:34

score 2 · Accepted Answer · edited May 17 '16 at 17:46

Public Function CreatedModified()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim tdf As DAO.TableDef
    Dim qdf As DAO.QueryDef
    Dim strSql As String
    Dim obj As AccessObject

    strSql = "DELETE * FROM tblCCSObjects"

    Set db = CurrentDb

    db.Execute strSql, dbFailOnError

    Set rs = db.OpenRecordset("tblCCSObjects")

    With rs

        ' tables
        For Each tdf In db.TableDefs
            If Not (tdf.Name Like "*MSys*" Or tdf.Name Like "~*") Then
                .AddNew
                !ObjectType_Order = "1"
                !ObjectType = "Table"
                !ObjectName = tdf.Name
                !DateCreated = tdf.DateCreated
                !DateModified = tdf.LastUpdated
                .Update
            End If
        Next

        ' queries
        For Each qdf In db.QueryDefs
            If Not (qdf.Name Like "*MSys*" Or qdf.Name Like "~*") Then
                .AddNew
                !ObjectType_Order = "2"
                !ObjectType = "Query"
                !ObjectName = qdf.Name
                !DateCreated = qdf.DateCreated
                !DateModified = qdf.LastUpdated
                .Update
            End If
        Next

        ' forms
        For Each obj In CurrentProject.AllForms
                .AddNew
                !ObjectType_Order = "3"
                !ObjectType = "Forms"
                !ObjectName = obj.Name
                !DateCreated = obj.DateCreated
                !DateModified = obj.DateModified
                .Update
        Next

        ' reports
        For Each obj In CurrentProject.AllReports
                .AddNew
                !ObjectType_Order = "4"
                !ObjectType = "Reports"
                !ObjectName = obj.Name
                !DateCreated = obj.DateCreated
                !DateModified = obj.DateModified
                .Update
        Next

      ' macros
        For Each obj In CurrentProject.AllMacros
                .AddNew
                !ObjectType_Order = "5"
                !ObjectType = "Macros"
                !ObjectName = obj.Name
                !DateCreated = obj.DateCreated
                !DateModified = obj.DateModified
                .Update
        Next
        End With

    rs.Close
    Set rs = Nothing
    Set db = Nothing

End Function

Hans, thanks again for your guidance. I did modify the code, getting rid of the If.. then statements for forms, reports, and macros, as you suggested. All still works! In the meantime, I changed the name of the table, and added a "Object Type sort order" field, so I could sort non-alphanumeric. Above is the cleaned up code. — Mike Morrison, May 17 '16 at 17:41
Disco. Again, thanks for your help. I suppose the Dim obj As AccessObject and dbFailOnError are normal protocol type stuff. I meant to add above that I had to change from a subroutine to a function to allow a "RunCode" to work in a macro. — Mike Morrison, May 17 '16 at 21:29

MS Access objects (forms, reports) display created or modified date

2 Answers2