Retrieve AutoNumber

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..
LVL 1
CMDAIAsked:
Who is Participating?
 
Jupiler78Connect With a Mentor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:


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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Identity does not apply in Access.

mx
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Jupiler78Commented:
MX,

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

Jupiler78
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
What works exaclty ?

mx
0
 
Jupiler78Commented:
CMDAI,

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

Jupiler78
0
 
CMDAIAuthor Commented:
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
 
CMDAIAuthor Commented:
Tried it this way also same error

sqlstr = "select @@indentity"
Set rs = .OpenRecordset(sqlstr)  > Error
0
 
CMDAIAuthor Commented:
I have no idea y, but this suddenly worked:
Set rs = CurrentDb.OpenRecordset("SELECT @@IDENTITY AS Expr1 FROM Category")
0
 
Jupiler78Commented:
Thanks for the points & grade.
Good luck.

Jupiler78
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
CMDAI

Was this Access or SQL server ?

mx
0
 
CMDAIAuthor Commented:
Access 07
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
ADO or DAO ?

mx
0
 
CMDAIAuthor Commented:
I Actually dont undertand difference between
ADO & DAO
But i Dim all the recordsets, Raltions Etc. as  DAO
0
 
CMDAIAuthor Commented:
I just went to read about ADO & DAO,
I undestand the adv+ and disadv- but really not idea what im using...
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
CMDAIAuthor Commented:
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
 
CMDAIAuthor Commented:
Basically Line 10 i execute & 11 i get the new ID
0
 
Jupiler78Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
CMDAIAuthor Commented:
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
 
CMDAIAuthor Commented:
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
 
CMDAIAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.