Solved

Retrieve AutoNumber

Posted on 2008-10-07
24
386 Views
Last Modified: 2012-05-05
I read into @@identity  but just dont get it..

I have this code, Symply inserte a new record.
With DoCmd
.SetWarnings (False)
.RunSQL ("INSERT INTO [Cat-Type] (Category,Type) Values ('" & Me.cmbCategory & "','" & Me.cmbType & "')")
.SetWarnings (True)
End With

Now i need to retrieve the new record's ID, wich is an autonumber..
0
Comment
Question by:CMDAI
  • 11
  • 8
  • 5
24 Comments
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility


With DoCmd
.SetWarnings (False)
.RunSQL ("INSERT INTO [Cat-Type] (Category,Type) Values ('" & Me.cmbCategory & "','" & Me.cmbType & "')")
.SetWarnings (True)

Dim lLastID as Long
lLastID = Dmax("[ID]","[Cat-Type]")

End With
0
 
LVL 8

Accepted Solution

by:
Jupiler78 earned 500 total points
Comment Utility
Hello CMDAI,

You didn't got the ID back from @@identity?

Try:
sqlstr = "select @@indentity"
set rs = currentdb.openrecordset(sqlstr)
result = rs.getrows(1)
rs.close
NewId = result(0,0)

Regards,

Jupiler78
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Identity does not apply in Access.

mx
0
 
LVL 8

Expert Comment

by:Jupiler78
Comment Utility
MX,

don't know why, but here at my work it does.
Don't know why, but it works.

Jupiler78
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
What works exaclty ?

mx
0
 
LVL 8

Expert Comment

by:Jupiler78
Comment Utility
CMDAI,

with the code it put above, I get the last added Autonumber.

Jupiler78
0
 
LVL 1

Author Comment

by:CMDAI
Comment Utility
Sorry for delay,
I tried a few different this give me an error

With CurrentDb
.Execute ("INSERT INTO Category (Category) Values ('" & Trim(Me.txtCategory.Value) & "')")
sqlstr = "select @@indentity From Category"
Set rs = .OpenRecordset(sqlstr)
result = rs.GetRows(1)
rs.Close
newID = result(0, 0)
End With



Error.jpg
0
 
LVL 1

Author Comment

by:CMDAI
Comment Utility
Tried it this way also same error

sqlstr = "select @@indentity"
Set rs = .OpenRecordset(sqlstr)  > Error
0
 
LVL 1

Author Comment

by:CMDAI
Comment Utility
I have no idea y, but this suddenly worked:
Set rs = CurrentDb.OpenRecordset("SELECT @@IDENTITY AS Expr1 FROM Category")
0
 
LVL 8

Expert Comment

by:Jupiler78
Comment Utility
Thanks for the points & grade.
Good luck.

Jupiler78
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
CMDAI

Was this Access or SQL server ?

mx
0
 
LVL 1

Author Comment

by:CMDAI
Comment Utility
Access 07
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
ADO or DAO ?

mx
0
 
LVL 1

Author Comment

by:CMDAI
Comment Utility
I Actually dont undertand difference between
ADO & DAO
But i Dim all the recordsets, Raltions Etc. as  DAO
0
 
LVL 1

Author Comment

by:CMDAI
Comment Utility
I just went to read about ADO & DAO,
I undestand the adv+ and disadv- but really not idea what im using...
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
This

won't even compile in the Access query grid ...

and this:

    Dim result, newID
    Dim rs As DAO.Recordset
    With CurrentDb
        Set rs = .OpenRecordset("select @@indentity as Expr1 From Table1")
        result = rs.GetRows(1)
        rs.Close
        newID = result(0, 0)
    End With

gives the syntax error.  I'm not aware of any such syntax @@IDENTITY ...

where did you find this ?

mx
0
 
LVL 1

Author Comment

by:CMDAI
Comment Utility
I think im going to go and research...
i feel im missing something basic if i dont understand ADO,DAO
My programming strated from the 'magic' record button in excel a few years ago'  If you know what i mean.

