List Box Row Number

[Huckle]

I'm would like to have a column in a list box that shows the row number. For
example, the first item in the list would have a "1" in the column, the
second item would have a "2" in the column. Hopefully these numbers could be
dynamic so if an item is added, removed or moved in the list box, it will
update the numbers.

The data for the list box comes from a table. I'm at a dead end and may
just add a new field to the table called ItemNumber and loop through the data
in the table and insert the number.

Basically, I'm looking for a running sum based on the number of rows in the
list box...

Thanks

 

[Dirk Goldgar]

I'm would like to have a column in a list box that shows the row number.
For
example, the first item in the list would have a "1" in the column, the
second item would have a "2" in the column. Hopefully these numbers could
be
dynamic so if an item is added, removed or moved in the list box, it will
update the numbers.

The data for the list box comes from a table. I'm at a dead end and may
just add a new field to the table called ItemNumber and loop through the
data
in the table and insert the number.

Basically, I'm looking for a running sum based on the number of rows in
the
list box...

Assuming that all the items in your list box's rowsource query are unique,
and that you can order these rows by a one or more key fields, you can
assign row numbers by counting, in the query, the number of rows that would
precede the current one. For example, suppose you have this rowsource query
now:

SELECT
ID,
Description
FROM MyTable
ORDER BY Description;

Assuming that Description is unique, you could add a row-number column like
this:

SELECT
ID,
Description,
(SELECT COUNT(*) FROM MyTable T
WHERE T.Description <= MyTable.Description)
AS RowNumber
FROM MyTable
ORDER BY Description;