Getting the next Insert ID

Caleb

New member
Local time
Tomorrow, 01:16
Joined
Apr 8, 2003
Messages
7
hey,

Can i use some kind of function to get the next Insert ID for an Auto Increcement field?

I need it because im doing some complicated inserts inside a Form and i gotta know the next value that the auto-inc field is going to have.

Is this possible using some kind of function ?
 
You could use a totals query with the Max function to get at the largest currently assigned number in a field is. There's no guarantee though, that adding 1 to it will give you the next number to be assigned. For example, if someone created a record, then deleted it, unless you compacted the database first, Access would skip that record number the next time a new record is created.
 
I have already tried the method you are talking about, using DCount()+1.

And yes i know that if records are deleted it is all messed up.

That's why i came here ;)
 
I don't know of a way to definitively find the next number to be assigned, but there is a trick for getting to it, sort of.

The next autonumber to be given out is usually one more than the maximum number in the table. But any time you either delete the "maximum" record or cancel out of creating a new record, Access "keeps" that autonumber and increments to the following one the next time you try to add a new record. In that case, the next number to be given out will be the maximum record + 2.

Assuiming that you're not in danger of running out of numbers (which ends at over 2 trillion), you can open the data table as a recordset, go to add a new record, record the current value of the autoincrement field, then cancel out of the update and close the recordset without making any changes. That will tell you what number Access would've given the field. Just add 2 and you'll have the next number to be given out. Not elegant, but it might work for you.

Hopefully, someone can tell us the right way to get the answer.
 
Exactly why do you want the number BEFORE the row is inserted? If you have Referential Integrity enforced (and you should), you can't insert related records before you insert the one-side record. When you need to insert multiple related records - Insert the record with the autonumber, obtain the value, then insert any related records.
 
Ok, look:

When my order products form closes i have to update 2 tables:
1) tbl_Orders
2) tbl_Orders_Products.

they both have a common field called Order_ID.

When the form closes, i first run a query to add a row into tbl_Orders which creates a new Order_ID (auto-increcement).
Then i run a query that creates a row/rows in tbl_Orders_Products that uses that new Order_ID.

So i somehow have to get the Order_ID!

This is all ran inside a Macro.. so maybe right after running the query to create the Order_ID i could do something to get the LAST inserted row to get the Order_ID?
 
Caleb,

Access does all this work for you.

You need to create a main form that uses your Tbl_Orders and the recordsource, and a subform that uses Tbl_Orders_Products as it's recordsource.

If you create the correct Master / Child relationship, Access will create the OrderID in each table as required.

HTH

Brad.
 
hey,

Im afraid that i cannot change the layout of the form.

I must have everything in one form and none of the controls are assigned to any fields.

After the textboxes have been filled the queries should do the rest.

This is the only way i have to do it.
 
Did someone tell you that you had to do it this way? Did they give you a reason? The biggest benefit of using Access is bound forms. If you have to do all your own coding to manage a form, you might as well be working in VB or C++.

If you were using a bound form, you would simply refer to the control after the record was inserted to obtain the value of the autonumber.

To get the number in an unbound form, you can't use macros to run queries, you will need to write DAO or ADO. Use the .AddNew method to insert a new row to the main form table. After the .Update method, you should be able to refer to the autonumber field in the recordset. MAKE ABSOLUTELY CERTAIN that you do NO other table activity before capturing the newly assigned autonumber. If you move off the current record, you will loose the ability to determine the autonumber of the row you just inserted. Using DMax() to obtain the highest key value is iffy at best in a multi-user environment.
 
Finding the autonumber just added.

After the .Update method, you should be able to refer to the autonumber field in the recordset.

This is what I need to acheive but don't know how to code it can you assist?

Thanks
 
Just reference it as you would any other recordset field.

SomeVariable = rst!YourAutonumberKeyValue

Make sure that you haven't moved the current record pointer or you'll get the keyvalue of whatever record is now the current one.
 

Users who are viewing this thread

Back
Top Bottom