Problems using unbound controls as criteria, and other Acc'03 oddities

[google]

I'm developing an application for use within my company in Access 2003.
I'm new to '03, the application I did for my former employer was in
'97. The two applications have similar functionality (we're sales
offices, and I'm doing things such as associate directories, commission
calculations, order tracking, etc.). 2003 seems to have a few extra
features, but I seem to continually run in to oddities that seem like
they SHOULD work, but don't, or are just plain flaky. I can't figure
out if there's something I'm doing wrong, or if 2003 is just plain
flakier. I have a form I'm working on now that has two examples:

This form is for sales commissions: people in accounting will use it
to enter data, and the sales people will use it to view their own data.
Our commissions come in different forms, and need to be calculated
differently depending on the type of sale. Therefore, the main form
has several subforms, each of which handles a particular "type" of
commission data, and may have further subforms within it. One of the
things I do quite commonly, and am also doing here, is use unbound
controls on the main form that are used as filters for the data that is
displayed. In this case, the main form has a combobox with all of the
sales people in it, a couple of other controls to enter a date range of
data that they may want to review. Queries that make up the record
source of the individual subforms include these as criteria to
determine which records to display. I'm am starting to get issues
where when the form is closed, a parameter query type pop-up is asking
for the data that comes from the unbound "filter" controls. It's as if
the main form is closing first, and the queries in the sub-forms is
freaking out because data that it needs is now gone. I'm confused by
this for two reasons: a), the subforms aren't being re-queried, so I
don't know why they are looking for that data anyway, and b), why are
they apparently closing after the main form? I've NEVER had this
problem in '97. And I don't always have it in 2003. It seems almost
random, but once I create a subform that causes the problem, it happens
every time that form is closed. It's benign... the user can either hit
OK or Cancel to make it go away, since the form is closing anyway. But
it's quite bothersome, and makes the project look shoddy. In the
current case, it started happening after adding a subform that from a
data perspective is almost an exact copy of the one of the existing
subforms that didn't cause the problem, except it has some fields
removed, and another one added, to sum the data by salesperson.

The other problem I'm having is, one of the subforms is a continuous
form, with two "total" fields in the footer, which are simply sums of
data in the detail section. These two fields display a jittering
"Error", as if it's in some loop trying to calculate the values. When
the form is first opened it defaults to showing all the records, and
this particular subform shows commission splits for the current record
of it's parent which shows sales transactions. The ONLY thing that is
required to get them to calculate correctly is to re-query the subform
with the jittering fields. ALL of the data that the problematic Sum()
fields are supposed to be summing is filled in, but they can't seem to
sum it until you re-query it. What's worse, I can't seem to find a
place to simply put the re-query command in that will automatically
take care of it... I've tried putting it in the main form's and
subform's load, open, and activate events, but it doesn't seem to work
unless I attach the re-query command to a command button that the user
clicks. Again, something that makes it look shoddy.

Admittedly, the queries and multiple subforms make it a little complex,
but I've certainly done the exact same thing with even more complex
queries and form/subform levels in '97, and never had these problems.
I know this is kind of vague, but does anyone have any clues or
suggestions?

[Ed Robichaud]

Your two problems may be related. When you do a Requery command and the
parameter criteria values are not available (your comboboxes are empty) then
you'll get the parameter popups. Search your form's module for all
instances of "Requery".

Totaling of value(s) on subforms can be done by using an unbound control on
the main form and using a Domain function (DSum, etc.) as the control source
to calculate the desired amounts. You'd need to requery that unbound
control on main form Open and on AfterUpdate of the underlying subform
controls.
-Ed

<go****@darincline.com> wrote in message
news:11**********************@e56g2000cwe.googlegr oups.com...

I'm developing an application for use within my company in Access 2003.
I'm new to '03, the application I did for my former employer was in
'97. The two applications have similar functionality (we're sales
offices, and I'm doing things such as associate directories, commission
calculations, order tracking, etc.). 2003 seems to have a few extra
features, but I seem to continually run in to oddities that seem like
they SHOULD work, but don't, or are just plain flaky. I can't figure
out if there's something I'm doing wrong, or if 2003 is just plain
flakier. I have a form I'm working on now that has two examples:

This form is for sales commissions: people in accounting will use it
to enter data, and the sales people will use it to view their own data.
Our commissions come in different forms, and need to be calculated
differently depending on the type of sale. Therefore, the main form
has several subforms, each of which handles a particular "type" of
commission data, and may have further subforms within it. One of the
things I do quite commonly, and am also doing here, is use unbound
controls on the main form that are used as filters for the data that is
displayed. In this case, the main form has a combobox with all of the
sales people in it, a couple of other controls to enter a date range of
data that they may want to review. Queries that make up the record
source of the individual subforms include these as criteria to
determine which records to display. I'm am starting to get issues
where when the form is closed, a parameter query type pop-up is asking
for the data that comes from the unbound "filter" controls. It's as if
the main form is closing first, and the queries in the sub-forms is
freaking out because data that it needs is now gone. I'm confused by
this for two reasons: a), the subforms aren't being re-queried, so I
don't know why they are looking for that data anyway, and b), why are
they apparently closing after the main form? I've NEVER had this
problem in '97. And I don't always have it in 2003. It seems almost
random, but once I create a subform that causes the problem, it happens
every time that form is closed. It's benign... the user can either hit
OK or Cancel to make it go away, since the form is closing anyway. But
it's quite bothersome, and makes the project look shoddy. In the
current case, it started happening after adding a subform that from a
data perspective is almost an exact copy of the one of the existing
subforms that didn't cause the problem, except it has some fields
removed, and another one added, to sum the data by salesperson.

