Yes, this is a real issue, though it's actually your bug and not
Microsoft's.
When you start Access, the dbEngine object is opened, along with the default
workspace and database. Your code is not creating Workspaces(0) - you are
merely pointing your variable to the default workspace that is already open.
Since you did not open it, you should not close it. If you do close it,
Access obeys your instructions and closes the workspace. It gives no error:
in fact Microsoft is too kind because they merely create the default
workspace again (along with the default database that depends on it), and
things keep running.
However, the RecordsetClone of a form relies on *another* database -
typically Databases(1). This one does not get automatically recreated. So a
side effect of closing the default workspace is that the database
connections being used by the RecordsetClone of your forms is terminated and
not automatically recreated for you. But when you open a new form (or close
and reopen it), the clone set becomes available as normal.
So in short, the form's RecordsetClone was dependent on Workspaces(0), which
you closed. And the moral of the story is: close what you open, but *only*
what you open.
This issue is trap #3 in this article about transactions:
Archive: Move Records to Another Table
at:
http://allenbrowne.com/ser-37.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"paii, Ron" <pa**@packairinc.comwrote in message
news:DL******************************@athenet.net. ..
Over the years I had a problem where me.Bookmark in an open form was
undefined until I closed and reopened the form. The problem was caused by
an
old dialog form that set an object variable to the current database then
on
form close, used the Close method on that variable.
The following code was used during form open
Set WS = DBEngine.Workspaces(0)
Set DB1 = WS.Databases(0)
Was used at form close
DB1.Close ' This kills me.Bookmark for any open form
WS.Close
Should have been
Set DB1 = Nothing
Set WS = Nothing
This show the affect of closing something I did not open.
