How to count each record returned from SQL statement

[lorirobn]

Hi,

I have a form that has SQL in its VBA module to return records, depending on what values user has selected on the form.

I use COUNT in the SQL so I can have a column of how many records exist with certain values.

Now I would like to add a grand total to be displayed on the bottom of the form of all these counts (or, it would be the same to just count how many rows are returned). I believe it may be too complicated to do this with the same SQL, so I was wondering if there's a way to do it programmatically within my form's VBA. As a former mainframe programmer, I know I need a loop to keep a running total of all the rows returned from my SQL statement. However, this part always baffles me within Access/VBA. Where do I have or create such a loop for each record returned? I just want to keep a running total as the SQL is performed. Does that make sense? I have programmed subforms that have a detail section, and may have been successful there, but not sure this time around.

I also posted this question within the Query forum.

Thanks,
Lori

 

[lorirobn]

ps - one place where I have the SQL is in my Form_Load event. How can I count records there returned from the SQL statement?

 

[JerryKlmns]

SELECT Count(*) FROM YourTable
WHERE .... the same where clause as your form's SQL select statement

Of course if you use the recordset.RecordCount for a Keyset cursor location you might have the same result. But I don't want to be me starting that issue again here - to trust or not to trust RecordCount!

 

[cmmrfrds]

If you already have a count with each line then put a textbox in the footer.
=sum(yourcountfieldname)

 

[Zion7]

I would agree with cmmrfrds 100%.

...just for the record, also DCount() is available.
little tricky to get criteria clause
from your rowsource maybe?...

 

[lorirobn]

Thank you - the ability to sum is exactly what I was looking for, cmmrfrds and zion.

One complication, though (isn't there always?) - the count I already have within each line is in a list box. Can I refer to its column within the "=sum" line? I tried "=Sum(lstBedSizes.Column(2))" but am getting #Error.

Just for the record, I added a loop where I total the count for each line in the list box, and this is working fine. But if I could get the sum to work, I'd like that better (seems more efficient than looping, plus I get to learn something).

Jerry - thank you, too... this time around I wanted to avoid adding any more SQL
Thanks again for all your help.

 

[PHV]

Have you tried to play with the lstBedSizes.Listcount property ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cmmrfrds]

I have not used a secondary item from a listbox control.

Normally, I use the name of the control not the field name, but this would not work for the listbox since the column you want is not the bound column.

I would need to experiment. Maybe Zion7 has done this.

 

[Zion7]

No cmmrfrds, I personally can't see it being feasible,
for the reasons you suggested.
again, wouldn't DSum(), now fit perfectly for that scenario?
I'm assuming you mean another control, would hold the value
from the list box, not a column, whithin the listbox itself?

Whatever the rowsource is for your listbox,
Modify that for the DSum() function.

If you need help, please post the Rowsource.