Retrieve New Autonumber CurrentDb.Execute

[gray]

Hi All

WinXPPro
Access 2002 SP3


I'm doing an INSERT to a table by building an SQL String and then executing it with :-

CurrentDb.Execute(SQL_String)...

Works marvellously! I want to retrieve the record number that the INSERT created and I suspect that there is an easy way to do it wihtout me writing tons of queries. I've experimented with calling

CurrentDb.Execute("SELECT @@IDENTITY") immediately afterwards but it fails either because

"CANNOT EXECUTE A SELECT QUERY"... or if I call it by opening a recordset query the connection has closed and the new record number is lost.

Can anyone help please?

Thanks

 

[ByteMyzer]

The following example should put you on the right track:

[COLOR="Navy"]Dim[/COLOR] db [COLOR="navy"]As[/COLOR] DAO.Database
[COLOR="navy"]Dim[/COLOR] rs [COLOR="navy"]As[/COLOR] DAO.Recordset

[COLOR="navy"]Set[/COLOR] db = CurrentDb

db.Execute SQL_String

[COLOR="navy"]Set[/COLOR] rs = db.OpenRecordset("SELECT @@IDENTITY")
[COLOR="navy"]Debug.Print[/COLOR] rs.Fields(0)

rs.Close
[COLOR="navy"]Set[/COLOR] rs = [COLOR="navy"]Nothing[/COLOR]

db.Close
[COLOR="navy"]Set[/COLOR] db = [COLOR="navy"]Nothing[/COLOR]

 

[gray]

Hi

Great I'll give it a whirl! Thanks

 

[gray]

Hi ByteMyzer

I seem to have hit a barrier with declaring DAO's ..
"User-Defined Type Not Defined"... And I don't appear to have a tick box in my references list for DAO?

I tried subsituting ADODB for DAO but it did not recognise the .Database declaration.

In the meantime, however, I discovered a neat trick using DMAX...

New_Record_No = DMax("[Item_ID]", "tempTBL") ..

Which returns the highest Item_ID no.

This will probably do for my particular purposes here since the tempTBL is not shared but I'd really like to crack it using the connection method as it seems far more reliable in a shared environment.

Any idea how I get around the DAO reference problem?

 

[gray]

Hi Again


Grrrr.... I found an article on how to set the DAO reference... but I couldn't find it in my list because it's actually prefixed with "Microsoft" i.e. in my case

"Microsoft DAO 3.6 Object Library"

I shall try your method again ByteMyzer - thanks

 

[boblarson]

Hi ByteMyzer

I seem to have hit a barrier with declaring DAO's ..
"User-Defined Type Not Defined"... And I don't appear to have a tick box in my references list for DAO?

Just add a reference to Microsoft DAO 3.x where x is just a number indicating the revision level (for example 3.51).

You can have ADO and DAO both checked and use both in your database. Just be sure to preface your code with the type you want:

Dim db As DAO.Database
Dim rst As DAO.Recordset

or if using ADO

Dim rst As ADODB.Recordset

instead of using just

Dim rst As Recordset

and in this case where you want the identity, I think you will still need to use DAO as shown by ByteMyzer.

 

[gray]

Hi

Works brilliantly! Thanks BytesMyzer and Bob!!

 

[hany1002]

when i convert my access batabase from access 2003 to access 2007

i receved error in this code part

-------------
code
-------------

Private Sub ACCID_BeforeUpdate(Cancel As Integer)
Set Q1 = CurrentDb.OpenRecordset("select * from Accounts where AccId = '" & AccID & "'")
If Q1.RecordCount > 0 Then

 

[gray]

Hi hany1002

Not quite sure what you a re asking me here... can yo explain a little further please?

rgds

 

[hany1002]

this code not work Is there a problem

-----------------
code
-----------------


Private Sub S_Click()
If (AA - BB) = 0 Then
Else
MsgBox "ÚÝÜÜæÇð ... ÇáÞíÜÜÜÜÏ ÛÜÜíÑ ãÊÜÜÜæÇÒä", , "ÇáãÈÑãÜÜÜÜÜÜÌ"
Exit Sub
End If
CurrentDb.Execute ("Insert into TransActions select * from TransActions1")
CurrentDb.Execute ("Insert into MoneyMoves select * from MoneyMoves1")
DoCmd.GoToRecord , , acNewRec
S1 = 0
S2 = Null
S2.SetFocus
ABB:
Exit Sub
ABC:
Resume ABB
End Sub

 

[hany1002]

This problem occurred when i convert database from access 2003 to access 2007

 

[gray]

Did you get an error code and/or description?

Failing that, put a debug into the vba code module so that you can step through each command to see which one it's failoing at.

Do both TransActions and TransActions1 have an Autonum PK field? I know when I've done copying using your described method I've had to specify all the field names but exclude the Autonum fields. Unlikely since that was the same at 2002.. but worth checking anyway.