By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,604 Members | 858 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,604 IT Pros & Developers. It's quick & easy.

display results of COUNT query in text box

WyvsEyeView
P: 46
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
Share this Question
Share on Google+
3 Replies


WyvsEyeView
P: 46
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

Expert Mod 2.5K+
P: 2,545
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.

-Stewart
Jul 23 '08 #3

WyvsEyeView
P: 46
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.