By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,476 Members | 1,376 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,476 IT Pros & Developers. It's quick & easy.

Problem with opening form by timer event

P: n/a
Hi,
In my database (access 2000) everything is done within the main form
("OpenForm"). It has a timer which triggers the code below. It first
closes all reports and forms. Then it reopens the main form. When the
main form is opened, the user is first asked for a password in a pop up
window, which stores all kind of info in public variables. Problem is
that quite often, the loginform makes access hang (a nonresponsive
login form is displayed on the screen, and a lost "MSACCESS.EXE" is
show in the process list). This ONLY happens when the main form is
started by the "IdleTimeDetected" Sub (which resides in a Module).

To be sure that no remnants of processes are present (and to do regular
cleaning up) I thought to use the CompactDatabase command. When you
compact the database from the menu (Options->Compact&repair), the
database is closed, compacted and re-opened (and automatically opens
the main form). This should resolve the above problem. However I cant
find out how to compact the database from within the "IdleTimeDetected"
Sub.

Would this be possible at all?

Thanks

Mike

===============================

Sub IdleTimeDetected(ExpiredMinutes)
Dim frmName As String

frmName = "OpenForm"

objectCount = Application.Reports.Count
For I = 0 To objectCount - 1
DoCmd.Close acReport, Application.Reports(I).Name, acSaveNo
Next

objectCount = Application.Forms.Count
For I = 0 To objectCount - 1
DoCmd.Close acForm, Application.Forms(I).Name, acSaveNo
Next

DoCmd.OpenForm frmName
DoCmd.Maximize

End Sub
=======================================

Apr 22 '06 #1
Share this Question
Share on Google+
12 Replies


P: n/a
If you have more than one form or report open and you want to close them by
their id-number, loop through the items backwards

for I = objectcount -1 to 0 step -1

When you close the first form the position of the second form shifts from 1
to 0. This means you will not be able to access forms(1), since it has
become forms(0). This may cause your code to hang.

"insomniux" <di*******@bosschaert.org> wrote in message
news:11**********************@z34g2000cwc.googlegr oups.com...
Hi,
In my database (access 2000) everything is done within the main form
("OpenForm"). It has a timer which triggers the code below. It first
closes all reports and forms. Then it reopens the main form. When the
main form is opened, the user is first asked for a password in a pop up
window, which stores all kind of info in public variables. Problem is
that quite often, the loginform makes access hang (a nonresponsive
login form is displayed on the screen, and a lost "MSACCESS.EXE" is
show in the process list). This ONLY happens when the main form is
started by the "IdleTimeDetected" Sub (which resides in a Module).

To be sure that no remnants of processes are present (and to do regular
cleaning up) I thought to use the CompactDatabase command. When you
compact the database from the menu (Options->Compact&repair), the
database is closed, compacted and re-opened (and automatically opens
the main form). This should resolve the above problem. However I cant
find out how to compact the database from within the "IdleTimeDetected"
Sub.

Would this be possible at all?

Thanks

Mike

===============================

Sub IdleTimeDetected(ExpiredMinutes)
Dim frmName As String

frmName = "OpenForm"

objectCount = Application.Reports.Count
For I = 0 To objectCount - 1
DoCmd.Close acReport, Application.Reports(I).Name, acSaveNo
Next

objectCount = Application.Forms.Count
For I = 0 To objectCount - 1
DoCmd.Close acForm, Application.Forms(I).Name, acSaveNo
Next

DoCmd.OpenForm frmName
DoCmd.Maximize

End Sub
=======================================

Apr 22 '06 #2

P: n/a
This sounds reasonable. I'll give it a try.
Thanks
Mike

Apr 22 '06 #3

P: n/a
"insomniux" <di*******@bosschaert.org> wrote in
news:11**********************@z34g2000cwc.googlegr oups.com:
Hi,
In my database (access 2000) everything is done within the
main form ("OpenForm"). It has a timer which triggers the code
below. It first closes all reports and forms. Then it reopens
the main form. When the main form is opened, the user is first
asked for a password in a pop up window, which stores all kind
of info in public variables. Problem is that quite often, the
loginform makes access hang (a nonresponsive login form is
displayed on the screen, and a lost "MSACCESS.EXE" is show in
the process list). This ONLY happens when the main form is
started by the "IdleTimeDetected" Sub (which resides in a
Module).
You have bugs in the IdleTimeSelected Sub. That's what is
causing the system to hang. See my comments in thecode below.

