number items in a list box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a list box on my form with 100 items in the list. I would like an
additional column which would number those items 1-100. I tried using the
"autonumber" but if I were to delete one of the records in the list, say
number 25, the autonumber goes too. I would like the number to re populate
itself and show 1-99. Is this possible?

Thanks,
 
How do you fill the listbox? with a value list or with a query? (in other
words, what is the listbox's Row Source?)
 
Ken,

Row Source is a query.



Ken Snell (MVP) said:
How do you fill the listbox? with a value list or with a query? (in other
words, what is the listbox's Row Source?)
Click to expand...
 
Then you may be able to use a slight variation of the "ranking" query. Here
is some generic code to do this:

SELECT R.*, ((SELECT Count(*) FROM Tablename AS T
WHERE T.PrimaryKeyField < R.PrimaryKeyField) + 1)
AS TheNumber
FROM Tablename AS R
ORDER BY ((SELECT Count(*) FROM Tablename AS T
WHERE T.PrimaryKeyField < R.PrimaryKeyField) + 1);

You may need to tweak this to work with a different sort order than the
primary key.
--

Ken Snell
<MS ACCESS MVP>
 
Back
Top