Help with recordset

[SF]

I have a new database link to SQL Server 2000 through ODBC. I am trying to
add new recordset to the underlaying table but receive saying that I should
use dbSeeChanges. But when I change to dbSeeChanges, I receive other error
code 3001. What happens to my code.



Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim VarID As Long

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblComplaintBy", dbSeeChanges)
With rst
.AddNew
![cbName_e] = Me.cbName_e
![cbName_k] = Me.cbName_k
![cbAddress] = Me.cbAddress
![cbProvinceID] = Me.cbProvinceID
![cbDistrictID] = Me.cbDistrictID
![cbCommuneID] = Me.cbCommuneID
![cbVillageID] = Me.cbVillageID
![cbPhone] = Me.cbPhone
VarID = rst![CTID]
.Update
End With

SF

 

[TC]

What is the exact text of the error message, and what line of code does
it occur on?

TC (MVP Access)
http://tc2.atspace.com

 

[Guest]

I suspect that your line
VarID = rst![CTID]
is the culprit.

It looks to me that CTID is a Primary key or identity value generated upon
insert. So, you want to insert data into the table, and retreive back
identity value CTID. Am I right? If yes, we can try to solve the problem.

 

[SF]

You are right.

I try to capture to new identity for later use. After removing this line,
there is no more error. But I have one more question. How do I capture the
new identity when inserting new record with ODBC connection.

I usauly used VarID = rst![CTID] and it work great

SF

I suspect that your line
VarID = rst![CTID]
is the culprit.

It looks to me that CTID is a Primary key or identity value generated upon
insert. So, you want to insert data into the table, and retreive back
identity value CTID. Am I right? If yes, we can try to solve the problem.
--

I have a new database link to SQL Server 2000 through ODBC. I am trying to
add new recordset to the underlaying table but receive saying that I should
use dbSeeChanges. But when I change to dbSeeChanges, I receive other error
code 3001. What happens to my code.



Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim VarID As Long

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblComplaintBy", dbSeeChanges)
With rst
.AddNew
![cbName_e] = Me.cbName_e
![cbName_k] = Me.cbName_k
![cbAddress] = Me.cbAddress
![cbProvinceID] = Me.cbProvinceID
![cbDistrictID] = Me.cbDistrictID
![cbCommuneID] = Me.cbCommuneID
![cbVillageID] = Me.cbVillageID
![cbPhone] = Me.cbPhone
VarID = rst![CTID]
.Update
End With

SF