dao recordset update: move to record just added 2

[adalger]

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

dim rst as DOA.Recordset

set rst=CurrentDB.OpenRecordset("tblExample")

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

???

[COLOR=green]'profit[/color]

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

 

[PWise]

Why Are you adding with .addnew

Use this method

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]

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]

number 1

Sorry Should be

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

 

[RoyVidar]

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

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

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

Roy-Vidar

 

[PWise]

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

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

You Can use

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

 

[adalger]

Thanks and stars around. This should do what I need in this case.

TMTOWDI - it's not just for Perl any more

 

[TheAceMan1]

How are ya adalger . . .

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

[blue]   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 [green]' hold primarykey[/green]
   rst.Update
   Set rst = Nothing
   
   Me.Requery [green]'include newly added record in form recordset.[/green]
   Me.Recordset.FindFirst "[PKname] = " & hldPK [green]'Goto that record![/green]

   Set db = Nothing[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[RoyVidar]

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]

Howdy RoyVidar . . .

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

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]