Access 2000: No events when hiding & reopening dialog box-a glitch in attempt to speed up app

[Matt Jaffey]

I have a question about hiding dialog boxes in Access 2000. I am
working on an application in which there is a form that is maximized
on the screen. On this form, there is a button which brings up a
dialog box on which some data entry can be done. The records that come
up in the dialog box are based on information found on the form behind
it.

For some reason, the dialog box opens very slowly (unacceptably
slowly) in some circumstances. I'm looking for ways to speed this up.
I'd be interested in any suggestions about this, but my question has
to do with one specific approach that I've tried:

Originally, the Close button on the dialog box closed the form. I've
been trying instead to simply hide the form by setting its visible
property to false. Then, the next time someone clicks on the button on
the main form, the DoCmd.OpenForm command opens the dialog box without
delay. There are a couple of problems though:

a) When the dialog box is hidden, it doesn't cause any events to take
place, so if the user was in the middle of editing a record in the
dialog box, hiding it doesn't cause the record to save. This is a
problem because the main form needs to do a requery that will show
changes made in the dialog box.

b) When the dialog box is made visible again, it doesn't cause any
events to take place. Suppose that after closing (hiding) the dialog
box, the user changes to another set of data on the main form, and
then returns to the dialog box - the data displaying in the dialog box
needs to change to correspond to the new data set on the main form. A
simple requery within the dialog box would do this, but since no
events take place, I haven't found a place to put the requery.

These problems don't occur if I don't use the modal and popup settings
and don't use "acDialog" as the window mode in the DoCmd.OpenForm
command. However, the modal and popup features are important to this
application. Also, it is important that the code in the on click event
for the button that opens the dialog box be suspended while the dialog
box is open (or visible). So it looks like I need to open this form as
a dialog box.

Does anyone have any ideas about this?

Thanks,
Matt

 

[Ken Snell]

Likely the slowness is because the form contains a combo box whose Row
Source query needs to be run (to fill the dropdown box), or the form's
Record Source query needs to be run. If either of these is true, then you
might look at trying to rewrite the queries so that they run faster (not
always possible, or may require the populating of a temporary table with
appropriate indices).

Actually, the Activate event will occur if the dialog box is made visible
again and it becomes the form on which the user must interact. The
Deactivate event occurs when you make the dialog box invisible.

 

[Matt Jaffey]

Ken,

Thanks for your idea about checking the combo box row source - that
might be fruitful. I think the form's Record Source is not the
problem.

Your comments about the Activate and Deactivate events make logical
sense, but they have me puzzled, because I tested for these events by
putting message boxes in them, and they definitely didn't run. The
behavior seems to be different when the form is opened as a dialog box
and then hidden than if it is opened as a "normal" form and then
hidden. What, if anything, was wrong with the test that I did?

Matt

 

[Ken Snell]

Hard to say what's different, as I don't know what you tested. Activate and
Deactivate will occur whenever a form is "activated" or "deactivated" ....
whether the form is opened in dialog mode or normal mode.

 

[Dirk Goldgar]

Hard to say what's different, as I don't know what you tested.
Activate and Deactivate will occur whenever a form is "activated" or
"deactivated" .... whether the form is opened in dialog mode or
normal mode.

Ken, I get the same behavior Matt reports when I use

DoCmd.OpenForm "frmDialog", WindowMode:=acDialog

 

[Ken Snell]

Hmmm...working ok here in A2K2 for me. I tested it and the activate and
deactive events both occurred as expected.

 

[Ken Snell]

My error, it seems.... You're right..it's doing this in A2K2 as well.

I thought I'd tested it in dialog mode, but I apparently tested it in
regular mode.

So....in the absence of an event, Matt, you will need to explicitly put the
desired code in the various actions that you know will happen. Essentially,
in the Close button (which hides the form), put a line of code in that will
explicitly save the current record:
If Me.Dirty = True Then Me.Dirty = False

And in the code that you run to make the form visible again, put a line of
code in to have the requery done for the form.

Perhaps another alternative (though I don't think it's a great one) would be
to set up a timer event on the form that checks the Visible status of the
dialog form, and to take actions accordingly. But seems a bit of an overkill
in this situation.

 

[Matt Jaffey]

Thanks for your help Ken. Both of these ideas worked.
1) playing with row source of a combo box
2) hiding dialog box instead of closing

Matt

:
snip

Likely the slowness is because the form contains a combo box whose Row
Source query needs to be run (to fill the dropdown box)

snip

:
snip

So....in the absence of an event, Matt, you will need to explicitly put the
desired code in the various actions that you know will happen. Essentially,
in the Close button (which hides the form), put a line of code in that will
explicitly save the current record:
If Me.Dirty = True Then Me.Dirty = False

And in the code that you run to make the form visible again, put a line of
code in to have the requery done for the form.

snip

 

[Matt Jaffey]

Ken,

Thanks for your help. Both of these approaches worked. So I can use either one:
1) Play with the combo box row source
2) Hide the dialog box instead of closing it

Matt

:
<<Likely the slowness is because the form contains a combo box whose Row
Source query needs to be run (to fill the dropdown box)>>

Snip

So....in the absence of an event, Matt, you will need to explicitly put the
desired code in the various actions that you know will happen. Essentially,
in the Close button (which hides the form), put a line of code in that will
explicitly save the current record:
If Me.Dirty = True Then Me.Dirty = False

And in the code that you run to make the form visible again, put a line of
code in to have the requery done for the form.

Snip

 

[Matt Jaffey]

Ken,

Thanks for your help. Both of these approaches worked. So I can use either one:
1) Play with the combo box row source
2) Hide the dialog box instead of closing it

Matt

:
<<Likely the slowness is because the form contains a combo box whose Row
Source query needs to be run (to fill the dropdown box)>>

Snip

So....in the absence of an event, Matt, you will need to explicitly put the
desired code in the various actions that you know will happen. Essentially,
in the Close button (which hides the form), put a line of code in that will
explicitly save the current record:
If Me.Dirty = True Then Me.Dirty = False

And in the code that you run to make the form visible again, put a line of
code in to have the requery done for the form.

Snip

 

[Matt Jaffey]

Sorry for the multiple posts. The website I posted those from kept on telling me that the posts failed.

 

[Ken Snell]

You're welcome. Glad you found success.