We help IT Professionals succeed at work.

Insert into help with autonumber.

1,454 Views
Last Modified: 2013-12-05
i have the attatched code.

When i run the code it creates a new record in tblcustomerdetails, there is also a column in tblcustomerdetails called  AUTO   that is an auto number.  so i dont have a record of it anywhere as it is randomly generated.

i need to refer to this new AUTO created in tblcustomerdetails  in the code below it, so that i can paste that new AUTO into the AUTO field on the second table and still pull the rest of the information off as currently scripted.
CurrentProject.Connection.Execute _
    "INSERT INTO tblcustomerdetails ([Date Recorded], Name, [trading as name], Legalconstitution, salesperson, [type of application], [ai/kyc], [internal status], [Date sent to SMS], [Date Approved / Declined], [Date Dispatched], [Date Installed], [Date Active]) SELECT [Date Recorded], Name, [trading as name], Legalconstitution, salesperson, [type of application], [ai/kyc], [internal status], [Date sent to SMS], [Date Approved / Declined], [Date Dispatched], [Date Installed], [Date Active] FROM tblcustomerdetails WHERE tblcustomerdetails.auto = " & Me!subbasicc.Form!txtauto & ""
CurrentProject.Connection.Execute _
    "INSERT INTO tblcomments ([Auto], [handepay comments], [Streamline Comments], [Post Log], [handepay comments2], [Post Log Comments]) SELECT * FROM tblcustomerdetails WHERE tblcomments.auto = " & Me!subbasicc.Form!txtauto & ""

Open in new window

Comment
Watch Question

In your first SQL statement do a "select scope_identity()" (without ") to retrieve the value of the newly inserted data.  Then pull recordset and get the value from your connection.

Author

Commented:
would you be able to post an example for me please?

Author

Commented:
i forgot i had a trigger that was automatically creating the records in the other tables i use, i managed to get the 1st part working with 1 table using the code below, however when it gets to the seconds one it fails saying you cant insert null into the field auto     is there a way to get the Identity_scope() command to run further?


Private Sub Command147_Click()
CurrentProject.Connection.Execute _
"INSERT INTO tblcustomerdetails ([Date Recorded], Name, [trading as name], Legalconstitution, salesperson, [type of application], [ai/kyc], [internal status], [Date sent to SMS], [Date Approved / Declined], [Date Dispatched], [Date Installed], [Date Active]) SELECT [Date Recorded], Name, [trading as name], Legalconstitution, salesperson, [type of application], [ai/kyc], [internal status], [Date sent to SMS], [Date Approved / Declined], [Date Dispatched], [Date Installed], [Date Active] FROM tblcustomerdetails WHERE tblcustomerdetails.auto = " & Me!subbasicc.Form!txtauto & ""
CurrentProject.Connection.Execute _
    "Delete from tblcomments WHERE tblcomments.auto = scope_identity()"
CurrentProject.Connection.Execute _
    "INSERT INTO tblcomments ([Auto], [handepay comments], [Streamline Comments], [Post Log], [handepay comments2], [Post Log Comments]) SELECT scope_identity(), [handepay comments], [Streamline Comments], [Post Log], [handepay comments2], [Post Log Comments] FROM tblcomments WHERE tblcomments.auto = " & Me!subbasicc.Form!txtauto & ""

CurrentProject.Connection.Execute _
    "Delete from tblterminaldetails WHERE tblterminaldetails.auto = scope_identity()"
CurrentProject.Connection.Execute _
    "INSERT INTO tblterminaldetails ([Auto], [MID], [TID], [Terminal], [Network], [FURRENTALOF] ,[MINPER], [Outlet], [MRP Doc]) SELECT scope_identity(), [MID], [TID], [Terminal], [Network], [FURRENTALOF] ,[MINPER], [Outlet], [MRP Doc]FROM tblterminaldetails WHERE tblterminaldetails.auto = " & Me!subbasicc.Form!txtauto & ""

Author

Commented:
sorry forgot to add.

After the Space is where it starts to fail.
Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
worked great, thanks.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.