To be sure that no remnants of processes are present (and to
do regular cleaning up) I thought to use the CompactDatabase
command. When you compact the database from the menu
(Options->Compact&repair), the database is closed, compacted
and re-opened (and automatically opens the main form). This
should resolve the above problem. However I cant find out how
to compact the database from within the "IdleTimeDetected"
Sub.

Would this be possible at all?
Until you fix your bug, it would be dangerous.
===============================

Sub IdleTimeDetected(ExpiredMinutes)
Dim frmName As String

frmName = "OpenForm"

objectCount = Application.Reports.Count
For I = 0 To objectCount - 1
DoCmd.Close acReport, Application.Reports(I).Name,
acSaveNo
Next
Watch what is happening in your loop.
I is zero. You close the report 0. Access renumbers all the open
reports by decrementing their index number.
You loop, and close report 1. That report was report 2 before
you closed report 0. And you have left report 0, which was
originally numbered 1, open.

Change to a do loop

Do until application.reports.count = 0
DoCmd.Close acReport, Application.Reports(0).Name, _
acSaveNo
loop
objectCount = Application.Forms.Count
For I = 0 To objectCount - 1
DoCmd.Close acForm, Application.Forms(I).Name, acSaveNo
Next
Do until application.forms.count = 0
DoCmd.Close acReport, Application.forms(0).Name, _
acSaveNo
loop
DoCmd.OpenForm frmName
DoCmd.Maximize

End Sub
=======================================


--
Bob Quintal

PA is y I've altered my email address.
Apr 22 '06 #4

P: n/a
hopefully the solution posted elsewhere in this thread will work for you,
since it's better to eliminate the cause of the error rather than
side-stepping it.

just as an fyi, you can use the following code to compact a database, as

On Error GoTo Compact_Err

Dim str As String
str = "Compact and Repair Database..."

