numbering records

  • Thread starter Thread starter Steven Greenberg
  • Start date Start date
S

Steven Greenberg

Greetings,

Is there any way to put in a calculated column in a query that will start
at 1 and increment by 1 (as a counter) so that when I use that query in a
subform, I can take that field and use it to number each record on the
subform?
It is like using a text box who's control source is "=1" and its property
set to running sum to number records on a report. there is no way to do
that on a form, but I thought I can dummy up an expression in the form's
query. Any suggestions? I can do VBA coding quite well.
Thanks
Steve
 
Steven said:
Is there any way to put in a calculated column in a query that will start
at 1 and increment by 1 (as a counter) so that when I use that query in a
subform, I can take that field and use it to number each record on the
subform?
It is like using a text box who's control source is "=1" and its property
set to running sum to number records on a report. there is no way to do
that on a form, but I thought I can dummy up an expression in the form's
query.
Click to expand...


This is typically done by using a subquery, but it does
require a sort field that provides a unique ordering of the
records (i.e. determines first, second, etc).

SELECT T.*,
(SELECT Count(*)
FROM table As X
WHERE X.sortfield <= T.sortfield
) As Rank
FROM table As T
ORDER BY T.sortfield
 
there some code involved but there you go, it better to do it in your form
create a field, bound the field to a function =Acc()
function Acc()
Acc=MyCount 'declare it in your form, init the number to 1 on load form
Acc=Acc+1
end function

that will give you the right order, now the problem start when you delete
records in the middle or add records, in any of this events you will need to
init MyCount to 1 and requery the records.
I hope it will work, try and QA and see if any other problems
 
Thanks, I'll check it out. seems like everything I have tried so far
either gives me an #error or #name? or blank. the closest one gave me
a number but didn't increment it. they all said "2". I'll take a look
at the website. Thanks
Steve
Click to expand...

Well, it works perfectly, I was working on code that was very similar to
the rownum() code, would have probably gotten it eventually if I knew I was
headed in the right direction as I have tried many things so far. I think
my database is as "tweaked" as I want. Now the drudgery of data validation
like "starting date" and you type your name!
Thanks to all yet again.
Steve
 
Back
Top