A trap to avoid

[Steve Jorgensen]

Since reading values from calculated controls in Access 2000 and 2002 from
code has proven unrliable at best, and since I like to avoid running
separate queries to calculate sums of subform records since they can give
inconsistent results if there's more than on interface or user with access
to the table, I wrote code to loop through a subform's RecordsetClone to
calculate aggregate values.

This approach worked fine until I moved to a master record with no related
records in the subform. After moving to that record, the subform returned
an empty recordset clone from then on even after I moved back to a parent
row with related records, and they appeared in the subform.

The work-around I came up with was to write a function that takes a form as
a parameter and returns the form's RecordsetClone if the form has a record
count > 1 not counting the new record if the current record is new. If the
form's record count is zero, the function returns Nothing, and does not
attempt to access the form's RecordsetClone. This solved the problem.

Just thought y'all might want to know.

- Steve Jorgensen

----
I would have written you a shorter program,
but I didn't have the time.

[Allen Browne]

Re: A trap to avoid

Steve, thanks for posting your tips appreciated.

The bit that still worries me about this is if the subform is filtered.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Steve Jorgensen" <nospam@nospam. nospam> wrote in message
news:4adilvs367 sqoji0vjoufamjd vgceskgah@4ax.c om...[color=blue]
> Since reading values from calculated controls in Access 2000 and 2002 from
> code has proven unrliable at best, and since I like to avoid running
> separate queries to calculate sums of subform records since they can give
> inconsistent results if there's more than on interface or user with access
> to the table, I wrote code to loop through a subform's RecordsetClone to
> calculate aggregate values.
>
> This approach worked fine until I moved to a master record with no related
> records in the subform. After moving to that record, the subform returned
> an empty recordset clone from then on even after I moved back to a parent
> row with related records, and they appeared in the subform.
>
> The work-around I came up with was to write a function that takes a form[/color]
as[color=blue]
> a parameter and returns the form's RecordsetClone if the form has a record
> count > 1 not counting the new record if the current record is new. If[/color]
the[color=blue]
> form's record count is zero, the function returns Nothing, and does not
> attempt to access the form's RecordsetClone. This solved the problem.
>
> Just thought y'all might want to know.
>
> - Steve Jorgensen
>
> ----
> I would have written you a shorter program,
> but I didn't have the time.[/color]

[Dimitri Furman]

Re: A trap to avoid

On Sep 05 2003, 09:17 pm, Steve Jorgensen <nospam@nospam. nospam> wrote
in news:4adilvs367 sqoji0vjoufamjd vgceskgah@4ax.c om:
[color=blue]
> Since reading values from calculated controls in Access 2000 and 2002
> from code has proven unrliable at best[/color]

Steve,

Maybe this has been covered recently and I just missed it, but can you
elaborate a bit? Is there a difference from the way it works in Access 97?

--
(remove a 9 to reply by email)

[Deano]

Re: A trap to avoid

Steve Jorgensen wrote:[color=blue]
> Since reading values from calculated controls in Access 2000 and 2002
> from code has proven unrliable at best, and since I like to avoid
> running separate queries to calculate sums of subform records since
> they can give inconsistent results if there's more than on interface
> or user with access to the table, I wrote code to loop through a
> subform's RecordsetClone to calculate aggregate values.
>
> This approach worked fine until I moved to a master record with no
> related records in the subform. After moving to that record, the
> subform returned an empty recordset clone from then on even after I
> moved back to a parent row with related records, and they appeared in
> the subform.
>
> The work-around I came up with was to write a function that takes a
> form as a parameter and returns the form's RecordsetClone if the form
> has a record count > 1 not counting the new record if the current
> record is new. If the form's record count is zero, the function
> returns Nothing, and does not attempt to access the form's
> RecordsetClone. This solved the problem.
>
> Just thought y'all might want to know.[/color]

Yeah, nice one Steve, or should I say 'arrrghh' - another way my app might
break because i have similar code running somewhere...

