On db Exit

[alex]

On db Exit

Hello,

I have a form that is opened at startup, hidden, and is supposed to
check for idleness and close the db if the user does not respond (the
idle part is not working right, but that’s another post).

This form also has in its close event a call to a function that
deletes a specific group of tables.

When the user closes the db manually, everything works fine. The
reason, I think, is that all forms are closed before the hidden form
that deletes the tables (which must close last).

The problem is when the hidden form detects that the db has been idle
and tries to shut the db down. What I think is happening is that when
the code (application.quit) executes, it tries to close the hidden
form before the others since its index is probably 0 or 1.

This causes an error because the other forms are connected to the
tables that the hidden form is trying to delete (lock table error
3211).

I’m not sure how I should code my way around this problem…I thought
maybe by placing code before the event that deletes all the tables, I
could write something that sets all record sources to “”. That way
none of the tables will be connected to a form when their deleted.
Or, in the hidden idle form, closing all forms Before the idle form
which should sever all connections to any table. I cannot find any
code that sets all record sources to “” or will loop through a forms
collection deleting all forms except one.

What would be cool, is something that mimics the autoexec macro, but
on exit! Just saying…

Thoughts?
Thanks,
alex

 

[Tom van Stiphout]

On Thu, 14 Jan 2010 05:26:13 -0800 (PST), alex <[email protected]>
wrote:

Yes, loop over the forms collection and close all but the hidden ones
first. Be careful with your code: the innocent programmer might write
something like:
for i = 0 to forms.count - 1
if forms(i).name <> "myHiddenForm" then docmd.close acform,
forms(i).name
next
but that does not work because you're changing the size of the
collection in the loop.

-Tom.
Microsoft Access MVP

 

[alex]

Yes, loop over the forms collection and close all but the hidden ones
first. Be careful with your code: the innocent programmer might write
something like:
for i = 0 to forms.count - 1
  if forms(i).name <> "myHiddenForm" then docmd.close acform,
forms(i).name
next
but that does not work because you're changing the size of the
collection in the loop.

-Tom.
Microsoft Access MVP











- Show quoted text -

Tom,
Thanks for your response. My guess is to loop through the forms
collection and look for any isloaded form (which i can do) and then
delete all with a visible propery of true. It's the visible property
that i cannot figure out.

From the help file in Access:
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentProject

For Each obj In dbs.AllForms
If obj.IsLoaded = True Then 'write code to close any unhidden form
End If
Next obj

alex

 

[John W. Vinson]

Tom,
Thanks for your response. My guess is to loop through the forms
collection and look for any isloaded form (which i can do) and then
delete all with a visible propery of true. It's the visible property
that i cannot figure out.

From the help file in Access:
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentProject

For Each obj In dbs.AllForms
If obj.IsLoaded = True Then 'write code to close any unhidden form
End If
Next obj

The Forms collection consists of all the *open* forms already, so it's simpler
to use:

Dim db As DAO.Database
Dim frm As Form
Set db = CurrentDb
For Each frm In db.Forms
If frm.Visible Then
DoCmd.Close acForm, frm.Name
End If
Next frm

 

[alex]

The Forms collection consists of all the *open* forms already, so it's simpler
to use:

Dim db As DAO.Database
Dim frm As Form
Set db = CurrentDb
For Each frm In db.Forms
   If frm.Visible Then
      DoCmd.Close acForm, frm.Name
   End If
Next frm

Thanks John for the response...I keep getting a compile error,
however, on .Forms. It's saying that it's not part of the
collection. I couldn't find forms under DAO.Database.

I wrote this instead:

Dim db As Access.Application
Dim frm As Form
Set db = Application
For Each frm In db.Forms
If frm.Visible Then
DoCmd.Close acForm, frm.Name
'Debug.Print frm.Name
End If
Next frm

Which works, but only deletes one form; I have to run the function in
the immediately window twice to get it to delete two forms. Which is
strange considering two print in the immediate window.

Can you tell me what I was doing wrong with your code...maybe I need
to set an additional reference to DAO?

Thanks,
alex