0

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!

Lee Mac
  • 15,615
  • 6
  • 32
  • 80
daveg
  • 1,051
  • 11
  • 24
  • 1
    Is your table a local or link table? I just tried it on a local table and the AutoNumber ID field automatically increments when I copy-pasted the same rows including its ID field. – mdialogo Nov 20 '18 at 23:55
  • 1
    Why would you 'cut/paste', did you mean 'copy/paste'? Is this 'autoincrement' an Autonumber type field? If so, copy/paste works fine on a local or link table. If this is not Autonumber, then how are you generating this ID? – June7 Nov 21 '18 at 01:51
  • Change = Insert and update. If you want specific behavior before inserting, use a `Before Change` trigger, and the `IsInsert` constant to test if the change is an insert. See [the docs on Before Change](https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/before-change-macro-event) – Erik A Nov 21 '18 at 08:59
  • Possible duplicate of [Access data macro get value from query (auto numbering)](https://stackoverflow.com/questions/51873857/access-data-macro-get-value-from-query-auto-numbering) – Erik A Nov 21 '18 at 09:02
  • It's just a table I created in a MS-Access DB. It didn't link to anything, so I guess it's "local" ? Yes, copy/paste (sorry). No, it wasn't an autonumber, but I thought it was. I changed that and this works now. Looks like I needed that slap upside the head to wake me up to checking this. Thanks ! – daveg Nov 21 '18 at 16:22

1 Answers1

0

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

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

This will work

Totakura SAMBASIVA RAO
  • 1