Count Results in Access 97

[Daniel]

Okay, I hope all this makes sense... here goes.

I have a "main" form and within that form have a subform, which on a
specified delay (I think it's currently set to 2 minutes - using the timer
event) performs a requery to show the number of results within a table in
the database, via the form - me.(subformname).requery

In Access 2007, it works no problem, and shows the "count" results and will
happily continue to do this, even if left idle for a long time.

In Access 97 however, it opens up fine and shows the results, yet when it
comes to perform the requery, in the status bar is shows that is is
"calculating...." and seems to just hang there until the user does something
within the form, either clicking somewhere on the form, or searching for
something in the combo box, or whatever.

My question is, does Access 97 not support the requery/count the same as
2007? Do I need to put something else into the code to make this work?

I've searched online and cannot find the answer I need, which is why I've
ended up posting the question here.

Thanks in advance

Daniel

 

[Arvin Meyer [MVP]]

Post the code. There are many similarities in VBA code between the 2
versions, but obviously there have been additions to VBA in the later
versions. Without knowing your code, there's no way that we can determine
what may be wrong.

 

[Daniel]

I have several "pages" within the main form and the subform (called
suggestionsalertbar) is on page57 (not sure how it ended up as 57, but
that's not important).

This is set to the OnTimer event (as I wish to update every couple of
minutes or so to see if the value has changed):

If Page57.Visible = True Then Me.SuggestionsAlertBar.Forms.Requery

(The only reason I have the Page57 Visible part in it, is if I move away
from this page within the database, it throws up an error message)

The table which the subform is linked to has a Yes/No field in it called
"ActiveSuggestion" - I wanted to put something in there so that if none of
the records were True, the text box containing
"=Abs(Sum([activesuggestion]))" would not be visible. I asked for advice on
here and was given the following:

Text27.Visible = (DCount("activesuggestion", "suggestions",
"activesuggestion=True") > 0)
Label28.Visible = Text27.Visible

(The label listed is just a text label)

Like I say, the above all works fine in Access 2007 and Access 2000 - it's
just Access 97 it seems to have a problem with, although it does seem to get
there eventually, just takes a long time for the "requery" to return the
final count.

Hope I haven't missed anything out... if I have, let me know and I will post
it

Thanks

 

[Steve]

Are you saying you want to count the number of records in a table? You can
do that with:
DLookup("*","NameOfTable")

Steve
(e-mail address removed)

 

[Daniel]

I think I've sorted it now

I've put a quick fix in - when the form does the requery, it now opens and
closes another form - I'm not sure why but the requery in Access 97 (or at
least my Access 97) is very very slow at returning the result, or at times
does not return it at all... just says it's "calculating..." - it only seems
to wake up when the user does something within the database..

 

[Steve]

I made a mistake! I meant DCount and posted DLookup. Sorry!

DCount should be very quick.

Steve

 

[Daniel]

It is in Access 2000 onwards but 97 doesnt want to play for some reason....
I've given up now and gone with my "fix"

Thanks for the suggestions though - I appreciate your time

Daniel

 

[Arvin Meyer [MVP]]

DCount should work fine in Access 2.0 on, and I see nothing in your code
that won't work in Access 97.

Perhaps you are missing a reference to a code or object library. It can be
any library. Open any code window, choose Tools >>> References and look for
any marked as MISSING If there is one, either find the reference (assuming
you need it) or just uncheck it. Try compiling. If you needed the reference,
it will not compile.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access

I have several "pages" within the main form and the subform (called
suggestionsalertbar) is on page57 (not sure how it ended up as 57, but
that's not important).

This is set to the OnTimer event (as I wish to update every couple of
minutes or so to see if the value has changed):

If Page57.Visible = True Then Me.SuggestionsAlertBar.Forms.Requery

(The only reason I have the Page57 Visible part in it, is if I move away
from this page within the database, it throws up an error message)

The table which the subform is linked to has a Yes/No field in it called
"ActiveSuggestion" - I wanted to put something in there so that if none of
the records were True, the text box containing
"=Abs(Sum([activesuggestion]))" would not be visible. I asked for advice
on here and was given the following:

Text27.Visible = (DCount("activesuggestion", "suggestions",
"activesuggestion=True") > 0)
Label28.Visible = Text27.Visible

(The label listed is just a text label)

Like I say, the above all works fine in Access 2007 and Access 2000 - it's
just Access 97 it seems to have a problem with, although it does seem to
get there eventually, just takes a long time for the "requery" to return
the final count.

Hope I haven't missed anything out... if I have, let me know and I will
post it

Thanks

Post the code. There are many similarities in VBA code between the 2
versions, but obviously there have been additions to VBA in the later
versions. Without knowing your code, there's no way that we can determine
what may be wrong.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access

 

[david]

Access 97 contains a considerable amount of
16 bit Windows 3.1 code from 1992

It was apparently recompiled as 32 bit code for
Windows 95, but not all updated for the new API
or the new Windows version.

In particular, the "idle" behaviour of Windows 3.1
and Access 2.0 was different from what you get in
Windows 2000 and Access 2000. When you
click on A97 the behaviour is different, and when
you leave A97 alone the behaviour is different.

I haven't seen the behaviour you describe, but I
haven't been looking. And it probably depends on
which version of Windows you are using. On Windows
XP, I would try using the "compatibility mode" with
a shortcut to MSACCESS.EXE

(david)