Stumped... "Enter parameter value" when closing form with suborm referring to data in parent

[Darin]

I have a problem I just can't figure out. I have a form with a
subform, and the recordsource of the subform has criteria based on
some unbound fields in the parent form so that data in the parent form
affects data in the subform. I've had this type of setup often, but
I've run across a couple forms where this has caused an "enter
parameter value" pop up for each reference to the parent control in
the query when the main form is closed. I've spent a lot of time
trying to figure out how to solve it, and eventually gave up, and
simply put code into the command button that closes the form to set
the sourceobject of the subform to "" before closing the form. This
doesn't solve the problem if the user clicks the "x" to close the
form, but at least I can tell them to use the command button instead
to avoid the "error". Unfortunately, putting the same code in the "on
close" event doesn't work, because by the time it's triggered, the
subform is apparently already closed, because you then get an error
because you're trying to change the sourceobject of something that
doesn't exist.

I'm now in a situation where the hack of removing the sourceobject via
the command button isn't sufficient. I need to figure out why the
query of the subform is trying to run after the parent has already
started shutting down. I DID have this problem in two places... in
the second one, the reference was in the recordsource of a combobox in
the subform. I had an "on current" event that updated that combobox,
so that is what apparently was triggering THAT query to run on close.
I've changed that code to only requery the combobox if the parent is
still loaded.

The cause for the other form, however, is more elusive. There is no
requerying going on of that form. I can't find any event triggers
that could be causing that data to be refreshed during a close. I've
tried saving the recordsource of the subform to a stand-alone query
and using the query name as the recordsource, and changing the
recordsource of the subform to "" in the on-close event (which
actually made the situation worse: I got the parameter pop-up
repeatedly, apparently possibly once for each record in the table,
rather than just once for each reference).

Any suggestions?

Thanks!

 

[Marshall Barton]

I have a problem I just can't figure out. I have a form with a
subform, and the recordsource of the subform has criteria based on
some unbound fields in the parent form so that data in the parent form
affects data in the subform. I've had this type of setup often, but
I've run across a couple forms where this has caused an "enter
parameter value" pop up for each reference to the parent control in
the query when the main form is closed. I've spent a lot of time
trying to figure out how to solve it, and eventually gave up, and
simply put code into the command button that closes the form to set
the sourceobject of the subform to "" before closing the form. This
doesn't solve the problem if the user clicks the "x" to close the
form, but at least I can tell them to use the command button instead
to avoid the "error". Unfortunately, putting the same code in the "on
close" event doesn't work, because by the time it's triggered, the
subform is apparently already closed, because you then get an error
because you're trying to change the sourceobject of something that
doesn't exist.

I'm now in a situation where the hack of removing the sourceobject via
the command button isn't sufficient. I need to figure out why the
query of the subform is trying to run after the parent has already
started shutting down. I DID have this problem in two places... in
the second one, the reference was in the recordsource of a combobox in
the subform. I had an "on current" event that updated that combobox,
so that is what apparently was triggering THAT query to run on close.
I've changed that code to only requery the combobox if the parent is
still loaded.

The cause for the other form, however, is more elusive. There is no
requerying going on of that form. I can't find any event triggers
that could be causing that data to be refreshed during a close. I've
tried saving the recordsource of the subform to a stand-alone query
and using the query name as the recordsource, and changing the
recordsource of the subform to "" in the on-close event (which
actually made the situation worse: I got the parameter pop-up
repeatedly, apparently possibly once for each record in the table,
rather than just once for each reference).

You are probably familiar with:
http://support.microsoft.com/kb/811860/en-us
which I think is near useless beyond indirectly admitting
that this problem is a bug.

Apparantly, you have also tried the work arounds of clearing
the Source Object and/or Record Source properties. You have
also noticed that Row Source queries can also get trapped by
the same bug.

Another place that I can think of that might run into the
problem is Conditional Formatting, especially if you are
seeing the problem for every record in view.

I haven't seen it, but maybe a text box Control Source
expression with a DLookup (or DCount, etc) could also do it.

 

[google]

Apparantly, you have also tried the work arounds of clearing
the Source Object and/or Record Source properties.

Yes, in fact, that's the workaround I've been using. There are a
couple of reasons I was hoping to find a better solution. For one,
contrary to what the KB suggests, my users DO get the pop-up if they
use "X" to close the form. So even though I've got code in MY close
button, if they use the X out of habit, they get the pop-up. I HATE
that, because it confuses them, and makes my app look sloppy. But the
bigger reason is that I have some major update/imports that I have to
run on a daily basis which really needs to run while no users have the
db open. So once I have all the data gathered that I'll be importing,
I run some code that prompts all the users to exit the db for a few
minutes for the daily update, then it initiates all the update queries
once the last user has exited. There is additional code that closes
their db session if they don't respond to the exit request within a
certain time frame. The problem that has surfaced is that if someone
has the form open that has this "parameter value" bug, if their
database session closes down with my code as opposed to them clicking
cancel in the "parameter value" pop-up, they don't seem to close the
database "cleanly". In such a case, all my updates end up taking
about 45-60 minutes instead of the usual 2-3, and the database bloats
from 30MB to 300MB. This seems to ONLY be a problem if someone is in
the particular screen that has the "enter parameter value" pop-up bug
AND doesn't shut it down themselves.

I guess the workaround is going to be to add code in my "please close
the database now" code that checks to see if any forms that have this
bug are open, set the sources to nothing, then close those forms (in
the cases where it's timed out and needs to force them out). I was
HOPING there might be a better workaround, but maybe not. :-/

Thanks for the reply... at least it's good to know I'm not crazy.

 

[Marshall Barton]

Yes, in fact, that's the workaround I've been using. There are a
couple of reasons I was hoping to find a better solution. For one,
contrary to what the KB suggests, my users DO get the pop-up if they
use "X" to close the form. So even though I've got code in MY close
button, if they use the X out of habit, they get the pop-up. I HATE
that, because it confuses them, and makes my app look sloppy. But the
bigger reason is that I have some major update/imports that I have to
run on a daily basis which really needs to run while no users have the
db open. So once I have all the data gathered that I'll be importing,
I run some code that prompts all the users to exit the db for a few
minutes for the daily update, then it initiates all the update queries
once the last user has exited. There is additional code that closes
their db session if they don't respond to the exit request within a
certain time frame. The problem that has surfaced is that if someone
has the form open that has this "parameter value" bug, if their
database session closes down with my code as opposed to them clicking
cancel in the "parameter value" pop-up, they don't seem to close the
database "cleanly". In such a case, all my updates end up taking
about 45-60 minutes instead of the usual 2-3, and the database bloats
from 30MB to 300MB. This seems to ONLY be a problem if someone is in
the particular screen that has the "enter parameter value" pop-up bug
AND doesn't shut it down themselves.

I guess the workaround is going to be to add code in my "please close
the database now" code that checks to see if any forms that have this
bug are open, set the sources to nothing, then close those forms (in
the cases where it's timed out and needs to force them out). I was
HOPING there might be a better workaround, but maybe not. :-/

You might be experiencing the problem described in KB
article: http://support.microsoft.com/?id=811860

If that's the problem, the workarounds are rather bizarre
but effective. I avoid the problem by not maximizing any
forms, but if you have users that maximize everything, using
DoCmd.Restore before you use the Close method also works.