The other problem I'm having is, one of the subforms is a continuous
form, with two "total" fields in the footer, which are simply sums of
data in the detail section. These two fields display a jittering
"Error", as if it's in some loop trying to calculate the values. When
the form is first opened it defaults to showing all the records, and
this particular subform shows commission splits for the current record
of it's parent which shows sales transactions. The ONLY thing that is
required to get them to calculate correctly is to re-query the subform
with the jittering fields. ALL of the data that the problematic Sum()
fields are supposed to be summing is filled in, but they can't seem to
sum it until you re-query it. What's worse, I can't seem to find a
place to simply put the re-query command in that will automatically
take care of it... I've tried putting it in the main form's and
subform's load, open, and activate events, but it doesn't seem to work
unless I attach the re-query command to a command button that the user
clicks. Again, something that makes it look shoddy.

Admittedly, the queries and multiple subforms make it a little complex,
but I've certainly done the exact same thing with even more complex
queries and form/subform levels in '97, and never had these problems.
I know this is kind of vague, but does anyone have any clues or
suggestions?

[google]

Thanks for the reply! Actually, the first problem has now gone away.
I added a sort order to the recordset of this subform, and suddenly the
sum fields are calculating fine from the beginning. There were a
couple of other times while I was working on this form that the sum
fields wouldn't have any problems, but then I'd make another change and
the problem would return. For what ever reason, adding a sort order to
the records seems to have permanently (hopefully) solved the problem
with the totals. Of course, I'm still mystified... I don't know why
the ORDER of the records would affect their sum, or the ability to
calculate one. But I guess for now I'll just be happy that the issue
has gone away.

On the second issue, I have searched through my code to find instances
of requery... I do have it in several places - places that I need it
(like the after update event of the unbound controls that I'm using for
filter criteria so the data updates when the user changes the filter
criteria, just like I do in many other forms with similar
functionality), but I don't have it in any events that should be
triggering when the form closes.

[Rain Wang]

test

<go****@darincline.com>
??????:11**********************@e56g2000cwe.google groups.com...

I'm developing an application for use within my company in Access 2003.
I'm new to '03, the application I did for my former employer was in
'97. The two applications have similar functionality (we're sales
offices, and I'm doing things such as associate directories, commission
calculations, order tracking, etc.). 2003 seems to have a few extra
features, but I seem to continually run in to oddities that seem like
they SHOULD work, but don't, or are just plain flaky. I can't figure
out if there's something I'm doing wrong, or if 2003 is just plain
flakier. I have a form I'm working on now that has two examples:

This form is for sales commissions: people in accounting will use it
to enter data, and the sales people will use it to view their own data.
Our commissions come in different forms, and need to be calculated
differently depending on the type of sale. Therefore, the main form
has several subforms, each of which handles a particular "type" of
commission data, and may have further subforms within it. One of the
things I do quite commonly, and am also doing here, is use unbound
controls on the main form that are used as filters for the data that is
displayed. In this case, the main form has a combobox with all of the
sales people in it, a couple of other controls to enter a date range of
data that they may want to review. Queries that make up the record
source of the individual subforms include these as criteria to
determine which records to display. I'm am starting to get issues
where when the form is closed, a parameter query type pop-up is asking
for the data that comes from the unbound "filter" controls. It's as if
the main form is closing first, and the queries in the sub-forms is
freaking out because data that it needs is now gone. I'm confused by
this for two reasons: a), the subforms aren't being re-queried, so I
don't know why they are looking for that data anyway, and b), why are
they apparently closing after the main form? I've NEVER had this
problem in '97. And I don't always have it in 2003. It seems almost
random, but once I create a subform that causes the problem, it happens
every time that form is closed. It's benign... the user can either hit
OK or Cancel to make it go away, since the form is closing anyway. But
it's quite bothersome, and makes the project look shoddy. In the
current case, it started happening after adding a subform that from a
data perspective is almost an exact copy of the one of the existing
subforms that didn't cause the problem, except it has some fields
removed, and another one added, to sum the data by salesperson.

The other problem I'm having is, one of the subforms is a continuous
form, with two "total" fields in the footer, which are simply sums of
data in the detail section. These two fields display a jittering
"Error", as if it's in some loop trying to calculate the values. When
the form is first opened it defaults to showing all the records, and
this particular subform shows commission splits for the current record
of it's parent which shows sales transactions. The ONLY thing that is
required to get them to calculate correctly is to re-query the subform
with the jittering fields. ALL of the data that the problematic Sum()
fields are supposed to be summing is filled in, but they can't seem to
sum it until you re-query it. What's worse, I can't seem to find a
place to simply put the re-query command in that will automatically
take care of it... I've tried putting it in the main form's and
subform's load, open, and activate events, but it doesn't seem to work
unless I attach the re-query command to a command button that the user
clicks. Again, something that makes it look shoddy.

Admittedly, the queries and multiple subforms make it a little complex,
but I've certainly done the exact same thing with even more complex
queries and form/subform levels in '97, and never had these problems.
I know this is kind of vague, but does anyone have any clues or
suggestions?