Obtain autonumber for new records in DAO

D

David Cleave

Hi all

If you use DAO to create a new record the code looks something like this:

Dim DatabaseName As DAO.Database
Set DatabaseName As CurrentDb
Dim TableName As DAO.Recordset
Set TableName = DatabaseName.OpenRecordset(“Name Of Tableâ€)
With TableName
..AddNew
![Field 1] = Value1
![Field 2] = Value2
..Update
..Close
End With

If the table has an autonumber primary key, is there a quick way to get this
new number? (I.E. to find out what it is, and store it in a variable or
something?)

Thanks

David
 
N

Nikos Yannacopoulos

David,

Right after the .AddNew, the autonumber value is available; so, if you
use a variable, say, vKey, to store it into, just modify your code like:

With TableName
..AddNew
vKey = .[AutonumberFieldName]
![Field 1] = Value1
![Field 2] = Value2
..Update
..Close
End With

HTH,
Nikos
 
K

Ken Snell [MVP]

Two ways to do it if you're using an autonumber primary key, because an
autonumber field gets its value as soon as a record is started.

The first example shows how to move the recordset back to the record that
was just modified, and then read the PKValue.

Dim DatabaseName As DAO.Database
Set DatabaseName As CurrentDb
Dim TableName As DAO.Recordset
Dim lngPKValue As Long
Set TableName = DatabaseName.OpenRecordset("Name Of Table")
With TableName
.AddNew
![Field 1] = Value1
![Field 2] = Value2
.Update
.Bookmark = .LastModified
lngPKValue = !AutonumberFieldName
.Close
End With


The second example shows how to read the value while the record is being
added:

Dim DatabaseName As DAO.Database
Set DatabaseName As CurrentDb
Dim TableName As DAO.Recordset
Dim lngPKValue As Long
Set TableName = DatabaseName.OpenRecordset("Name Of Table")
With TableName
.AddNew
![Field 1] = Value1
![Field 2] = Value2
lngPKValue = !AutonumberFieldName
.Update
.Close
End With
 
D

David Cleave

Genius! This will save me so much daft coding!

Cheers

David

Nikos Yannacopoulos said:
David,

Right after the .AddNew, the autonumber value is available; so, if you
use a variable, say, vKey, to store it into, just modify your code like:

With TableName
..AddNew
vKey = .[AutonumberFieldName]
![Field 1] = Value1
![Field 2] = Value2
..Update
..Close
End With

HTH,
Nikos

David said:
Hi all

If you use DAO to create a new record the code looks something like this:

Dim DatabaseName As DAO.Database
Set DatabaseName As CurrentDb
Dim TableName As DAO.Recordset
Set TableName = DatabaseName.OpenRecordset(“Name Of Tableâ€)
With TableName
.AddNew
![Field 1] = Value1
![Field 2] = Value2
.Update
.Close
End With

If the table has an autonumber primary key, is there a quick way to get this
new number? (I.E. to find out what it is, and store it in a variable or
something?)

Thanks

David
Click to expand...
Click to expand...
 
D

David C. Holley

You should be able to by checking the field value containing the
Autonumber. Syntax should be
TableName.Fields("fieldName")

.. You may have to place it immediately after the .Update statement. Let
me know if it works, I'll be visiting tis soon myself.

David H
 
Top