Count Function

[Guest]

I currently have a form that is Unbound and only contains controls to open 12
different forms. My problem is that each text box must use a different
query. All I want to do is count the records in the query (I will use the ID
field) and then display it on the unbound form. When I bind the form to the
same same query as the text box uses, it works fine, but only for one query.
When I bind the form to the table that the queries are based on, it does not
work. I am hoping that my problem is just getting the expression right and
not that it isn't possible. I know just enough about VBA to be dangerous.
Please offer any suggestions that you feel appropriate.

Ron

 

[Granny Spitz via AccessMonster.com]

All I want to do is count the records in the query (I will use the ID
field)

Don't. Count(*) is faster. It's easiest to use DCount to set the count for
each text box like this:

DCount("*", "queryname")

If that seems to be too slow, you can create a recordset that counts the
records in the query and loop through all queries, setting each text box as
you go. But it's far easier to use DCount if you can.

 

[Guest]

Granny,

Who ever thought it would be so easy. Thanks a million for you assistance.
You're my hero. Have a great evening.

Ron

 

[Granny Spitz via AccessMonster.com]

Who ever thought it would be so easy. Thanks a million for you assistance.
You're my hero. Have a great evening.

You're welcome, Ron. I'm happy it worked so well for you!

 

[Guest]

Granny,

Although the DCount function worked as you said, it is also a bit on the
slow side and sometimes the first result does not stay updated in the text
box. It returns when I exit or change the focus. Can you talk me through
the other method and I will see if that solves all my problems. Thanks again
in advance.

Ron

 

[Granny Spitz via AccessMonster.com]

Although the DCount function worked as you said, it is also a bit on the
slow side and sometimes the first result does not stay updated in the text
box. It returns when I exit or change the focus.

It's requerying the text boxes because you set the calculated value as the
control source. Set the value in the form's open event instead.

Can you talk me through
the other method and I will see if that solves all my problems.

That's a lot more work. Try setting the text box values in the open event
first and see if that's acceptable.

 

[Granny Spitz via AccessMonster.com]

It's requerying the text boxes

should be "recalculating the text boxes."

 

[Guest]

Granny,

Let me give you a little more information regarding my database. I have a
switchboard that allows the user to select a function. One is to view
birthdays and anniversaries by month. I have individual queries for each
month and extract the "dd" for sorting purposes. That selection opens
another form with 12 control buttons for January through December. On each
button (overlayed), I have a text box which displays the number of records in
that months query. So January's control would have a digit in the upper
right indicating the number of birthdays and anniveraries in that month. If
I use an event on the form the controls are on, I would not be able to have
12 separate and diferent events. If I put it on the form to be opened, it
will not be present on the control. I hope this makes some kind of sense.
Thanks for your patience.

Ron