Results 1 to 11 of 11
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Forms, Query, Reports and Macros collection (Access XP)

    I have a need to delete the above based upon a certain criteria. Can I just read thru a forms collection and delete the form if it's name doesn't start with frm. I need to do the same kind of thing with Queries, Reports and Macros.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Forms, Query, Reports and Macros collection (Access XP)

    Yes, you can, using using DAO and the Forms collection of the database object, not the application's forms collection. The macros are contained in the database's scripts collection.
    Charlotte

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Forms, Query, Reports and Macros collection (Access XP)

    Thanks Charlotte, by the time I got your response I had figured out how to do the forms, queries and reports (at least I think I have).
    What is the forms collection of the dtabase object? Will the following suffice?
    <pre> ' Delete the queries which don't start with "qry" or "z"
    For Each qdf In .QueryDefs
    If Left(qdf.NAME, 3) <> "qry" And Left(qdf.NAME, 1) <> "z" Then
    DoCmd.DeleteObject acQuery, qdf.NAME
    End If
    Next qdf
    ' Delete the forms which don't start with "frm"
    For Each frm In Forms
    If Left(qdf.NAME, 3) <> "qry" Then
    DoCmd.DeleteObject acForm, qdf.NAME
    End If
    Next frm
    ' Delete the reports which don't start with "frm"
    For Each rpt In Reports
    If Left(rpt.NAME, 3) <> "rpt" Then
    DoCmd.DeleteObject acForm, rpt.NAME
    End If
    Next rpt
    </pre>

    I haven't tested the above yet.

    Also where do I find out how to address the database's scripts collection for macros, would you give me a simple example please?

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Forms, Query, Reports and Macros collection (Access XP)

    Here's a routine extracted from something else that will allow you to delete a specified object of whatever type:

    <pre>Public Function DeleteObject(strContainerName As String, _
    intContainerType As Integer, _
    strObjName As String)
    On Error Resume Next

    Dim dbs As DAO.Database, ctr As DAO.Container
    Dim obj As Object

    Set dbs = CurrentDb
    Set ctr = dbs.Containers(strContainerName)
    Set obj = ctr.Documents(strObjName)
    If Err = 0 Then
    DoCmd.DeleteObject intContainerType, strObjName
    End If

    Set obj = Nothing
    Set ctr = Nothing
    Set dbs = Nothing

    End Function</pre>


    You would call this like this:

    deleteobject "scripts", acmacro, "Macro1"
    Charlotte

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Forms, Query, Reports and Macros collection (Access XP)

    That's great thank you Charlotte, but how do you see what macros you have by way of a loop?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Forms, Query, Reports and Macros collection (Access XP)

    For code like that you have for querydefs, forms etc.:

    Dim dbs As DAO.Database
    Dim ctr As DAO.Container
    Dim doc As DAO.Document

    Set dbs = CurrentDb
    ' Reference to the Scripts (=macros) container
    Set ctr = dbs.Containers!Scripts
    ' Loop through the documents (=individual macros) in the container
    For Each doc In ctr.Documents
    If Left(doc.Name, 3) <> "mcr" Then
    DoCmd.DeleteObject acMacro, doc.Name
    End If
    Next doc

    Set doc = Nothing
    Set ctr = Nothing
    Set dbs = Nothing

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Forms, Query, Reports and Macros collection (Access XP)

    Thanks Hans

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Forms, Query, Reports and Macros collection (Access XP)

    What do you mean by the Forms collection of the database object?

    How do you code a loop to get all the forms in a database? I have a need to delete all forms which don't start with "frm" or "z".
    I need to do the same thing with reports.

    Obviously the following does not work.

    ' Delete the forms which don't start with "frm"
    For Each frm In Forms
    If Left(frm.NAME, 3) <> "frm" Then
    DoCmd.DeleteObject acForm, frm.NAME
    End If
    Next frm

    ' Delete the reports which don't start with "rpt"
    For Each rpt In Reports
    If Left(rpt.NAME, 3) <> "rpt" Then
    DoCmd.DeleteObject acReport, rpt.NAME
    End If
    Next rpt

  9. #9
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Forms, Query, Reports and Macros collection (Access XP)

    As noted, to do this you have to use the DAO Container objects & Documents collections, which are not the same as the Forms and Reports collections, which are collections of all OPEN forms or reports, respectively. Sample code:

    Public Sub DeleteObjectsDAO()
    On Error GoTo Err_Handler

    Dim db As DAO.Database
    Dim doc As DAO.Document
    Dim strMsg As String

    Set db = CurrentDb
    For Each doc In db.Containers("Forms").Documents
    If Left$(doc.Name, 3) <> "frm" Then
    DoCmd.DeleteObject acForm, doc.Name
    End If
    Next doc

    For Each doc In db.Containers("Reports").Documents
    If Left$(doc.Name, 3) <> "rpt" Then
    DoCmd.DeleteObject acReport, doc.Name
    End If
    Next doc

    Exit_Sub:
    Set db = Nothing
    Set doc = Nothing
    Exit Sub
    Err_Handler:
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    Beep
    MsgBox strMsg, vbExclamation, "ERROR MESSAGE"
    Resume Exit_Sub
    End Sub

    HTH

  10. #10
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Forms, Query, Reports and Macros collection (Access XP)

    The other option is to use the CurrentProject AllForms & AllReports collections introduced in Access 2000. If using these collections you have to do a little more work, as the For Each... Next approach does not seem to work w/o drastic errors. Instead loop thru the collection from last item to first and delete the rejects. Sample code:

    Public Sub DeleteObjects()

    Dim strName As String
    Dim n As Long

    If CurrentProject.AllForms.Count > 0 Then
    For n = CurrentProject.AllForms.Count - 1 To 0 Step -1
    strName = CurrentProject.AllForms.Item(n).Name
    If Left$(strName, 3) <> "frm" Then
    DoCmd.DeleteObject acForm, strName
    End If
    Next n
    End If

    If CurrentProject.AllReports.Count > 0 Then
    For n = CurrentProject.AllReports.Count - 1 To 0 Step -1
    strName = CurrentProject.AllReports.Item(n).Name
    If Left$(strName, 3) <> "rpt" Then
    DoCmd.DeleteObject acReport, strName
    End If
    Next n
    End If

    End Sub

    HTH

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Forms, Query, Reports and Macros collection (Access XP)

    Thanks Mark, I have not worked with this before, hence my question on what it was.

    I had figured it out after struggling thru the help, I should get an oscar for finding this stuff in the help.

    Again, thanks for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •