select @@identity returns 0

[icccapital]

I have been doing some reading, but without success in figuring this out.
How do I group two queries an insert and then an @@Identity so that
@@identity won't return 0, but will return the primary key, which is an
autonumber?

I am running access 2007 and am trying to run this query in response to a
control button being pressed so it is in vba.

Thanks

 

[Bob Barrows]

I have been doing some reading, but without success in figuring this
out. How do I group two queries an insert and then an @@Identity so
that @@identity won't return 0, but will return the primary key,
which is an autonumber?

I am running access 2007 and am trying to run this query in response
to a control button being pressed so it is in vba.

Errr....
run the insert query that inserts a single record into the table that has
the autonumber field, and, before doing anything else, open a recordset
using SELECT @@IDENTITY.

Seriously ... it's that simple. If it's not working for you, you will need
to show us how to reproduce your symptom.

 

[Microsoft Access]

Unless I am mistaken, that is exactly what I did. Here is the code. Thanks
for the thoughts:

strInsert = "INSERT INTO Contacts ([First Name], [Last Name], Company,
Address, [Address 2], City " & _
", State, [Zip Code], [Phone Number], Fax, [Email Address],
User, ModifiedDate) VALUES (""" & Me.txtFirstName & _
""", """ & Me.txtLastName & """, " & strNullCompany & ", """
& Me.txtAddress & _
""", """ & Me.txtAddress2 & """, """ & Me.txtCity & """, """
& Me.txtState & _
""", """ & Me.txtZipCode & """, """ & Me.txtPhoneNumber &
""", """ & Me.txtFax & _
""", """ & Me.txtEmailAddress & """, """ & fOSUserName() &
""", #" & Date & "#)"
DoCmd.RunSQL strInsert

Set rst = CurrentDb().OpenRecordset("SELECT @@IDENTITY")
debug.print rst.Fields(0)

this prints 0

 

[Bob Barrows]

Your problem is you are doing these two operations in different scopes. The
DoCmd is operating in Access's "sandbox" (for want of a better word) and the
OpenRecordset is operating in DAO's sandbox. In DAO's sandbox, nothing
happened to generate the autonumber. You need to perform both actions in the
same sandbox. Now, I've never used DAO for this, but I think this will work:

db.execute strInsert
'you'll probably want to check for errors at this point, but that can wait
et rst = CurrentDb().OpenRecordset("SELECT @@IDENTITY")
debug.print rst.Fields(0)

I _know_ this can be done with ADO. There are many examples that can be
found via google, including a knowledge base article on the
support.microsoft.com site.

Unless I am mistaken, that is exactly what I did. Here is the code.
Thanks for the thoughts:

strInsert = "INSERT INTO Contacts ([First Name], [Last Name], Company,
Address, [Address 2], City " & _
", State, [Zip Code], [Phone Number], Fax, [Email
Address], User, ModifiedDate) VALUES (""" & Me.txtFirstName & _
""", """ & Me.txtLastName & """, " & strNullCompany &
", """ & Me.txtAddress & _
""", """ & Me.txtAddress2 & """, """ & Me.txtCity &
""", """ & Me.txtState & _
""", """ & Me.txtZipCode & """, """ &
Me.txtPhoneNumber & """, """ & Me.txtFax & _
""", """ & Me.txtEmailAddress & """, """ &
fOSUserName() & """, #" & Date & "#)"
DoCmd.RunSQL strInsert

Set rst = CurrentDb().OpenRecordset("SELECT @@IDENTITY")
debug.print rst.Fields(0)

this prints 0

Errr....
run the insert query that inserts a single record into the table
that has the autonumber field, and, before doing anything else, open
a recordset using SELECT @@IDENTITY.

Seriously ... it's that simple. If it's not working for you, you
will need to show us how to reproduce your symptom.

 

[Microsoft Access]

Thank you I will take a look at that. I saw information about the scope of
the query, but I could not find information on what that implied in access,
so thank you for clarifying that. I will take a look at the ado examples I
can find. Thanks

Your problem is you are doing these two operations in different scopes.
The DoCmd is operating in Access's "sandbox" (for want of a better word)
and the OpenRecordset is operating in DAO's sandbox. In DAO's sandbox,
nothing happened to generate the autonumber. You need to perform both
actions in the same sandbox. Now, I've never used DAO for this, but I
think this will work:

db.execute strInsert
'you'll probably want to check for errors at this point, but that can wait
et rst = CurrentDb().OpenRecordset("SELECT @@IDENTITY")
debug.print rst.Fields(0)

I _know_ this can be done with ADO. There are many examples that can be
found via google, including a knowledge base article on the
support.microsoft.com site.

Unless I am mistaken, that is exactly what I did. Here is the code.
Thanks for the thoughts:

strInsert = "INSERT INTO Contacts ([First Name], [Last Name], Company,
Address, [Address 2], City " & _
", State, [Zip Code], [Phone Number], Fax, [Email
Address], User, ModifiedDate) VALUES (""" & Me.txtFirstName & _
""", """ & Me.txtLastName & """, " & strNullCompany &
", """ & Me.txtAddress & _
""", """ & Me.txtAddress2 & """, """ & Me.txtCity &
""", """ & Me.txtState & _
""", """ & Me.txtZipCode & """, """ &
Me.txtPhoneNumber & """, """ & Me.txtFax & _
""", """ & Me.txtEmailAddress & """, """ &
fOSUserName() & """, #" & Date & "#)"
DoCmd.RunSQL strInsert

Set rst = CurrentDb().OpenRecordset("SELECT @@IDENTITY")
debug.print rst.Fields(0)

this prints 0

icccapital wrote:
I have been doing some reading, but without success in figuring this
out. How do I group two queries an insert and then an @@Identity so
that @@identity won't return 0, but will return the primary key,
which is an autonumber?

I am running access 2007 and am trying to run this query in response
to a control button being pressed so it is in vba.

Errr....
run the insert query that inserts a single record into the table
that has the autonumber field, and, before doing anything else, open
a recordset using SELECT @@IDENTITY.

Seriously ... it's that simple. If it's not working for you, you
will need to show us how to reproduce your symptom.

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"