List Box Total

[Lee-Anne Waters via AccessMonster.com]

Hi All,

hopefully an easy question but i cant seem to get this to work.

i have a list box that shows a dollar figure in column 7.

what i would like to do is see a txt box calculate the total sum of this
column.

the list box is re-queried each time different criterea is selected.

thanks

Lee-Anne

 

[Graham R Seach]

Lee-Anne,

There are two ways of doing this. The first involves cycling through the
listbox's dataset, adding up the value of column 7 as it gets to each
record. This might work OK if there aren't many records, but if you have a
lot of records showing in the listbox, it might be better to use a query to
get the sum.

Option 1:
Dim lCount As Long
Dim curSum As Currency

For lCount = 1 To Me.lstListBox.ListCount
curSum = curSum + CCur(Me.lstListBox.Column(7, lCount))
Next lCount

Me.txtSum = curSum

Option 2:
Dim curSum As Currency

curSum = Nz(DSum("[myCurrencyColumn]", "[myTable]", "criteria = 123"),
0)
Me.txtSum = curSum

Option 2A (faster on large tables):
Dim rs As DAO.Recordset
Dim curSum As Currency
Dim sSQL As String

sSQL = "SELECT Sum(myCurrencyColumn) As MySum " & _
"FROM myTable WHERE criteria = 123"

Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)
Me.txtSum = Nz(rs!MySum, 0)

rs.Close
Set rs = Nothing

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Lee-Anne Waters via AccessMonster.com]

Hi Graham.

thanks but i have one more question

this is what i have thus far using your option 2


Dim curSum As Currency
curSum = Nz(DSum("[TotalValue]", "[qryRCTI_B]", "criteria = me.lstTechs"),
0)
Me.txtTotal = curSum

should i always be referencing a table or is a query ok.
and
how do i use the other list box as a set criterea. column (0) is the unique
record

many thanks
Lee-Anne

Lee-Anne,

There are two ways of doing this. The first involves cycling through the
listbox's dataset, adding up the value of column 7 as it gets to each
record. This might work OK if there aren't many records, but if you have a
lot of records showing in the listbox, it might be better to use a query to
get the sum.

Option 1:
Dim lCount As Long
Dim curSum As Currency

For lCount = 1 To Me.lstListBox.ListCount
curSum = curSum + CCur(Me.lstListBox.Column(7, lCount))
Next lCount

Me.txtSum = curSum

Option 2:
Dim curSum As Currency

curSum = Nz(DSum("[myCurrencyColumn]", "[myTable]", "criteria = 123"),
0)
Me.txtSum = curSum

Option 2A (faster on large tables):
Dim rs As DAO.Recordset
Dim curSum As Currency
Dim sSQL As String

sSQL = "SELECT Sum(myCurrencyColumn) As MySum " & _
"FROM myTable WHERE criteria = 123"

Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)
Me.txtSum = Nz(rs!MySum, 0)

rs.Close
Set rs = Nothing

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

Hi All,

[quoted text clipped - 10 lines]

Lee-Anne

 

[Graham R Seach]

Lee-Anne,

Referencing a query is fine; it doesn't have to be a table.

"criteria" is a word I used to demonstrate that a filter can be applied to
the DSum query to specify the records to be included in the sum. If you want
to use the bound column of lstTechs as the filter, you would use "criteria =
me.lstTechs", but you should change the word "criteria" to the name of the
field to which lstTechs refers.

Also, since lstTechs is not an object that the database engine understands,
you should place it outside the query. For example:
curSum = Nz(DSum("[TotalValue]", "[qryRCTI_B]", "somefield = " &
me.lstTechs), 0)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

Hi Graham.

thanks but i have one more question

this is what i have thus far using your option 2


Dim curSum As Currency
curSum = Nz(DSum("[TotalValue]", "[qryRCTI_B]", "criteria = me.lstTechs"),
0)
Me.txtTotal = curSum

should i always be referencing a table or is a query ok.
and
how do i use the other list box as a set criterea. column (0) is the
unique
record

many thanks
Lee-Anne

Lee-Anne,

There are two ways of doing this. The first involves cycling through the
listbox's dataset, adding up the value of column 7 as it gets to each
record. This might work OK if there aren't many records, but if you have a
lot of records showing in the listbox, it might be better to use a query
to
get the sum.

Option 1:
Dim lCount As Long
Dim curSum As Currency

For lCount = 1 To Me.lstListBox.ListCount
curSum = curSum + CCur(Me.lstListBox.Column(7, lCount))
Next lCount

Me.txtSum = curSum

Option 2:
Dim curSum As Currency

curSum = Nz(DSum("[myCurrencyColumn]", "[myTable]", "criteria = 123"),
0)
Me.txtSum = curSum

Option 2A (faster on large tables):
Dim rs As DAO.Recordset
Dim curSum As Currency
Dim sSQL As String

sSQL = "SELECT Sum(myCurrencyColumn) As MySum " & _
"FROM myTable WHERE criteria = 123"

Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)
Me.txtSum = Nz(rs!MySum, 0)

rs.Close
Set rs = Nothing

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

Hi All,

[quoted text clipped - 10 lines]

Lee-Anne

 

[Lee-Anne Waters via AccessMonster.com]

thanks Graham your advise has been very helpful...

Lee-Anne
from sunny perth

Lee-Anne,

Referencing a query is fine; it doesn't have to be a table.

"criteria" is a word I used to demonstrate that a filter can be applied to
the DSum query to specify the records to be included in the sum. If you want
to use the bound column of lstTechs as the filter, you would use "criteria =
me.lstTechs", but you should change the word "criteria" to the name of the
field to which lstTechs refers.

Also, since lstTechs is not an object that the database engine understands,
you should place it outside the query. For example:
curSum = Nz(DSum("[TotalValue]", "[qryRCTI_B]", "somefield = " &
me.lstTechs), 0)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

Hi Graham.

[quoted text clipped - 69 lines]