Docmd.DeleteObject problem in For-Next loop (1 Viewer)

DataMiner

Registered User.
Local time
Today, 19:51
Joined
Jul 26, 2001
Messages
336
Hi,
I am just changing over from Access 97 to 2002, and have run into the following problem. For several years I've used the following code to automatically delete "temporary forms". It no longer works.
I get a variety of error messages, usually:
"Runtime error 29086: Microsoft Access cannot complete the operation. You must stop the code and try again". If I go into debug mode and try again, I get:
"Runtime Error 7874, Microsoft Access can't find the object." ... yeah, because I just deleted it, and for some reason it's failed to advance to to the next form object.

If I take out the docmd.deleteobject, and just debug.print the form names instead, it works fine. So it seems like somehow the deleteobject method is reordering the forms in a way that confuses the for-next loop.

This only seems to be a problem with forms. Works fine with tables and queries.

Any ideas?

'start code-------------------------------
Sub deleteforms97()
Dim F As Document, DB As Database, D As Date
D = Date
Set DB = CurrentDb
For Each F In DB.Containers!Forms.Documents
DB.Containers!Forms.Documents.Refresh '''

If InStr(1, F.Name, "_TEMP_") <> 0 And F.DateCreated < D Then
varreturn = SysCmd(acSysCmdSetStatus, "Deleting form " & F.Name)

DoCmd.DeleteObject acForm, F.Name
Debug.Print F.Name
End If
Next F
End Sub
'end code----------------------------

I also tried the following, but get the same behavior.
'Start code------------------
Sub deleteforms()
Dim obj As AccessObject, dbs As Object, D As Date
D = Date
Set dbs = Application.CurrentProject
' Search for open AccessObject objects in AllForms collection.
For Each obj In dbs.AllForms

If InStr(1, obj.Name, "_TEMP_") <> 0 And obj.DateCreated < D Then
'varreturn = SysCmd(acSysCmdSetStatus, "Deleting form " & obj.Name)
Debug.Print obj.Name
DoCmd.DeleteObject acForm, obj.Name


End If
Next obj

End Sub
'end code-----------------------------
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:51
Joined
Feb 19, 2002
Messages
43,231
The problem is that deleting an object messes up the index used for looping. Try changing the loop to be based on the number of items in the collection and run it backwards. So rather than indexing 1,2,3,etc do it max, max-1, max, -2, etc.
 

pono1

Registered User.
Local time
Today, 11:51
Joined
Jun 23, 2002
Messages
1,186
Or you can use your For Each loop to fill an array with the names of the forms you want to axe and then run a For Next Loop to execute the deed.

Regards,
Tim
 

Users who are viewing this thread

Top Bottom