Last insert auonumeric

[kunguito]

Is there any way, in ACCESS, to automaticaslly get the autonumeric value generated when inserting a new register?

Thanks

[CreganTur]

Are you talking about the AutoNumber data type available in tables? If so, then what do you mean by "to automatically get the automatic value"?

[kunguito]

Ok,
I use a SQL INSERT.
The table I insert into has a an index which is autonumeric. So, apart from the data included in the INSERT a new index value will be automatically generated.
What's the simplest way to get this index value?
Is there some straightforward sql stuff? Or do a new query is necessary?

[CreganTur]

What's the simplest way to get this index value?
Well, AutoNumber always increases by one, so when you insert a new record, then the AutoNumber value for the newest record will be the greatest number in that field.

I can't think of any way to get this value via SQL... but it can easily be done with a DAO (Data Access Object) connection.

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("***Insert SQL Statement Here***")

rst.MoveLast '<<<Move to last record
Debug.Print rst.Fields(0) '<<<Print value to Immediate Window

rst.Close
Set rst = Nothing
db.Close
Set db = Nothing

The SQL you'll want to insert is a simple SQL SELECT statement that will select only your AutoNumber field. This will move to the very last field in the recordset, which should be the record with the largest AutoNumber value.

HTH:thumb

[Mavyak]

DLookup?

[CreganTur]

DLookup?

It would depend on what his primary key field is. If you do a DLookup on a non PK field, and there are multiple entries existing in the table, then it's not gauranteed that it'll pull the record you want.



If more than one field meets criteria, the DLookup function returns the first occurrence.



I assume that since he's using an AutoNumber field, then that field is the table's PK. Using DAO's MoveLast method could be more Explicit in this case. :dunno

[Mavyak]

Any autonumber field can be considered at least an alternate key. The following works, but I don't know if it's the most efficient way to do it.

Sub FindMaxAutoNumber()
Dim MyInteger As Integer
MyInteger = DLookup("[AutoNumberField]", "MyTable", "[AutoNumberField]=(SELECT Max([AutoNumberField]) FROM MyTable)")
MsgBox MyInteger
End Sub

[kunguito]

Thanks everyone,
What do you think about that?:think:

SELECT Last(Table.id_Table) FROM Table

[CreganTur]

Thanks everyone,
What do you think about that?:think:

SELECT Last(Table.id_Table) FROM Table

I didn't know about the First/Last Functions in SQL (learn something new everyday!)

The concept looks solid.

I'd just suggest running a few tests and see if it returns the value you're expecting.

Also, you might want to look at using a WHERE clause in there, just for added peace of mind.