?
Solved

Retrieve AutoNumber

Posted on 2008-10-07
24
Medium Priority
?
392 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 8
  • 5
24 Comments
 
LVL 75
ID: 22666346


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 2000 total points
ID: 22666351
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
ID: 22666358
Identity does not apply in Access.

mx
0
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
LVL 8

Expert Comment

by:Jupiler78
ID: 22666440
MX,

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

Jupiler78
0
 
LVL 75
ID: 22666451
What works exaclty ?

mx
0
 
LVL 8

Expert Comment

by:Jupiler78
ID: 22666514
CMDAI,

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

Jupiler78
0
 
LVL 1

Author Comment

by:CMDAI
ID: 22666754
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
ID: 22666765
Tried it this way also same error

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

Author Comment

by:CMDAI
ID: 22666843
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
ID: 22666881
Thanks for the points & grade.
Good luck.

Jupiler78
0
 
LVL 75
ID: 22670484
CMDAI

Was this Access or SQL server ?

mx
0
 
LVL 1

Author Comment

by:CMDAI
ID: 22674872
Access 07
0
 
LVL 75
ID: 22675113
ADO or DAO ?

mx
0
 
LVL 1

Author Comment

by:CMDAI
ID: 22675531
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
ID: 22675567
I just went to read about ADO & DAO,
I undestand the adv+ and disadv- but really not idea what im using...
0
 
LVL 75
ID: 22675645
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
ID: 22675688
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
ID: 22675694
Basically Line 10 i execute & 11 i get the new ID
0
 
LVL 8

Expert Comment

by:Jupiler78
ID: 22677165
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
ID: 22680010
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
ID: 22680189
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
ID: 22680263
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
ID: 22683955
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
ID: 22684058
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

752 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