Reference Autonumber Primary Key 1

[ParyGoal]

I am trying to access the autonumber primary key of the newly created records through code. The datasourse is an access table.
The code was working fine when I used a connectionstring using AppConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\HECP.mdb;Persist Security Info=False"

Now that I am using an ODBC connection in my connectionstring such as
AppConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=HECPCon"
I am unable to located the autonumber primary key of the newly created record.
Below is the code I am using.
Dim cnNewHECP As New Connection
Dim rsNewHECP As New Recordset
Dim strNewHECPKey As String

cnNewHECP.Open AppConnectionString
rsNewHECP.Open "SELECT * FROM tblHECP WHERE 1=0", cnNewHECP, adOpenStatic, adLockOptimistic


With rsNewHECP
.AddNew

.Fields("CreateDate") = Now
.Fields("CreatedByID") = CurrUser.USER_PK
.Fields("RefNumber") = strRefNo
.Fields("EquipmentNoRefID") = txtEquip1.Tag
.Fields("LastAuditDate") = Now()
.Fields("LastAuditedByID") = CurrUser.USER_PK
.Fields("LastRevisionDate") = Now()
.Fields("LastRevisedByID") = CurrUser.USER_PK
.Update

strNewHECPKey = .Fields("HECPID")----> empty when step through
End With

Any help will be greatly appreciated

Thank you

ParyGoal

 

[BobRodes]

It will be empty while you're still in addnew state. It won't be populated until you save the record, using update. Try checking it again after you call the update method.

HTH

Bob

 

[SBerthold]

"Update" is being called

 

[ParyGoal]

Hi BobRodes

I did call it again after I call the update methods. It still does not work.
I have no idea?

ParyGoal

 

[dpaulson]

How about issuing a .requery.

David Paulson

 

[ParyGoal]

Hi dpaulson

I am getting a run-time error
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

Parygoal

 

[SBerthold]

You can use the following to get the last Identity value from the same connection:

Dim rs As ADODB.Recordset
Set rs = conn.Execute("SELECT @@Identity")
Debug.Print rs.Fields(0).Value
rs.Close

However, be advised that if there are any triggers updating other tables which also have an Identity field, you will get the Identity value from the last one updated.

Therefore, it is best to use a stored proceedure to do the update with, and use "Select Scope_Identity() As myLastID" in it.

Also, please verify the following:

Debug.? cnNewHECP.CursorLocation '2-Server
Debug.? rsNewHECP.CursorType '1-KeySet

 

[SBerthold]

ParyGoal,

Also, what happens when you explicitly set the CursorType to adOpenKeySet? Does it work then?

 

[SBerthold]

Ok. The problem appears to the lack of the Resync abilitity of the cursor.
That because the lock type is optimistic, and the cursor was set to Static, but upon opening the recordset, the cursor changes to Keyset (that is expected because of the lock type.)
However, even though it is now a KeySet cursor, and updatable, it will not resyncronize the Identity field after an AddNew.

In order to have it do this, you need to make sure you explicitly set the cursor type to adOpenKeyset.

To see this, you can verify the "Supports" property using adResync.

I would still prefer to use the Stored Proceedure method for this.

 

[ParyGoal]

Hi SBerthold

You are right. It had to do the type of the cursor I was using. As soon as I changed it to adopenKeySet it worked.
I wish I could use I stored procedure, but I am currently using an access database as backend.

Thank you very much for your help

 

[BobRodes]

<"Update" is being called

So it was, my bad.