Access : How to know what is the next autonumber generated for my primary key

[Robert_Mathews99]

I have a column whose column type is autonumber. I want to know what will be the next autonumber generated. Like if I am deleting some records in between , I am loosing the track of the count..
Is there any way for knowing the number automatically.

RM

[Hastur]

Hi,

One can never be totally categoric, but I think that knowing in advance what will be the next generated key is not possible. Anyway, in a database, a key should never be anticipated, a primary key is made to be searched, not anticipated.
If you use the Recordset.AddNew

method to insert your data, you can retreive the key value even if you have not call the Recordset.Update

method.

I suppose this not very helpfull for you, sorry for that, but may I ask you why you want to know in advance the value of the next generated key ?

Regards.
Hastur.

[Robert_Mathews99]

Hi,

Thanks for your kinda reply. The reason for knowing the next auto number in advance is that, this ID should be stored in the child table; and the queries are sending in a batch mode and hence I cannot presume the ID which is being generated in the PARENT table is 1 plus to the maximum number in the database (in case if the user deletes the last ten records updated).

Regards

RM

I think I had similar scenario:
On the same screen I have several controls displaying parent record (Order)and Grid for child records (Order Details). Whith displaying this hierarchy I did not have any problem. But when I needed to add a new Order with Details I had to do it step by step. My boss hates to make extra clicks (Update button for Orders). So, I analized if all required fields were filled on either KeyPress or some other events (I don't remember) and when everything was entered for Order I added the new record into the Order table, came to that record, got its ID and used it in a OrderDetail record as foreign key. Before new record for Order entered Grid control is disabled. After new Order is in a table, Grid is enabled and allows to enter details.
Normal DB design (referential integrity) doesn't allow you to enter a new record into the OrderDetails table for not existing Order. So, even if you know what ID will be generated, you cannot use this ID in an OrderDetails table. Or don't pay attention on DB design rules and create ugly DB (BTW it's much easier). You will probably have a lot of problems later (orphaned records, duplicates, details from John's order belong now to Steve's order and so on)
Vlad

[czimmerman]

If you are using DAO, you can use the LastModified property to move to the record you just inserted, and then read the ID property. If you are using ADO, you can create a new recordset, ordered by DateModified field (if you have one), and then read the ID of the first record.

Charlie Zimmerman
http://www.freevbcode.com

[Hastur]

Hi,

I suggest you, in your case, to forget the SQL queries and to use Visual Basic. Create a workspace object, and instanciate it to the current workspace (Workspaces(0)), create a recordest pointing to the table in which you want to insert your datas. You can insert data in a recordset by calling the method MyRecordset.AddNew, and validate it with a MyRecordset.Update. However, you can read the identity key even if you have not called the .Update method, and use it as you wish.
If you need to perform a lot of inserts which should be all cancelled if an error occurs, then call the method MyWorkspace.BeginTrans before your inserts, and the method MyWorkspace.CommitTrans after. Do an error interruption routine (On Error Goto MyLabel), and call the method MyWorspace.RollBack in it.

So you algorithm may look like this :

[Begin Sub]
On Error Goto MyError

MyWorspace.BeginTrans
[For Statement]
MyRecordset.AddNew
MyRecordset.Fields("MyField1") = "MyValue"
MyRecordset.Fields("MyField2") = "MyOtherValue"
...
MyKey = MyRecordset.Fields("MyIdentKey")
MyRecordset.Update
MyRecordset2.AddNew
MyRecordset2.("MyChildKey") = MyKey
MyRecordset2.Fields("MyWhatEver") = "WhoKnows"
MyRecordset2.Udpate
[Next Statement]
MyWorkspace.CommitTrans

[Exit Sub]

MyError:
MyWorkspace.RollBack
[End Sub]

In this algorithm, some datas are inserted in a child table, according to the new ident key in the parent table. If an error occurs, all inserts in the two tables are cancelled.

Hope this can help you.

Best regards.
Hastur.

[Lonely Wolf]

if the key is generated adding 1 to the last inserted, you can add & update your recordset, so close the recorset and open it again ordering by key.
Now using .movelast you can go to the last record, retrieve the key and key+1 will be the next.
All this using DAO.

I'm sorry for my english.
Bye

[Gianandrea]

If you do not have many updates, and (as i think to have understood) your field is a key field...something like 'SELECT MAX(myautofield) From MyAutoTable' will work fine...
If you have many updates, forget the autonumber feature and use an external counter table...

[pauel]

I realy cannot se a problem here, or am I missing something?

if you have tblOder and tblOdeDitales then:

- Open recordset for tblOder

rst.OpenRecordset(.....)

workspace.BeginTrans
rst.AddNew
rst.Update ' after this line you logicaly created record and can get assigned value of ID
dim a as long
a=rst.Fields("ID") ' that is a future ID
workspace.rollback 'cancelling update, but you still have ID