Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

Thread Tools Rate Thread Display Modes
Old 09-18-2002, 03:46 PM   #1
Registered User
Join Date: Jul 2001
Location: Everett,WA
Posts: 336
Thanks: 0
Thanked 1 Time in 1 Post
DataMiner is an unknown quantity at this point
Docmd.DeleteObject problem in For-Next loop

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-----------------------------

She Through Whom All Data Flows
DataMiner is offline   Reply With Quote
Old 09-18-2002, 08:26 PM   #2
Pat Hartman
Super Moderator
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,818
Thanks: 13
Thanked 1,510 Times in 1,436 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
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.
Pat Hartman is offline   Reply With Quote
Old 09-18-2002, 10:30 PM   #3
Newly Registered User
Join Date: Jun 2002
Posts: 1,186
Thanks: 0
Thanked 0 Times in 0 Posts
pono1 will become famous soon enough
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.


pono1 is offline   Reply With Quote

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

All times are GMT -8. The time now is 12:54 AM.

Microsoft Access Help
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post

Sponsored Links

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World