469,632 Members | 1,750 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,632 developers. It's quick & easy.

display results of COUNT query in text box

This seems like it should be so easy to do. I have a table, called tblTopics. Each topic can have one or more instances, contained in a table called tblTopicInst. tblTopics is bound to a form called frmTopics and tblTopicInst is bound to a form called frmTopicInst. From frmTopics, you access frmTopicInst via a button. I have also created a query, qryCountInst, that counts the number of instances for the topic currently displayed on frmTopics. I want to add a text box, txtCountInst, to frmTopics to display the number of instances for the topic--i.e., the results of qryCountInst. I also want to add a button to refresh this count. After searching all the Access documentation I have, I can't find any info on how to display the results of a COUNT query in a text box. The expression that Access "helps" me build--[qryCountInst]![total_record]--doesn't work either. How can I accomplish this simple thing? Thanks for your help!
Jul 21 '08 #1
3 4183
FYI, I kept looking and finally was able to get it working using DCount. Still no way to auto-refresh, but that's not really critical. I can add a button or something if it proves to be a need.

Specifically, I created the text box and added this as the Control Source:

=DCount("top_id", "tblTopicInst", "top_id = Forms!frmTopics!top_id")
Jul 22 '08 #2
Stewart Ross
2,545 Expert Mod 2GB
Hi. DCount is a good solution for what you need to do. There are several ways to refresh the value calculated. One such is to use the On Current event of the form itself, which fires once for each record you move to.

In your form properties right-click on On Current and select build. A skeleton of the event sub is created for you. Just add
Expand|Select|Wrap|Line Numbers
  1. Me![name_of_your_control].Requery
where name_of_your_control is replaced by the actual name of your textbox with the DCount control source.

This will requery the DCount for each record of the form without your users having to press any 'refresh' buttons to do so.

Jul 23 '08 #3
Actually, using the OnCurrent event doesn't work because you never leave the form on which the count is displayed. You add the new record on a separate form, so perhaps there would be a way to trigger a requery when the other form closes. I'll look into that, but it's not a showstopper. I appreciate your suggestion, though--thanks very much.
Jul 24 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by erica | last post: by
4 posts views Thread by Joe Van Meer | last post: by
1 post views Thread by sunilkeswani | last post: by
5 posts views Thread by PHPBABY3 | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.