How to Determine the Next Auto-Numbering Value for a Field

[Phillip J. Allen]

Hi all,

I have a table with an “autonumber” primary key field that also acts as
a foreign key in 2 other tables. I would like to programmatically add a
new record to the first table and the other 2 child tables. The reason
for doing this is to copy one complete record (parent and child table
records) into a new record so the user can make a few modifications.

So how do I determine what will be the next auto-generated number for my
primary key? I visualize doing this schematically like:

1. Open a Transaction
2. Get the next autonumber value
3. Add a new record to my first table (have Access generate the primary key)
4. Add a new record to my 2 child tables using the “next autonumber
value” as the foreign keys
5. End Transaction
I have always resisted using auto-numbered primary keys in this type of
instance for this very reason. But in this situation I almost had to.
This database was an “observation database” and it’s a bit difficult to
ask people to manually keep track of how many observations they take.

Thanks,

Phillip J. Allen
e-mail: pa*****@attglobal.net

[Jon Trelfa]

Phillip J. Allen wrote:

1. Open a Transaction
2. Get the next autonumber value
3. Add a new record to my first table (have Access generate the primary
key)
4. Add a new record to my 2 child tables using the “next autonumber
value” as the foreign keys
5. End Transaction

I recently solved this one (with some pain and suffering). Here's how I
did it:

---------------start code------------------
'create a connection
set conn = server.createObject("ADODB.connection")
conn.Mode = adModeReadWrite
conn.Timeout = 20
conn.open "DSN","user","pass"

'create a recordset
set rs = Server.CreateObject("ADODB.recordset")
rs.ActiveConnection = conn
rs.CursorLocation = adUseServer
rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic
rs.Source = "table name"
rs.Open

'Add the new record
rs.AddNew
rs("field1") = "value"
rs("field2") = "value"
rs.Update

rs.MoveLast

'get the newly created autonumber from the recordset
AutoNumber = rs("Auto-Number-Field")

'close the recordset and kill the object
rs.close
set rs = nothing

-------------------end code--------------------

Then i just did a loop for the rest of the database entries.

I used my connection and recordset options the "long way", but I find it
easier to remember what I was doing when I revisit the code months
later. You can put the connection params and recordset params on one
line if you want.

HTH,

--
Jon Trelfa
There 10 kinds of people in this world...
Those who understand binary and those who don't

[rkc]

"Phillip J. Allen" <pa*****@attglobal.net> wrote in message
news:3f********@news1.prserv.net...

Hi all,

I have a table with an “autonumber” primary key field that also acts as
a foreign key in 2 other tables. I would like to programmatically add a
new record to the first table and the other 2 child tables. The reason
for doing this is to copy one complete record (parent and child table
records) into a new record so the user can make a few modifications.

So how do I determine what will be the next auto-generated number for my
primary key? I visualize doing this schematically like:

1. Open a Transaction
2. Get the next autonumber value
3. Add a new record to my first table (have Access generate the primary key) 4. Add a new record to my 2 child tables using the “next autonumber
value” as the foreign keys
5. End Transaction

You can use the recordsets LastModified and Bookmark properties to retrieve
the autonumber of a just entered record. Steps 2 and 3 in your outline
would
be reversed.

rs.AddNew
rs!LastName = "Updike"
rs!FirstName = "Eugene"
rs.Update

rs.Bookmark = rs.LastModified
Autonumber = rs.EmployeeID

[rkc]

"John Mishefske" <mi****@execpc.com> wrote in message
news:vm***********@corp.supernews.com...

rkc wrote:

You can use the recordsets LastModified and Bookmark properties to retrieve the autonumber of a just entered record. Steps 2 and 3 in your outline
would
be reversed.

rs.AddNew
rs!LastName = "Updike"
rs!FirstName = "Eugene"
rs.Update

rs.Bookmark = rs.LastModified
Autonumber = rs.EmployeeID

How about:

rs.AddNew
rs!LastName = "Updike"
rs!FirstName = "Eugene"
Autonumber = rs!EmployeeID
rs.Update

Which works fine inside Jet but ODBC?

I should mention that I was assuming DAO and a Jet database.

Your code would return the EmployeeID of the current record or
cause an error if it was an empty recordset.

I don't know whether .LastModified works with an ODBC connection.

[Phillip J. Allen]

Everyone,

Thanks. You have definately got me thinking on the right track. My
application exists as both native Access tables and sometimes ODBC
connection to PostgreSQL tables. Both I have to manage but the
PostgreSQL problem I have undercontrol and can manage.

Thank again

Phillip J. Allen

Phillip J. Allen wrote:

Hi all,

I have a table with an “autonumber” primary key field that also acts as
a foreign key in 2 other tables. I would like to programmatically add a
new record to the first table and the other 2 child tables. The reason
for doing this is to copy one complete record (parent and child table
records) into a new record so the user can make a few modifications.

So how do I determine what will be the next auto-generated number for my
primary key? I visualize doing this schematically like:

1. Open a Transaction
2. Get the next autonumber value
3. Add a new record to my first table (have Access generate the primary
key)
4. Add a new record to my 2 child tables using the “next autonumber
value” as the foreign keys
5. End Transaction
I have always resisted using auto-numbered primary keys in this type of
instance for this very reason. But in this situation I almost had to.
This database was an “observation database” and it’s a bit difficult to
ask people to manually keep track of how many observations they take.

Thanks,

Phillip J. Allen
e-mail: pa*****@attglobal.net