does anyone help me for this query??

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

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
 
J

Jason

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...
 
W

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
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Incone & Expenses Statement 0
Page numbering with Merged Document and IF statements 1
Trying to create a report that will list unmatched records by Ticket Primary Key 0
Error message: "Word found unreadable content in FILENAME. Do you want to recover the contents of this document? 0
Modern equivalent of Autotext in Word? 0
Table of content - format does not get saved 2
Create a dynamic master file 1
A little help please 0

Top