[AccessD] Delete all reports and all forms

Max,

    While trying to close the open forms, prior to deletion, it would be desirable to do so via the Forms collection and not AllForms collection. This way your code would not be attempting to close already closed forms, or try to address any subform as if it were a stand-alone form. Forms collection reflects only the open forms, ignoring subforms.

    In case you wish to use a test form for calling the function for deleting all forms, the calling form has to be excluded from its scope. The function duly modified on these lines is given below. In this version, the array of form names is getting populated only from second element onwards. This facilitates a cross-check whether it holds any valid form name for deletion, thus eliminating the risk of attempting to perform deletion based upon possible Null value in any element.

    If called from a form, sample statement would be:
    libDeleteLocalForms Me.Name

    Note: The argument is optional and can be omitted if the function is executed directly within VBA window.

    You might like to test it out. The function should be able to run smoothly without any error handling code. Eventually, you could add the error handler, or simply insert On Error Resume Next at start.

    Tests at my end (Access 2003 on Win XP) did not attract any error. If you still happen to encounter any problem, you could consider sending me a zipped copy of skeleton sample file featuring the problem.

Best wishes,
A.D. Tejpal
------------

' Sample function
'================================
Public Function libDeleteLocalForms( _
            Optional CallingFormName As String = "")
    Dim obj As Object, fm As Access.Form
    Dim Cnt As Long, Rtv As Variant
    
    For Each fm In Application.Forms
        If fm.Name <> CallingFormName Then
            DoCmd.Close acForm, fm.Name
        End If
    Next
    
    ' Populate the zero based array from
    ' second element onwards.
    ReDim Rtv(0)
    Cnt = 0
    For Each obj In CurrentProject.AllForms
        If obj.Name <> CallingFormName Then
            Cnt = Cnt + 1
            ReDim Preserve Rtv(Cnt)
            Rtv(Cnt) = obj.Name
        End If
    Next
    
    ' Delete forms if array has grown beyond
    ' first element
    If UBound(Rtv) > 0 Then
        For Cnt = 1 To UBound(Rtv)
            DoCmd.DeleteObject acForm, Rtv(Cnt)
        Next
    End If
    
    Set obj = Nothing
    Set fm = Nothing
End Function
'=================================

  ----- Original Message ----- 
  From: Max Wanadoo 
  To: 'Access Developers discussion and problem solving' 
  Sent: Thursday, March 18, 2010 21:51
  Subject: Re: [AccessD] Delete all reports and all forms


  AD,

  There were some problems with the code so I am posting back what I ended up
  with.
  Once again, for me, Access did not like deleting the Forms, so I just had to
  jump around the error (as shown). Also a test for zero Forms.

  Any thoughts on the error?

  Max

  Public Function libDeleteLocalForms()
    On Error GoTo EH
    Dim obj As Object, Rtv As Variant, Cnt As Long
    If CurrentProject.AllForms.Count > 0 Then
      ReDim Rtv(CurrentProject.AllForms.Count - 1)
      Cnt = 0
      For Each obj In CurrentProject.AllForms
        DoCmd.Close acForm, obj.Name
        Rtv(Cnt) = obj.Name
        Cnt = Cnt + 1
      Next
      For Cnt = 0 To UBound(Rtv)
        DoCmd.DeleteObject acForm, Rtv(Cnt)
      Next
    End If
  EX:
    Set obj = Nothing
    Exit Function
  EH:
    Select Case Err.Number
    Case 29068
      Resume Next
    Case Else
      MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
      Resume EX
    End Select
  End Function