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

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-18-2002, 03:46 PM   #1
DataMiner
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

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

__________________
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,431
Thanks: 13
Thanked 1,419 Times in 1,353 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
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
pono1
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.

Regards,
Tim

pono1 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 05:48 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


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