Most recently recreated autonumber.

[hooi]

Hi,

If I have a VBA that creates a new record eg:

Dim db As Database
Dim rsNewRecord As Recordset

Set db = CurrentDb()
Set rsNewRecord = db.OpenRecordset("uService", dbOpenDynaset)

rsNewRecord.AddNew
rsNewRecord("ContractID") = Me.ContractID
rsNewRecord("DateReported") = Me.Received
rsNewRecord.Update
rsNewRecord.Close

This "uService" table has a autonumber field "ServiceID" which I need to know when a new record is created. How can I find out the ServiceID?

Thanks for helping.

 

[WayneRyan]

hooi,

After you do the Insert the autonumber is available by
rsNewRecord!AutoNumberField.

It is the same with a form.

Wayne

 

[hooi]

Thank you very much Wayne. Got it now.

 

[tsteinh]

Retrieving autonumber value

Does anyone know if there is a way to do this in Access97? I tried this code and I get "Invalid object or no longer set" if I try to get the value of the autonumber field after the recordset.close operation. If I try to get the value before the close, then I get a value of 1 in all cases (the value right now should be 11).

Basically, I need to add a record to a table, get that record's autonumber field and then close the table. If I could lock the table during this short operation in order to be sure no one else has access to it I can use the max() function to retrieve the highest number, but I cannot find out how to lock the table.

Any help would be appreciated!

 

[WayneRyan]

t,

It's not about locking. Once you do the .Update after the
.AddNew, the rstNewrecord!AutoNumberField is there for
you.

If you set a breakpoint, you can go to the immediate window
and see the value" ?rstNewRecord!AitoNumberField

Wayne

 

[tsteinh]

Wayne:

Here is my code:

Dim holdid As Long, rst As Recordset
Set rst = CurrentDb.OpenRecordset("tblTest", dbOpenDynaset)
rst.AddNew
rst!Remarks = "testing"
rst.Update
holdid = rst!TestID ' <<--- always returns 1, not last autonumber
rst.Close
Set rst = Nothing


I set a breakpoint at the holdid=rst!TestID statement, and everytime the value is 1 no matter how many records I have entered previously. My tblTest has 2 fields, TestID defined as autonumber and primary key and Remarks which is a text field of 50 characters.

Again, I am using Access97, so I don't know if that is the issue. I have been wrestling with this problem for quite a while and always resort to rereading the table for the max value after the update.

Any thoughts??

Thanks!

 

[Mile-O]

Dim db As Database
Dim rsNewRecord As Recordset

Suggested change....

Dim db As DAO.Database
Dim rsNewRecord As DAO.Recordset

 

[Pat Hartman]

Move this line -
holdid = rst!TestID ' <<--- always returns 1, not last autonumber

to be before the .update