[color=blue]
>
> - Steve Jorgensen
>
> ----
> I would have written you a shorter program,
> but I didn't have the time.[/color]

[Steve Jorgensen]

Re: A trap to avoid

On 6 Sep 2003 02:25:05 GMT, Dimitri Furman <dfurman@cloud9 9.net> wrote:
[color=blue]
>On Sep 05 2003, 09:17 pm, Steve Jorgensen <nospam@nospam. nospam> wrote
>in news:4adilvs367 sqoji0vjoufamjd vgceskgah@4ax.c om:
>[color=green]
>> Since reading values from calculated controls in Access 2000 and 2002
>> from code has proven unrliable at best[/color]
>
>Steve,
>
>Maybe this has been covered recently and I just missed it, but can you
>elaborate a bit? Is there a difference from the way it works in Access 97?[/color]

I don't know if it's been covered here, but I've exprienced it, and other
developers I've talked to have as well. Basically, it seems as if, in
Access 97 (the good ol' days for things like this), if you try to read the
value of a calculated control, that forces everything to wait while the
calculation gets all its dependencies in order and produces a result.
Also, in Access 97, if one calculation happens to be temporarily broken,
all unrelated calculations continue to work.
In Access 2000 and 2002, on the other hand, if you try to read the value of
a calculation from code, if Access hasn't gotten around to completing the
calculation, it can give you a Null instead. There don't seem to be any
reiable work-arounds, and I and others have treid really hard to come up
with them.

- Steve Jorgensen

----
I would have written you a shorter program,
but I didn't have the time.

[Steve Jorgensen]

Re: A trap to avoid

An update on this problem. I was checking for an empty recordset using
rst.EOF and rst.BOF. I do this because I know that .RecordCount is
unreliable until the last record has been navigated to. In my code, once
the RecordSetClone has been empty. EOF and BOF continue to be true whether
it is still empty or not. but .RecordCount is >0 if there are records. All
I had to do was check the .RecordCount as well as .EOF and .BOF, and
everything's fine.

On Sat, 06 Sep 2003 01:17:20 GMT, Steve Jorgensen <nospam@nospam. nospam>
wrote:
[color=blue]
>Since reading values from calculated controls in Access 2000 and 2002 from
>code has proven unrliable at best, and since I like to avoid running
>separate queries to calculate sums of subform records since they can give
>inconsistent results if there's more than on interface or user with access
>to the table, I wrote code to loop through a subform's RecordsetClone to
>calculate aggregate values.
>
>This approach worked fine until I moved to a master record with no related
>records in the subform. After moving to that record, the subform returned
>an empty recordset clone from then on even after I moved back to a parent
>row with related records, and they appeared in the subform.
>
>The work-around I came up with was to write a function that takes a form as
>a parameter and returns the form's RecordsetClone if the form has a record
>count > 1 not counting the new record if the current record is new. If the
>form's record count is zero, the function returns Nothing, and does not
>attempt to access the form's RecordsetClone. This solved the problem.
>
>Just thought y'all might want to know.
>
>- Steve Jorgensen
>
>----
>I would have written you a shorter program,
>but I didn't have the time.[/color]


- Steve Jorgensen

----
I would have written you a shorter program,
but I didn't have the time.

[Tony Toews]

Re: A trap to avoid

Steve Jorgensen <nospam@nospam. nospam> wrote:
[color=blue]
>The work-around I came up with was to write a function that takes a form as
>a parameter and returns the form's RecordsetClone if the form has a record
>count > 1 not counting the new record if the current record is new. If the
>form's record count is zero, the function returns Nothing, and does not
>attempt to access the form's RecordsetClone. This solved the problem.[/color]

Very nice. I've had a similar problem a few years ago in which I was having some
similar wierd problems.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at

http://www.granite.ab.ca/accsmstr.htm

[Steve Jorgensen]

Re: A trap to avoid

On Sat, 06 Sep 2003 23:31:13 GMT, Chuck Grimsby
<c.grimsby@worl dnet.att.net.in valid> wrote:
[color=blue]
>
>For what it's worth, I just check for .BOF when doing this, stepping
>through backwards. I don't even bother with .EOF, since I'm stepping
>through backwards, I know I'm never going to hit that!
>
>Experience had taught me that .EOF isn't always accurate, but .BOF
>always is.[/color]

In this case, I was checking EOF and BOF before doing any navigation since
getting a new reference from .RecordsetClone . It seems that changing to a
less restrictive filter was not clearing either .EOF or .BOF after they got
set, so I don't know what that means in terms of the accuracy of either
one. I guess one could say it's by design since the values were correct
after the previous navigation, and no further navigation was done, just a
change in filtering.

Regarding the use of .RecordCount here to check for non-empty, I think it's
safe. Although, the value may not always be accurate, I've never seen a
case where it was zero for a non-empty recordset. It seems to at least
read the first row and count that in all cases. In fact, given that,
there's probably no point in using .EOF or .BOF in the condition at all.

- Steve Jorgensen

----
I would have written you a shorter program,
but I didn't have the time.

[Steve Jorgensen]

Re: A trap to avoid

On Sun, 07 Sep 2003 10:34:59 GMT, Chuck Grimsby
<c.grimsby@worl dnet.att.net.in valid> wrote:
....[color=blue][color=green]
>>Regarding the use of .RecordCount here to check for non-empty, I think it's
>>safe. Although, the value may not always be accurate, I've never seen a
>>case where it was zero for a non-empty recordset. It seems to at least
>>read the first row and count that in all cases. In fact, given that,
>>there's probably no point in using .EOF or .BOF in the condition at all.[/color]
>
>True. I've done that as well. I can't say as .RecordCount has ever
>failed me either. (Well, when I don't forget to do a .MoveLast first
>however! <Grin>)[/color]

Of course, here, I need to make sure there is more than one record -before-
attempting to do a .MoveLast, or any other move unless I'm going to use On
Error Resume Next.

- Steve Jorgensen

----
I would have written you a shorter program,
but I didn't have the time.

[David W. Fenton]

Re: A trap to avoid

c.grimsby@world net.att.net.inv alid (Chuck Grimsby) wrote in
<012mlv8g4n4it7 jog8p94nrpevu82 u34g6@4ax.com>:
[color=blue]
>On Sun, 07 Sep 2003 00:17:47 GMT, Steve Jorgensen
><nospam@nospam .nospam> wrote:[color=green]
>>Regarding the use of .RecordCount here to check for non-empty, I
>>think it's safe. Although, the value may not always be accurate,
>>I've never seen a case where it was zero for a non-empty
>>recordset. It seems to at least read the first row and count
>>that in all cases. In fact, given that, there's probably no
>>point in using .EOF or .BOF in the condition at all.[/color]
>
>True. I've done that as well. I can't say as .RecordCount has
>ever failed me either. (Well, when I don't forget to do a
>.MoveLast first however! <Grin>)[/color]

You don't need to do a .MoveLast unless you want an accurate
non-zero recordcount. When .RecordCount returns 0, it is always
reliable -- you have an empty recordset. If it returns something
else, then you know you have a non-empty recordset, but you won't
know the exact count of records until you do your .MoveLast.

I think y'all are confusing two questions here, both of which can
be answered by .RecordCount:

1. how many records are in the recordset -- requires .MoveLast

2. whether the recordset is empty or not -- requires only a check
if the .RecordCount is 0 or not.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Steve Jorgensen]

