display results of COUNT query in text box

[WyvsEyeView]

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!

[WyvsEyeView]

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")

[Stewart Ross]

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

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

[WyvsEyeView]

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.