Returning Count value from SELECT statement

[Meltdown]

Hi all,

I have the following code which counts up the records in a particular query. How can I run this code from my form and stick the count value returned into a textbox.

SELECT Count(qryCountRepRecs1.RepsRecordsID) AS CountOfRepsRecordsID
FROM qryCountRepRecs1;

I can't use DCount as qryCountRepRecs1 references many tables. I can't use DoCmd.RunSQL as that doesn't execute SELECT statements??

Thanks
Melt

 

[MarkK]

What happens when you type this ...

? DCount("*", "qryCountRepRecs1")

... into the immediate window?

 

[Brianwarnock]

If the form is based on the query it will automatically return the value when opened, if the form where you want the count is based on other data then make the count form a subform.

Brian

 

[MarkK]

Brian, my read is that the OP wants the result to appear in a textbox on a form. Certainly his SQL can only return a single record. What I don't get is his assertion that DCount() won't work 'cause his query has joins in it.
Mark

 

[Brianwarnock]

Nope I don't understand that either,but assumed that he had tried it and it didn't work, so all i was offering was that a subform based on the query would have 1 field - the count, and so would give him that on his form.

As often there is more than one way to do things and we can only offer suggestions as it is not our project.

Brian

 

[Meltdown]

Hi guys, thanks for the replies.

Thanks lagbolt your solution works fine DCount("*", "qryCountRepRecs1"), I wasn't aware I could feed a query into a DCount, problem solved

Thanks for your input Brian

Regards
Melt

 

[MarkK]

Yeah, a query is functionally indistiguishable from a table.
Cheers guys,