I'm not sure how to adequately explain my problem in two sentences or less, so at the risk of providing TMI, here's the condensed verion.
I have developed an Access 2002 database file that contains a form, multiple queries and multiple reports. The purpose of the form is to allow the user to run various queries against my company's Sybase server and display a report.
Since the queries return ADO recordsets from the Sybase server, I've found it MUCH easier to write VB code that creates a table in Access based on the fields and data in the ADO recordset. I then use the table as the record source for my reports.
Each time a query is run, the old tables are deleted and then recreated with the new data from the Sybase server before the report is displayed.
I have many people in my company that like the using the form and reports in my database. But since the users of the database are constantly deleting and recreating tables, each user must have their own copy of the database file to avoid stepping on each other.
I continually get requests from users to modify and/or add new queries and reports. But since all of the users have their own copy of the database, distributing updated forms, queries and reports is a challenge.
I hit upon the idea of creating a table in the database that contains the names, types, and revision dates of all database objects (i.e. all queries, forms, reports, macros, modules, etc.). Then I placed a master copy of the database file on my companies network with the latest and greatest version of all database objects.
Finally, I added an "Update" button to the main form in the database. When the user clicks the button, a macro is launched. The macro runs a procedure that first closes the main form, then compares the revision date table in the master database file (on the company network) with the revision date table in the users local copy of the database.
If the revision date for a particular object is newer in the master database, the object is deleted from the user's local copy and the updated version of the object is copied in from the master database.
Make sense?
Everything work beautifully until today when I made a revision to the main form. Now when the "Update" button is clicked, the macro fires, the procedure is run, the main form is closed, but when the procedure tries to delete the main form object, Access gives me an error. The error says:
Run-time error '2008':
You can't delete the database object [main form name] while it's open.
What gives? My procudre has already closed the form and it is no longer visible on the screen. Why does Access still think it's open?
I figure the problem has something to do with the fact that the form I'm trying to delete is the same form that called the macro which called the procedure that's trying to delete it.
Any thoughts on how I can get the form to completely close so I can delete it?
Here's the code I use to close the main form (called "PROGMAN_frm")
Expand|Select|Wrap|Line Numbers
- If CurrentProject.AllForms![PROGMAN_frm].IsLoaded Then
- DoCmd.Close acForm, "PROGMAN_frm", acSaveNo
- End If
Scott