does anyone help me for this query??

  • Thread starter Thread starter Jason
  • Start date Start date
J

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??
 
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
 
thx

Allen Browne said:
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
Click to expand...
 
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
 
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
 
Back
Top