Count Rows in Listbox

[tEkHEd]

Hi.

Was wondering if there is a way that I can count the entries in a given listbox?

I have a listbox (lstComponents) that shows records dependant upon a combobox:

select * from tblMyTable
group by tblMyTable.MyField1, tblMyTable.MyField2
Having (tblMyTalbe.MyField)=[Forms]![frmMyForm]![cboMyCombo]))

The results are displayed on a form, and show 3 columns (the lastone hidden)

Column 1 in lstComponents is labelled SMS ID
Column 2 in lstComponents is labelled Package
Column 3 in lstComponents is labelled Tot (not sure if needed, as this column just has the number 1 listed)

I want to be able to display the total number of rows that appear in my listbox in a text box (txtTotals)

I don't seem to be able to reference the hidden column to do something like =Sum(me.lstComponents.Tot)

Can anyone help with this please?

Thanks in adv..

 

[erezmor]

hi there
you can simply use lstComponents.ListCount to return the number of rows in the listbox (and i think that means you dont need column 3)
good luck,
Erez.

 

[tEkHEd]

That's the ticket... thought it would be simple

thanks for the help.. just a quick point to note though, if you have a header on your listbox, this also counts as a row, so you will find that the total is always one more than visible.

to get around this, I did the following:

=(([lstComponents].[ListCount])-1)

The only issue that I have now is that I can't seem to get this to auto update even after coding an on change event for the combo and/or listbox as

Me.txtTot.Requery

 

[tEkHEd]

Got it now.. I had the formulae entered in the DefaultValue rather than the ControlSource..