Solved

Retrieve AutoNumber

Posted on 2008-10-07
24
391 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 500 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

705 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