Re: A trap to avoid

On Sun, 07 Sep 2003 21:11:56 GMT, dXXXfenton@bway .net (David W. Fenton)
wrote:
[color=blue]
>c.grimsby@worl dnet.att.net.in valid (Chuck Grimsby) wrote in
><012mlv8g4n4it 7jog8p94nrpevu8 2u34g6@4ax.com> :
>[color=green]
>>On Sun, 07 Sep 2003 00:17:47 GMT, Steve Jorgensen
>><nospam@nospa m.nospam> wrote:[color=darkred]
>>>Regarding the use of .RecordCount here to check for non-empty, I
>>>think it's safe. Although, the value may not always be accurate,
>>>I've never seen a case where it was zero for a non-empty
>>>recordset. It seems to at least read the first row and count
>>>that in all cases. In fact, given that, there's probably no
>>>point in using .EOF or .BOF in the condition at all.[/color]
>>
>>True. I've done that as well. I can't say as .RecordCount has
>>ever failed me either. (Well, when I don't forget to do a
>>.MoveLast first however! <Grin>)[/color]
>
>You don't need to do a .MoveLast unless you want an accurate
>non-zero recordcount. When .RecordCount returns 0, it is always
>reliable -- you have an empty recordset. If it returns something
>else, then you know you have a non-empty recordset, but you won't
>know the exact count of records until you do your .MoveLast.
>
>I think y'all are confusing two questions here, both of which can
>be answered by .RecordCount:
>
>1. how many records are in the recordset -- requires .MoveLast
>
>2. whether the recordset is empty or not -- requires only a check
>if the .RecordCount is 0 or not.[/color]

