Adding a record number field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

The Chomp - 16 Nov 2006 11:00 GMT

Good Day;

I would like to add record number field to a table. I figure all I have to
do is get the number of the last record and add "1" to it. Small problem, not
sure what the code would be to do this.
Thanks for any help

Chomp

Reply to this Message

Stefan Hoffmann - 16 Nov 2006 11:36 GMT

hi,

> I would like to add record number field to a table. I figure all I have to
> do is get the number of the last record and add "1" to it. Small problem, not
> sure what the code would be to do this.

You can create a number on the fly:

SELECT (
SELECT Count(*)
FROM Table i
WHERE i.CK < o.CK
) AS RecNo, *
FROM Table o
ORDER BY o.CK

CK is a candiate key field, which identifies a record. It can be the
primary key. But the ORDER BY fields must be the same.

When you really like to store such a number, then use the Form_Update event:

If Me.NewRecord Then
Me![RecNo] = Nz(DMax("RecNo", "Table"), 0) + 1
End If

mfG
--> stefan <--

Reply to this Message

Klatuu - 16 Nov 2006 16:04 GMT

A faster way is

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

> hi,
>
[quoted text clipped - 22 lines]
> mfG
> --> stefan <--

Reply to this Message

Stefan Hoffmann - 16 Nov 2006 16:12 GMT

hi,

> A faster way is
> lngNewRecNo = Nz(DMax("[RecNo]", "TableName"), 0) + 1

Why do you think, it's faster?

>> When you really like to store such a number, then use the Form_Update event:
>> If Me.NewRecord Then
>> Me![RecNo] = Nz(DMax("RecNo", "Table"), 0) + 1
>> End If

mfG
--> stefan <--

Reply to this Message