[AccessD] Delete all reports and all forms

Max Wanadoo max.wanadoo at gmail.com
Thu Mar 18 14:38:57 CDT 2010


Hi AD,

1. There is no error either with or without closing forms with the
docmd.closeform while enumerating the .allForms collection.
2. The  code is called from a class function not from a form but that is a
check I should have to build in just in case it needs to be called from a
form.  For now this is not the source of the problem.
3. The forms I am using are anyformfromanywhere. I just import any old forms
from any old mdb and run the code against them.  There are no forms that I
wish to retain. The code should just delete them all (which it does) but
without the  errors.

I am creating a library (class) and there are no forms, tables, etc in
there, just class code and one module to test them with plus whatever  I
drag in to do the testing.

I am using Access 2003 SP3 (11.8166.8221)
The vb editor shows Microsoft Visual Basic 6.5 Version 1024.

Thanks for the interest.

Max

Ps. 29068 = "Microsoft Office Access cannot complete this operation. You
must stop the code and try again."




 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D. Tejpal
Sent: Thursday, March 18, 2010 6:36 PM
To: Access Developers discussion and problem solving
Subject: Re: [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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list