Runtime error 2008 -you cannot delete the database object while its open

[Snowflake68]

I have the following code that deletes a subform (frmItemSelectionSubForm) and copies a template (DEFAULTfrmItemSelectionSubForm) and then renames it to the original subform name.

DoCmd.Close acForm, "frmItemSelection", acSaveYes
DoCmd.DeleteObject acForm, "frmItemSelectionSubForm"
DoCmd.CopyObject , "frmItemSelectionSubForm", acForm, "DEFAULTfrmItemSelectionSubForm"
DoCmd.OpenForm "frmItemSelection"

The procedure is called from the main form (frmItemSelection) (that contains the subform frmItemSelectionSubForm) via a button named 'RESET'. My code first closes the main form but then I get an error saying that it cannot delete the database object while it is open. But the main form and thus the subform is actually closed.


Why does Access think the form is still open when its not? Why do I get this error saying its open and is there away to get around it?

Its really difficult to explain why i want to delete and copy a form but basically its because I want to reset the subform and unhide all the columns that the user had chosen to hide. The form has over 50 columns so its easier to have a reset button to do this for them. The template has all of the columns unhidden so I thought that if i close the main form then recreate the subform that when the main form opens it will display all of the columns again.

I do have some code that unhides all columns in the subform which does work but not if the user hides/unhides columns and then navigates away to another form (the changes to the subform are saved) and when the user returns to the main form again and runs the code to unhide all columns on the subform from the main form does not unhide them because they have been previously saved as hidden in the subform.

I should also should point out that when the user navigates away from this main form it is done via a button called 'BACK' which runs a macro that closes the form (and saves it) then opens another form. I have to save the form when they navigate away because they go back and forth between several forms and it has to remember the columns they had visible previously until such time that they chose to reset them back to the default.

Really sorry for all the blabbering on I just dont know how to explain it any better.

Hope someone can help this damsel in distress.

 

[sneuberg]

I don't know if this would help but try closing the subform too. I guess you would do this before you close the main form like:

DoCmd.Close acForm, "frmItemSelectionSubForm", acSaveYes
DoCmd.Close acForm, "frmItemSelection", acSaveYes
DoCmd.DeleteObject acForm, "frmItemSelectionSubForm"
DoCmd.CopyObject , "frmItemSelectionSubForm", acForm, "DEFAULTfrmItemSelectionSubForm"
DoCmd.OpenForm "frmItemSelection"

 

[Snowflake68]

I don't know if this would help but try closing the subform too. I guess you would do this before you close the main form like:

DoCmd.Close acForm, "frmItemSelectionSubForm", acSaveYes
DoCmd.Close acForm, "frmItemSelection", acSaveYes
DoCmd.DeleteObject acForm, "frmItemSelectionSubForm"
DoCmd.CopyObject , "frmItemSelectionSubForm", acForm, "DEFAULTfrmItemSelectionSubForm"
DoCmd.OpenForm "frmItemSelection"

I did try your suggestion but it didnt work. I put the same code on another form and it definitely works so its got to be because Im calling it from the main form that has the subform. It appears to be closed but Access still thinks its open for some reason and I cant figure out how to get around it.

Really appreciate your reply, it goes a long way when someone tries to help, so thank you for your effects.

Caroline

 

[Snowflake68]

I did try your suggestion but it didnt work. I put the same code on another form and it definitely works so its got to be because Im calling it from the main form that has the subform. It appears to be closed but Access still thinks its open for some reason and I cant figure out how to get around it.

Really appreciate your reply, it goes a long way when someone tries to help, so thank you for your effects.

Caroline

The code all works when I run it from another form so I got to thinking that I could simply have the button on the main form call a procedure to click a button on a separate form that to run the code but guess what that even gives me the same error.

I have an old database that deletes forms and copies them from the same from it calls the code from so I cannot understand why this is not working in this database. Ive tried compacting it but nothing works. Im running out of ideas here

 

[JHB]

Try by creating a new database and copy all into it!
Else post a stripped version of your database with some sample data, (zip it) + how to reproduce the error.

 

[Snowflake68]

Try by creating a new database and copy all into it!
Else post a stripped version of your database with some sample data, (zip it) + how to reproduce the error.

ive tried your suggestion of importing everything into a new database but still have the same issue.

Ill sort out a small database containing only the necessary tables and forms and remove the sensative data as this is a customer of mine. Once Ive prepared the database ill upload it here. Would really appreciate someone looking at it for me.

Thanks

 

[CJ_London]

why not supply your user with a accde or mde front end - then he/she can hide columns to their hearts content but any changes would not be saved.

 

[Snowflake68]

Try by creating a new database and copy all into it!
Else post a stripped version of your database with some sample data, (zip it) + how to reproduce the error.

In the SAMPLE DB attached I have removed all of the sensitive data and some forms and some controls off some of the remaining forms but I have left the two main forms which are outlined below.

These forms are always open at the same time and form the top and bottom sections of the screen.

I have also left a macro called ‘OpenItemSelectionForms’ so if you run this it will open both of the forms for you.
The forms are as follows;

frmSearchItemSelection’ – this opens up in the top half of the screen and contains a subform named frmSearchItemSelectionSubForm in datasheet view

frmSearchItemSelectionSubForm – this opens up in the bottom half of the screen (it isn’t really a subform at all but for historic reasons that’s what it has been named) it contains an actual subform named ‘frmPreviewSelectionDatasheet’ and is also in Datasheet view

