INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

dao recordset update: move to record just added

dao recordset update: move to record just added

(OP)
Hi,

can I get some opinions, please, on what's the best way to make the record just added with .AddNew and .Update the current record? E.g.:

CODE

dim rst as DOA.Recordset

set rst=CurrentDB.OpenRecordset("tblExample")

rst.AddNew
rst.Fields("SomeRandomData") = "Trivial Fact"
rst.Update

???

'profit

The idea is, after "???", I'd like the record I just added to be the current record so I can look up the Autonumber primary key and insert a record referencing it into another table.

Thanks for any insight you can provide.

TMTOWDI - it's not just for Perl any more

RE: dao recordset update: move to record just added

Why Are you adding with .addnew

Use this method

CODE

Dim Mydb as database
Dim Rst As recordset
Set mydb=currentdb

mydb.execute "Insert into tblExample (field,field2,SomeRandomData) Values (value1,Value2,"Trivial Fact")"
Set rst = mydb.openrecordset("Select @@@@IDENTITY As ident")
mydb.execute "Insert into tblExample (Somefield) Values (Rst!ident)"

RE: dao recordset update: move to record just added

(OP)
I'm using .addnew because that's what Sussman and Smith taught me. ;)

I'm unfamiliar with the "@@@@IDENTITY" notation. I'm also a little weak on raw SQL. Can you explain in a little more detail what's going on in your code, please?

Thanks!

TMTOWDI - it's not just for Perl any more

RE: dao recordset update: move to record just added

number 1

Sorry Should be

CODE

Set rst = mydb.openrecordset("Select @@@@IDENTITY As ident")
mydb.execute "Insert into tblExample2 (Somefield) Values (Rst!ident)"

@@IDENTITY  returns the last autonumber insetred bt the je database

RE: dao recordset update: move to record just added

Isn't it supposed to be only two "@"s? I only use it with ADO, though ...

Set rs = mydb.openrecordset("SELECT @@IDENTITY")
mydb.execute "Insert into tblExample2 (Somefield) Values (" & rs.Fields(0).Value & ")"


SELECT @@IDENTITY is supposed to return the last identity (autonumber) inserted on that connection.

Roy-Vidar

RE: dao recordset update: move to record just added

Roy works with doa

Try it

you are right should be only 2 not 4 @'s
also need to concatanate then ident field

if you use this syntex

CODE

Set rst = mydb.openrecordset("Select @@IDENTITY As ident")

You Can use


CODE

mydb.execute "Insert into tblExample2 (Somefield) Values  (" & Rst!ident &")"


 

RE: dao recordset update: move to record just added

(OP)
Thanks and stars around. :) This should do what I need in this case.

TMTOWDI - it's not just for Perl any more

RE: dao recordset update: move to record just added

How are ya adalger . . .

Without all the @@ hocus pocus, this can still be done quite easily with a recordset:

CODE

   Dim db As DAO.Database, rst As DAO.Recordset, hldPK As Long
   
   Set db = CurrentDb
   Set rst = CurrentDb.OpenRecordset("tblExample")
   
   rst.AddNew
   rst("SomeRandomData") = "Trivial Fact"
   hldPK = rst!PKname ' hold primarykey
   rst.Update
   Set rst = Nothing
   
   Me.Requery 'include newly added record in form recordset.
   Me.Recordset.FindFirst "[PKname] = " & hldPK 'Goto that record!

   Set db = Nothing

  See Ya! .  .  .  .  .  .

Be sure to see thread181-473997: How To Get Good Answers To Your Questions  Worthy Reading! thumbsup2
Also FAQ181-2886: How can I maximize my chances of getting an answer?  Worthy Reading! thumbsup2
                 

RE: dao recordset update: move to record just added

Identity hocus pocus?

It's a feature built into the Jet engine 10 years ago, so it's not exactly something new and fancy, is it?

Anyway, why not use the .LastModified property of the DAO recordset to move to the newly added record? This should work even for situations where you can't retreive the identity field prior to the .update (say with SQL-server etc).

Roy-Vidar

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close