How to specify the max of a field +1 when defining a SetField value in a Before Insert trigger?

Question

I have a database with a table that has as its primary key an autoincrement number field I call id.

In the datasheet view, I want to create some new records using cut/paste of some existing records, then update/modify those records. The paste doesn't work because the id values of the cut records already exist and id is a primary key.

I want to set the value for id to be the max(id)+1 before the insert to avoid the conflict.

I tried to do this with a trigger (using the Before Change event). But I don't know how to specify max(id)+1.

I tried max([id])+1 and max([id+1]), no luck. I also tried to stuff a SQL statement in there, something like select max(id)+1 from thetable. That didn't work.

Does anyone know how to specify max(id)+1 in MS Access lingo such that this might work? I don't think max is a built-in that Access knows about.

Is this a fool's errand? Can this be made to work?

Is there a better way to approach this, maybe with VBA?

Finally, is there a way to stuff a SQL expression in the value field?

I noticed that MS Access supports Before Change and Before Delete, but not Before Insert. Is there the equivalent of a Before Insert trigger for this database?

Thanks!

Answer 1

Using the "Before Change" event you can set the value as

DMax("[Id]","[YourTableName]")+1

This will work