Tally records in VBA

[Access_guy49]

OK, currently I have a listbox which displays some results using an SQL query in the row source. In the list box i display records from a parent and child table. Therefor i'm getting duplicate information from the parent. (picture)

This is what i want for display purposes. But I want to be able to create a tally of all the students that came. In the example attached, You can see that for the first record, there are actually 3 parent records displayed each with 80 kids. but truely there was 80 in total... not 240.
I was hoping to run some VB code to tally the results in my list box, but adding the values in the column wont give me the results I want. Any Ideas? should I use a hidden list box with ONLY the parent records?

 

[vbaInet]

Are they two separate listboxes?

 

[Access_guy49]

Nope. It's all one list box. I want to display the tally in a text box at the bottem of my form.. i'm thinking maybe a hidden list box???

 

[vbaInet]

Not sure about the textbox. When you say tally the results, what do you actually mean? Have you got a screenshot of how it currently looks?

 

[Access_guy49]

The attachment in my first post is how it currently looks. I want to add up the student column.. but where i have...

80
80
80
79
79
79

I want 80+79 (which are the values in the SINGLE parent record) I don't want 80+80+80+79+79... ect.

 

[vbaInet]

It's either you use a DSum() function in a textbox or create another hidden listbox to dispplay distinct values then sum from that one. You can still sum on the current listbox ignoring duplicates but it will take longer.

DSum()
http://www.techonthenet.com/access/functions/domain/dsum.php

 

[Access_guy49]

Ok I was searching online and i'm wondering about a record set.
I think either VBA or SOS helped me with these in the past...
If i created a recordset would that work? record set and a hidden list box are sorta the same in a way are they not?

 

[vbaInet]

In the context of what you're trying to do yes they would both achieve the same results. A recordset might be a quicker way, but this time, instead of adding up in the recordset, add up in the sql of the recordset.

 

[Access_guy49]

In the context of what you're trying to do yes they would both achieve the same results. A recordset might be a quicker way, but this time, instead of adding up in the recordset, add up in the sql of the recordset.

umm.. sounds easy enough......

 

[vbaInet]

Like:

dim rst as DAO.Recordset

set rst = currentdb.openrecordset("SELECT Sum() AS SumOfRec FROM Table WHERE ...condition...")

functionToGetSum = rst!SumOfRec

rst.close
set rst = nothing

 

[Access_guy49]

Worked like a charm! thanks!

 

[vbaInet]

You're welcome!