Table numbering

[ryan.fitzpatrick3]

I know there is autonumbering in tables, how can I create a column
where on the main table for all new items it assigns a unique number
starting at 1, then 2, etc. I know the auto number does this but when
i link up a subtable the autonumber goes into the subtable. I would
just like to have the number go in sequential order upon new entry,
this way I can tell how many entries I have created.

Ryan

 

[Klatuu]

autonumber fields don't quite do that. They will assign sequential values if
you make that choice in design view, but there will be gaps. If you delete a
record, that number is gone. If you start a new record, but undo it, that
number is gone.

But, to answer your question, you can't do it directly in the table, but you
can in a form. Assume you have a field named [RecNo] in your table and on
your form, you have a control named txtRecNo to which [RecNo] is bound. The
idea is to use the Default Value property of txtRecNo to look up the current
highest number and add 1 to it:

=Nz(DMax("[RecNo]","MyTableName"),0) + 1

But, that still doesn't resolve the issue of deleted records.