Auto Increment a numeric field

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
You cannot do this in a table
Click to expand...

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.

--
 
Back
Top