How to specify the max of a field +1 when defining a SetField value in a Before Insert trigger?
up vote
0
down vote
favorite
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!
ms-access access-vba ms-access-2007
edited 2 days ago
Lee Mac
2,79621036
asked 2 days ago
daveg
1978
add a comment |
up vote
0
down vote
favorite
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!
ms-access access-vba ms-access-2007
edited 2 days ago
Lee Mac
2,79621036
asked 2 days ago
daveg
1978
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
2 days ago
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
2 days ago
Change = Insert and update. If you want specific behavior before inserting, use aBefore Changetrigger, and theIsInsertconstant to test if the change is an insert. See the docs on Before Change
– Erik von Asmuth
yesterday
Possible duplicate of Access data macro get value from query (auto numbering)
– Erik von Asmuth
yesterday
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
yesterday
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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!
ms-access access-vba ms-access-2007
edited 2 days ago
Lee Mac
2,79621036
asked 2 days ago
daveg
1978
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!
ms-access access-vba ms-access-2007
ms-access access-vba ms-access-2007
edited 2 days ago
Lee Mac
2,79621036
asked 2 days ago
daveg
1978
edited 2 days ago
Lee Mac
2,79621036
asked 2 days ago
daveg
1978
edited 2 days ago
Lee Mac
2,79621036
edited 2 days ago
Lee Mac
2,79621036
edited 2 days ago
Lee Mac
2,79621036
2,79621036
asked 2 days ago
daveg
1978
asked 2 days ago
daveg
1978
asked 2 days ago
daveg
1978
1978
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
2 days ago
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
2 days ago
Change = Insert and update. If you want specific behavior before inserting, use aBefore Changetrigger, and theIsInsertconstant to test if the change is an insert. See the docs on Before Change
– Erik von Asmuth
yesterday
Possible duplicate of Access data macro get value from query (auto numbering)
– Erik von Asmuth
yesterday
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
yesterday
add a comment |
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
2 days ago
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
2 days ago
Change = Insert and update. If you want specific behavior before inserting, use aBefore Changetrigger, and theIsInsertconstant to test if the change is an insert. See the docs on Before Change
– Erik von Asmuth
yesterday
Possible duplicate of Access data macro get value from query (auto numbering)
– Erik von Asmuth
yesterday
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
yesterday
1
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
2 days ago
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
2 days ago
1
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
2 days ago
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
2 days ago
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– Erik von Asmuth
yesterday
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– Erik von Asmuth
yesterday
Possible duplicate of Access data macro get value from query (auto numbering)
– Erik von Asmuth
yesterday
Possible duplicate of Access data macro get value from query (auto numbering)
– Erik von Asmuth
yesterday
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
yesterday
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
yesterday
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53402669%2fhow-to-specify-the-max-of-a-field-1-when-defining-a-setfield-value-in-a-before%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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
2 days ago
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
2 days ago
Change = Insert and update. If you want specific behavior before inserting, use a
Before Changetrigger, and theIsInsertconstant to test if the change is an insert. See the docs on Before Change– Erik von Asmuth
yesterday
Possible duplicate of Access data macro get value from query (auto numbering)
– Erik von Asmuth
yesterday
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
yesterday