If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
select @@identity returns 0
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 |
#2
|
|||
|
|||
select @@identity returns 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" |
#3
|
|||
|
|||
select @@identity returns 0
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" wrote in message ... 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" |
#4
|
|||
|
|||
select @@identity returns 0
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. Microsoft Access wrote: 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" wrote in message ... 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" |
#5
|
|||
|
|||
select @@identity returns 0
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 "Bob Barrows" wrote in message ... 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. Microsoft Access wrote: 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" wrote in message ... 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" |
Thread Tools | |
Display Modes | |
|
|