I've been messing with an Access unbound form with SQL back-end. I add a new record using VBA but want to retrieve the Identity value created. At first I simply tried "tbField0 = rs.fields(0).value" but after the rs.update is shows record is deleted. I tried adding rs.bookmark = rs.lastmodified but same result.
Found this article Access: Get newly created auto number in DAO and a comment by Rick about BIGINT & Access which I knew about. I don't use BIGINT because of it but NUMERIC instead. So, since no suggestions would work I tried to change the field type to INT and test. Works as required.
Therefore my question is how can I retrieve an incremented identity field from SQL which is not a datatype if INT using VBA in Access. I saw some mention of an OUTPUT command but not sure how to use or if applicable.
I have created two tables, one called tbl1_Int and the other tbl2_Num. Both contain two fields IndexID and Field1. Both Field1 are nchar and as you might surmise IndexID is a Int in tbl1_Int and a Numeric in tbl2_Num.
Created a blank form with two unbound fields txtIndex and txtField1 as well as two buttons btnInt and btnNumeric with the following code.
Private Sub btnInt_Click()
Dim DB As Database
Dim RS As Recordset
Dim strSQL As String
strSQL = "INSERT INTO dbo_tbl1_Int (Field1) VALUES ('" & txtField1 & "')"
Set DB = CurrentDb
DB.Execute strSQL
txtIndex = DB.OpenRecordset("SELECT @@IDENTITY")(0)
DB.Close
End Sub
Private Sub btnNumeric_Click()
Dim DB As Database
Dim RS As Recordset
Dim strSQL As String
strSQL = "INSERT INTO dbo_tbl2_Num (Field1) VALUES ('" & txtField1 & "')"
Set DB = CurrentDb
DB.Execute strSQL
txtIndex = DB.OpenRecordset("SELECT @@IDENTITY")(0)
DB.Close
End Sub
The records get created in the tables however it will only capture (thus populate) the index from tbl1 which is when the identity is a field type of Int. Which leads back to my original question of how do I capture the index when it is not an Integer?
I've gotten it to work with the following.
Private Sub btnNumeric_Click()
Dim DB As Database
Dim RS As Recordset
Dim strSQL As String
strSQL = "select * from dbo_tbl2_Num where false"
Set DB = CurrentDb
Set RS = DB.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
RS.AddNew
RS.Fields(1) = txtField1
RS.Update
RS.Move 0, RS.LastModified
txtIndex = Null
txtIndex = RS.Fields(0).Value
RS.Close
DB.Close
End Sub
