Get CustomerID of New Record After Recordset.Addne w

[Harold]

Sat I have a customers table with the fields CustomerID and Customer and I use
the recordset.addne w method to add a new record to the table. What is the best
way to get the CustomerID of the new record after recordset.updat e is executed?
Recordset!Custo merID doesn't work because the recordset bookmark is on the first
record. Moving to the last record doesn't work if the recordset is based on a
query and the recordset is sorted.

Thanks!

Harold

[Allen Browne]

If this is a DAO recordset, Access sets the LastModified bookmark, so:
rs.Bookmark = rs.LastModified
Debug.Print rs!CustomerID

If this data is in an Access table, the autonumber is assigned as soon as
you start to add the record, so you can even get the number before the
Update if you wish:
rs.AddNew
Debug.Print rs!CustomerID

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Harold" <hm*****@earthl ink.net> wrote in message
news:3v******** **********@news read2.news.atl. earthlink.net.. .

Sat I have a customers table with the fields CustomerID and Customer and I use the recordset.addne w method to add a new record to the table. What is the best way to get the CustomerID of the new record after recordset.updat e is executed? Recordset!Custo merID doesn't work because the recordset bookmark is on the first record. Moving to the last record doesn't work if the recordset is based on a query and the recordset is sorted.

Thanks!

Harold

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If you're using DAO recordsets you can use the .LastModified bookmark to
get to the last record entered. Here is an example code from the Help
article LastModified Property (DAO):

Function AddName(rstTemp As Recordset, _
strFirst As String, strLast As String)

' Adds a new record to a Recordset using the data passed
' by the calling procedure. The new record is then made
' the current record.
With rstTemp
.AddNew
!FirstName = strFirst
!LastName = strLast
.Update
.Bookmark = .LastModified
End With

End Function

After ".Bookmark = .LastModified" you could also do something like this:

.Bookmark = .LastModified
lngCustomerID = !CustomerID

lngCustomerID would be the Customer ID of the last entered record. You
could return the lngCustomerID value as the function's return value.

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQQkjx4echKq OuFEgEQIvqACgv5 pVWgWcM8NRwHUhp 85K7Ck8iqkAn3/Y
ZTnYQlzkbtDXnba L0Le4ZZaz
=z3aa
-----END PGP SIGNATURE-----
Harold wrote:

Sat I have a customers table with the fields CustomerID and Customer and I use
the recordset.addne w method to add a new record to the table. What is the best
way to get the CustomerID of the new record after recordset.updat e is executed?
Recordset!Custo merID doesn't work because the recordset bookmark is on the first
record. Moving to the last record doesn't work if the recordset is based on a
query and the recordset is sorted.