does anyone help me for this query??

[Jason]

I want to have an insert statement, after this insert statement, I want to
get back the return value of the primary key ar??
I don't want to requery the select statement, any method I can do that??

 

[Allen Browne]

Jet doesn't provide that information back to you.

You can use the AddNew method (DAO), to get the new p.k. value:

With rs
.AddNew
!SomeField = SomeValue
!AnotherField = AnotherValue
'etc.
.Update

'Make the new record the current one.
.Bookmark = .LastModified
MsgBox "The new ID value is " & !ID
End With

 

[Jason]

thx

Jet doesn't provide that information back to you.

You can use the AddNew method (DAO), to get the new p.k. value:

With rs
.AddNew
!SomeField = SomeValue
!AnotherField = AnotherValue
'etc.
.Update

'Make the new record the current one.
.Bookmark = .LastModified
MsgBox "The new ID value is " & !ID
End With

 

[Woody]

this method works for me.

Private Function Process_Db_Command(StrSQlCommand As String)
On Error GoTo Unable_To_Process

Dim ConDB As Connection
Dim ConRS As ADODB.Recordset

Set ConDB = New Connection
ConDB.CursorLocation = adUseServer
ConDB.Open modStartUp.GlobalData.Get_SQL_Provider & App.Path &
"\contacts.mDb;"

Set ConRS = New Recordset

ConRS.Open StrSQlCommand, ConDB, adOpenStatic, adLockPessimistic

ConDB.BeginTrans

ConDB.Execute StrSQlCommand

If Left(StrSQlCommand, 6) = "SELECT" _
or left(StrSQLCommand,6) = "INSERT" Then
strPassName = ConRS.Fields("RecipientName").Value
strPassLetter = ConRS.Fields("LetterFileSent").Value
If IsNull(ConRS.Fields("CurrentStatus").Value) = True Then
ConRS.Fields("CurrentStatus").Value = 0
End If
dblPassCount = ConRS.Fields("CurrentStatus").Value
End If

ConRS.UpdateBatch adAffectAllChapters
ConDB.CommitTrans

ConRS.Close
ConDB.Close

Set ConRS = Nothing
Set ConDB = Nothing

Process_Db_Command = 0

Exit Function

 

[Joan Wild]

Hi Allen,
The new ID is available as soon as you use .AddNew. At least it's always
worked for me.

With rs
.AddNew
msgbox "The new ID value is " & !ID
!SomeField = SomeValue
!AnotherField = AnotherValue
'etc.
.Update
End With

 

[Allen Browne]

Yep: that's correct for Jet tables.