Anyway, I created a new database "Product_Line.accdb" in Access 2007
and here is the whole procedure where i use this code:

Private Sub cmd_Add_New_Product_Click() '** Modified From cmd_Add_Type_Click() ++ added form create

Dim i As Long

On Error GoTo err_handle

'Check values are Available

If Me.cmbCategory = 0 Then MsgBox "Category is not selected", vbCritical, "Violation": Exit Sub

If Me.cmbType = 0 Then MsgBox "Type is not selected", vbCritical, "Violation": Exit Sub
 

DoCmd.SetWarnings (False) 'Temporary turn off Access Warnings

With CurrentDb

.Execute ("INSERT INTO [Cat-Type] (Category,Type) Values ('" & Me.cmbCategory & "','" & Me.cmbType & "')")

i = .OpenRecordset("SELECT @@IDENTITY AS Expr1 FROM [Cat-Type]").GetRows(1)(0, 0)

End With
 

DoCmd.SetWarnings (True) 'Warnings on

Me.Recalc
 

If Not Create_new_Cat_Type_structure(i & "") Then

MsgBox "Error Occured when creating strucure for new Product" & Chr(10) & "Structure was not created", vbCritical, "Error"

End If
 

Exit Sub

err_handle:

DoCmd.SetWarnings (True)

MsgBox "Products was not Instreted", vbOKOnly, "Violation"

Me.Recalc
 

End Sub

Open in new window

0
 
LVL 1

Author Comment

by:CMDAI
Comment Utility
Basically Line 10 i execute & 11 i get the new ID
0
 
LVL 8

Expert Comment

by:Jupiler78
Comment Utility
Sorry that I couldn't reply earlier.

Here is a page from MS: http://support.microsoft.com/kb/232144

I don't know anymore where I found it when I used it ni my programs, but it works fine.

0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
That article implies that SELECT @@Identity only works with ADO, which was my guess - which is why I don't  understand how it works above?

Also, as far as GetRows ... I don't get this syntax either:

.GetRows(1)(0, 0)

                      ^^^^

That is not show in the Help file.  Can't say I've ever had a need to use GetRows, but I guess it could be handy sometime.

mx
0
 
LVL 1

Author Comment

by:CMDAI
Comment Utility
Getrows:http://www.w3schools.com/ado/met_rs_getrows.asp

I just put all in one row, i like short code, bad for bugs though
...................
Set rs = .OpenRecordset("select @@indentity as Expr1 From Table1")
        result = rs.GetRows(1)
        newID = result(0, 0)
..................
Set rs = .OpenRecordset("select @@indentity as Expr1 From Table1").GetRows(1)
        newID = result(0, 0)
..................
Set rs = .OpenRecordset("select @@indentity as Expr1 From Table1").GetRows(1)(0, 0)

0
 
LVL 1

Author Comment

by:CMDAI
Comment Utility
oops Correction:

...........................
CurrentDB.OpenRecordset("SELECT @@IDENTITY AS Expr1 FROM [Cat-Type]").GetRows(1)(0, 0)
+And+
rs.GetRows(1)
+And+
result(0, 0)
...........................
=
CurrentDB.OpenRecordset("SELECT @@IDENTITY AS Expr1 FROM [Cat-Type]").GetRows(1)(0, 0)


But I dont know CurrentDB.OpenRecordset will go through DAO or ADO....
0
 
LVL 1

Author Comment

by:CMDAI
Comment Utility
Just a follow up,
I dont have refrence to "Microsoft ActiveX Data Objects 6.0 Library"

so unless "CurrentDB.OpenRecordset " is smart enough to know when to use ADO and When to use DAO then it must be DAO.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
CurrentDb is not a ADO construct.  It's only available in DAO.

When you create a New mdb, the default Reference is to ADO, not DAO  (sadly and stupid).  So, until you set an explicit reference to DAO, using CurrentDB will give you an error.

mx
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now