Get triggered value back (1 Viewer)

[echnaton]

Hi,

I want to use an MSSQL 2005 server as the backend of my access database. I created a table with two fields:
Field1 -> ID field, it will be updated by a trigger on the server (This is a Primary key)
Field2 -> the actual data, entered by the user

So the user enters field2 on a form, and when he/she saves there is a message box showing field one.

I use the following code:

Sub AddNew
Dim OpenADORS As ADODB.Recordset
Dim objConn As ADODB.Connection
Set objConn = New ADODB.Connection

objConn.Open "Provider=sqloledb.1;data source=localhost;Initial catalog=MyDB;Integrated Security=SSPI;"

Set OpenADORS = New ADODB.Recordset
OpenADORS.Open "SELECT * FROM dbo.MyTable, objConn, adOpenDynamic, adLockOptimistic

OpenADORS.AddNew
OpenADORS!Field2 = "John Doe"
OpenADORS.Update

'This is when the trigger updates field1 and I need to get this data from the server to show it in the following line:


MsgBox (OpenADORS!field1)
'But it shows NULL value...
End Sub


Please let me know if you have an idea how I could resolve this problem.
Many thanks in advance,
Akos

 

[pdx_man]

Well, you need to requery your recordset to get the update from the server.

OpenADORS.Open "SELECT * FROM dbo.MyTable WHERE Field2 = 'John Doe'"
, objConn, adOpenDynamic, adLockOptimistic

OpenADORS.AddNew
OpenADORS!Field2 = "John Doe"
OpenADORS.Update
OpenADORS.ReQuery