Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Retrieve AutoNumber

Posted on 2008-10-07
24
Medium Priority
?
398 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
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
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, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

578 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