CommandBars("Menu Bar").Controls("Tools").Controls("Database
Utilities").Controls(str).accDoDefaultAction

Compact_End:
Exit Sub

Compact_Err:
MsgBox err.Number & " " & err.Description, "Administrator:
isCompact()"

it will run from a form's module, or you can paste it into a Public Sub in a
standard module and call it from a form module.

hth
"insomniux" <di*******@bosschaert.org> wrote in message
news:11**********************@v46g2000cwv.googlegr oups.com...
This sounds reasonable. I'll give it a try.
Thanks
Mike

Apr 22 '06 #5

P: n/a
> CommandBars("Menu Bar").Controls("Tools").Controls("Database_
Utilities").Controls(str).accDoDefaultAction

Here I've a problem. I'm using a Dutch version of access. I can get all
commands from the menu's, except the first one "Menu Bar".

Application.CommandBars("Menu Bar")_
.Controls("Extra")_
.Controls("Databasehulpprogramma's " )_
.Controls("Database comprimeren en herstellen...").accDoDefaultAction
If I try CommandBars(1), or debug.print commandBars(1).Name, Access
crashes. The next time I open the database I get an error 3159, meaning
that I have to convert to a previous version and back to 2000 to be
able to open the database again.
I guess this should not be normal behaviour. Do I need to load special
references to access these properties?

Apr 23 '06 #6

P: n/a
"insomniux" <di*******@bosschaert.org> wrote in
news:11*********************@j33g2000cwa.googlegro ups.com:
CommandBars("Menu Bar").Controls("Tools").Controls("Database_

Utilities").Controls(str).accDoDefaultAction

Here I've a problem. I'm using a Dutch version of access. I
can get all commands from the menu's, except the first one
"Menu Bar".

Application.CommandBars("Menu Bar")_
.Controls("Extra")_
.Controls("Databasehulpprogramma's " )_
.Controls("Database comprimeren en
herstellen...").accDoDefaultAction
If I try CommandBars(1), or debug.print commandBars(1).Name,
Access
crashes. The next time I open the database I get an error
3159, meaning that I have to convert to a previous version and
back to 2000 to be able to open the database again.
I guess this should not be normal behaviour. Do I need to load
special references to access these properties?

Have you fixed the problems with your code that Roland Roos and
I identified, then tested thoroughly? and tested again?. Your
attempt to compact the database if you have mangled a pointer
with bad code may lead to the corruption you ask about here.

--
Bob Quintal

PA is y I've altered my email address.
Apr 23 '06 #7

P: n/a
well, i don't know. as i recall, i didn't need to load any special
references to use the code. the Dutch version of Access may have different
specifications, i suppose. you should also consider Bob's post re this
issue, and my earlier caution that it's better to fix a problem than to
sidestep it.

hth
"insomniux" <di*******@bosschaert.org> wrote in message
news:11*********************@j33g2000cwa.googlegro ups.com...
CommandBars("Menu Bar").Controls("Tools").Controls("Database_

Utilities").Controls(str).accDoDefaultAction

Here I've a problem. I'm using a Dutch version of access. I can get all
commands from the menu's, except the first one "Menu Bar".

Application.CommandBars("Menu Bar")_
.Controls("Extra")_
.Controls("Databasehulpprogramma's " )_
.Controls("Database comprimeren en herstellen...").accDoDefaultAction
If I try CommandBars(1), or debug.print commandBars(1).Name, Access
crashes. The next time I open the database I get an error 3159, meaning
that I have to convert to a previous version and back to 2000 to be
able to open the database again.
I guess this should not be normal behaviour. Do I need to load special
references to access these properties?

Apr 23 '06 #8

P: n/a
Bob and tina,
I've changed the procedure for closing reports and forms as you
suggested. However, the problem appeared before I introduced this code.
In some earlier posts it was suggested that open forms and reports
could cause this problem. That's why I added this code.
Since I have no other clue to the cause of this behaviour, my final
solution was to try a compact&repair.
Mike

tina wrote:
well, i don't know. as i recall, i didn't need to load any special
references to use the code. the Dutch version of Access may have different
specifications, i suppose. you should also consider Bob's post re this
issue, and my earlier caution that it's better to fix a problem than to
sidestep it.

hth


Apr 24 '06 #9

P: n/a
okay, well, hopefully the solution suggested by Roland and Bob took care of
the initial problem for you. sorry i wasn't able to help you get the
compact/repair code working - it is handy to use, in "normal" situations.
good luck with your project. :)
"insomniux" <di*******@bosschaert.org> wrote in message
news:11**********************@g10g2000cwb.googlegr oups.com...
Bob and tina,
I've changed the procedure for closing reports and forms as you
suggested. However, the problem appeared before I introduced this code.
In some earlier posts it was suggested that open forms and reports
could cause this problem. That's why I added this code.
Since I have no other clue to the cause of this behaviour, my final
solution was to try a compact&repair.
Mike

tina wrote:
well, i don't know. as i recall, i didn't need to load any special
references to use the code. the Dutch version of Access may have different specifications, i suppose. you should also consider Bob's post re this
issue, and my earlier caution that it's better to fix a problem than to
sidestep it.

hth

Apr 25 '06 #10

P: n/a
"insomniux" <di*******@bosschaert.org> wrote in
news:11**********************@g10g2000cwb.googlegr oups.com:
Bob and tina,
I've changed the procedure for closing reports and forms as
you suggested. However, the problem appeared before I
introduced this code. In some earlier posts it was suggested
that open forms and reports could cause this problem. That's
why I added this code. Since I have no other clue to the cause
of this behaviour, my final solution was to try a
compact&repair. Mike

Could it be that some other unclosed object is causing your
lockup?.

It has been suggested in this group that sometimes referring to a
checkbox value via an implicit instead of explicit call can cause
similar behaviour:. e.g. me.checkbox then (implicit) vs
me!checkbox.value (explicit)

--
Bob Quintal

PA is y I've altered my email address.
Apr 25 '06 #11

P: n/a
OK,
Thanks for pointing to this issue. I'll look into that as well and
report back the result.
Mike

Apr 25 '06 #12

P: n/a
I've been cleaning the code and made all references to checkboxes
explicit. This did not reduce frequency of crashes.
Later, I found that there was a reference in one of the systemtables to
a form which was removed in a previous release. This was definitely not
correct. I could not remove this record manually. What I had to do to
get rid of the remnants of this form was the following:
1. create a form with the same name (generated an error that the form
already existed, but after that action, the form was shown in the forms
window again).
2. rename the form (it could not be opened or removed, but it could be
renamed).
3. delete the form (after renaming it could be deleted).
After this action, the crashes have not appeared anymore (at least it
has not been reported by the users anymore). Hope this was the culprit
of the problem.
Thanks for your help

May 12 '06 #13

This discussion thread is closed

Replies have been disabled for this discussion.