Smart questions
Smart answers
Smart people
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 now!
  • 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.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

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 addedHelpful Member!(2) 

adalger (Programmer) (OP)
9 Dec 09 14:35
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

Helpful Member!  PWise (Programmer)
9 Dec 09 14:50
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)"
adalger (Programmer) (OP)
9 Dec 09 15:09
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

PWise (Programmer)
9 Dec 09 15:14
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
Helpful Member!  RoyVidar (Instructor)
9 Dec 09 15:24
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

PWise (Programmer)
9 Dec 09 15:30
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 &")"


 
adalger (Programmer) (OP)
9 Dec 09 15:44
Thanks and stars around. :) This should do what I need in this case.

TMTOWDI - it's not just for Perl any more

TheAceMan1 (Programmer)
13 Dec 09 19:31
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
                 

RoyVidar (Instructor)
14 Dec 09 2:28
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

TheAceMan1 (Programmer)
14 Dec 09 9:49
Howdy RoyVidar . . .

I didn't mean it that way! . . . (actually I use it myself). Just wanted to show it can be done with recordset!

  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
                 

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!

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