Auto Increment a numeric field

[Guest]

Is there a way to automatically increment a numeric field when a new record
is created? I want to do this with an existing field, if possible.

For example:
"LogNumber" = 526 in the last record. When a new record is created, I want
"LogNumber" to automatically be entered as 527. The capability of being
edited would be helpful if that's possible. I would like the system to check
the "LogNumber" of the last record, then increment that number by one and
enter the new number into the new record.

 

[Ken Snell \(MVP\)]

You cannot do this in a table, but you can do it in a form that is bound to
the table (or bound to an updatable query that includes that table).

One uses the DMax domain function to get the maximum value for a field, and
then adds 1 to that value:

NextValue = Nz(DMax("FieldName", "TableName"), 0) + 1

 

[Jamie Collins]

You cannot do this in a table

But you could write a constraint to ensure there are no gaps in the
sequence, which would at least ensure the incrementing was done (via a
form, via Excel, via OLE DB, via ODBC, etc) e.g.

CREATE TABLE Categories (
CategoryID INTEGER NOT NULL UNIQUE,
CategoryName VARCHAR(15) NOT NULL UNIQUE,
Description VARCHAR(80) NOT NULL,
CONSTRAINT CategoryID__sequence
CHECK (CategoryID = (
SELECT COUNT(*)
FROM Categories AS C2
))
)
;

You could also write a 'helper' SQL procedure to increment the value
e.g.

CREATE PROCEDURE AddCategory
(
arg_CategoryName VARCHAR(15),
arg_Description VARCHAR(80)
)
AS
INSERT INTO Categories (CategoryID, CategoryName, Description)
SELECT IIF(MAX(CategoryID) IS NULL, 0, MAX(CategoryID)) + 1,
arg_CategoryName, arg_Description
FROM Categories;

You could additionally remove the 'insert' permissions from the table,
forcing all inserts to be done via the procedure, and a similar
approach could be taken for updates.

Jamie.

--