On the form ‘frmSearchItemSelection’ there is a button with a label ‘Reset Form 1’ (ill rename it to RESET once its working but its just a reminder to me that the code behind it is just set to the 1 form)

I need the code behind the reset button to do the following;
1. Close the form ‘frmSearchItemSelection’
2. then delete the subform frmSearchItemSelectionSubForm’
3. copy the form DEFAULTfrmSearchItemSelectionSubFormSHOWALL and name it frmSearchItemSelectionSubForm’
4. Then reopen the form frmSearchItemSelection’

Once I get his code to work then I have some more code which simply hides some fields but that is working so I have just commented it out for ease of looking at the code so you can just ignore that bit.

Just to add, there is also some more code that does exactly the same thing as steps 1 to 4 above but to the datasheet of the ‘frmSearchItemSelectionSubForm that opens up in the bottom section of the screen and this is working OK but again I have commented this out until the broken bit is working, Ive just left it there so that you can see what I am trying to achieve. Basically the RESET button needs to reset both datasheets back to display all columns.

To test the code you just need to use the SHOW/HIDE button to hide some of the columns and then click the RESET button which should then restore all of the columns but I am getting an error at point 2 of the above steps which produces an error saying that it cannot delete the object while it is open. The form is closed so I don’t know why I am getting the error.

Hope someone can help me please.

 

[Snowflake68]

why not supply your user with a accde or mde front end - then he/she can hide columns to their hearts content but any changes would not be saved.

Thanks for your reply however I do not know what a accde or mde front end is plus the user can already hide and unhide columns, the issue I have is resetting the forms back to a specified default which basically is to show all columns again (and then hide some that are defined). I have uploaded a sample db so maybe you could take a look for me if possible please.

Thanks again.

 

[JHB]

... The form is closed so I don’t know why I am getting the error.

Actually the form isn't closed yet, because the code behind the button "cmdResetForm" still runs.
You can test my statement by creating a new form with a button on it and copy the code into it, you'll see the code runs okay.
A solution to your problem is to set the sourceobject for the object frame, (frmSearchItemSelectionSubForm) = "" before deleting the form and after you have copied the form set it back to "frmSearchItemSelectionSubForm".
Never mind I think you have a wrong spelling in your code.

DoCmd.CopyObject , "[B][COLOR=Red]frmSearchSelectionSubForm[/COLOR][/B]", acForm, "DEFAULTfrmSearchItemSelectionSubFormSHOWALL"

I think it should be "frmSearchItemSelectionSubForm"

 

[Snowflake68]

Actually the form isn't closed yet, because the code behind the button "cmdResetForm" still runs.
You can test my statement by creating a new form with a button on it and copy the code into it, you'll see the code runs okay.
A solution to your problem is to set the sourceobject for the object frame, (frmSearchItemSelectionSubForm) = "" before deleting the form and after you have copied the form set it back to "frmSearchItemSelectionSubForm".
Never mind I think you have a wrong spelling in your code.

DoCmd.CopyObject , "[B][COLOR=Red]frmSearchSelectionSubForm[/COLOR][/B]", acForm, "DEFAULTfrmSearchItemSelectionSubFormSHOWALL"

I think it should be "frmSearchItemSelectionSubForm"

Hi thanks for your reply. I did spot that typo and thought id fixed it but obviously not doh! However its the line of code prior to this that is causing the runtime error 2008 where it cant delete the subform because it thinks it is still open. I also tested the code from a different form so I agree with you that the code does actually work

I think I need to go with your suggestion though about setting the source object to something else and then resetting it back but I dont know how to code this though, are you able to assist me with the code to do this please?

Thanks again
Caroline

 

[JHB]

The code is below:

DoCmd.SetWarnings False
''Reset Form SearchItemSelectionSubForm
Me.frmSearchItemSelectionSubForm.SourceObject = ""
DoCmd.DeleteObject acForm, "frmSearchItemSelectionSubForm"
DoCmd.CopyObject , "frmSearchItemSelectionSubForm", acForm, "DEFAULTfrmSearchItemSelectionSubFormSHOWALL"
Me.frmSearchItemSelectionSubForm.SourceObject = "frmSearchItemSelectionSubForm"
DoCmd.Close acForm, "frmSearchItemSelection", acSaveYes
DoCmd.OpenForm "frmSearchItemSelection"
DoCmd.SetWarnings True

 

[Snowflake68]

The code is below:

DoCmd.SetWarnings False
''Reset Form SearchItemSelectionSubForm
Me.frmSearchItemSelectionSubForm.SourceObject = ""
DoCmd.DeleteObject acForm, "frmSearchItemSelectionSubForm"
DoCmd.CopyObject , "frmSearchItemSelectionSubForm", acForm, "DEFAULTfrmSearchItemSelectionSubFormSHOWALL"
Me.frmSearchItemSelectionSubForm.SourceObject = "frmSearchItemSelectionSubForm"
DoCmd.Close acForm, "frmSearchItemSelection", acSaveYes
DoCmd.OpenForm "frmSearchItemSelection"
DoCmd.SetWarnings True

You are an absolute diamond and I am so grateful for your help. It appears to be working perfectly.

I need to give it a thorough testing though but on the face of it all is working.

 

[JHB]

You are an absolute diamond and I am so grateful for your help. It appears to be working perfectly.
..

Good luck.