Re: AutoInc in mdb

Thats easy paul
But my question was the next one which is complicated if there is a way.?
PS. to get the last number do
close
open
last
and read the field!!

"Paul Scott" <paul.scott@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:op.t79fmav6jpqj2l@xxxxxxxxxxxxxxxxxxxxxxxx
On Wed, 19 Mar 2008 08:58:47 -0000, limelect <limelect@xxxxxxxxxxxx> wrote:

I would like to know the next number in a mdb autoincrment field.
However just taking the last number+1 is not correct
since if you delete a record the next one will be +2
and so on

While I'm not sure how you can get the NEXT auto-increment number (the one
which /would/ be allocated next), if you want to get the LAST
auto-increment number (the one which has just been allocated), In Access
the field is available from the query row immediately after the POST.

The following pseudo-code works for me...

AdoQuery.Insert

// Set up the other fields in the row
AdoQuery.FieldByName ( 'OtherField' ).Value := ....

AdoQuery.Post

// Now, what number was actually auto-allocated?
LastNumber := AdoQuery.FieldByName ( 'AutoIncField' ).AsInteger ;


(The syntax may not be exactly correct, I'm extracting this from my
wrapper procedures)


P.S i tried 'select @@IDENTITY as [ID]' not wirking

While that does work on MS SQLserver, it might not give you /exactly/ the
result you expect.

"select scope_identity() as [NewID]" is more predictable - see Books
Online for more information.

--
Paul Scott
Information Management Systems
Macclesfield, UK.


.