Adding a record number field

[Guest]

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

 

[Stefan Hoffmann]

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 <--

 

[Guest]

A faster way is

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

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 <--

 

[Stefan Hoffmann]

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 <--