Cool - that confirms how I'm using RecordCount in this case. I don't need
or try to get an accurate record count up-front because I'm about to loop
through the records, and EOF will tell me when I'm done.

- Steve Jorgensen

----
I would have written you a shorter program,
but I didn't have the time.

[Steve Jorgensen]

Re: A trap to avoid

On Sun, 07 Sep 2003 21:09:11 GMT, dXXXfenton@bway .net (David W. Fenton)
wrote:
[color=blue]
>nospam@nospam. nospam (Steve Jorgensen) wrote in
><rkhklvssjabit dbq05a676e12p4m 7gcu7d@4ax.com> :
>[color=green]
>>An update on this problem. I was checking for an empty recordset
>>using rst.EOF and rst.BOF. I do this because I know that
>>.RecordCoun t is unreliable until the last record has been
>>navigated to. . . .[/color]
>
>Not true. The .RecordCount never returns anything but 0 if the
>recordset is empty. It returns an unreliable number if it is *not*
>empty, but since you don't care exactly how many records there are,
>checking the .RecordCount is sufficient.[/color]

By unreliable, here, I just meant the actual count, not that I think it's
unreliable for determining empty/non-empty. It's nice to get confirmation
on this, though.
[color=blue]
>[color=green]
>> . . . In my code, once the RecordSetClone has been empty.
>>EOF and BOF continue to be true whether it is still empty or not.
>>but .RecordCount is >0 if there are records. All I had to do was
>>check the .RecordCount as well as .EOF and .BOF, and everything's
>>fine.[/color]
>
>You shouldn't need to check .EOF and .BOF at all.[/color]

Right, that's what my code now assumes since .EOF and .BOF are obviously
not helpful in this case.

- Steve Jorgensen

----
I would have written you a shorter program,
but I didn't have the time.

[Bri]

Re: A trap to avoid

Steve,

I have had the problem with calculated values in Access 97 as well. I tried all
sorts of things like DoEvents and Me.Refresh to try to get the calculated values
to finish calculating before a reference to them and was unable to do it. I
opted for the separate query method at the time as the records are in a temp
table on the workstation so the execution time was unnoticable. The
RecordsetClone method will be handy for the next time, particularly if the table
is in the backend of a slow network (is there any other kind?).

--
Bri

Steve Jorgensen wrote:[color=blue]
>
> Since reading values from calculated controls in Access 2000 and 2002 from
> code has proven unrliable at best, and since I like to avoid running
> separate queries to calculate sums of subform records since they can give
> inconsistent results if there's more than on interface or user with access
> to the table, I wrote code to loop through a subform's RecordsetClone to
> calculate aggregate values.[/color]