Insert into help with autonumber.
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.
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 & ""
BrandonGalderisi
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.
ASKER
would you be able to post an example for me please?
ASKER
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.
"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.
"Delete from tblcomments WHERE tblcomments.auto = scope_identity()"
CurrentProject.Connection.
"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.
"Delete from tblterminaldetails WHERE tblterminaldetails.auto = scope_identity()"
CurrentProject.Connection.
"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 & ""
Private Sub Command147_Click()
CurrentProject.Connection.
"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.
"Delete from tblcomments WHERE tblcomments.auto = scope_identity()"
CurrentProject.Connection.
"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.
"Delete from tblterminaldetails WHERE tblterminaldetails.auto = scope_identity()"
CurrentProject.Connection.
"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 & ""
ASKER
sorry forgot to add.
After the Space is where it starts to fail.
After the Space is where it starts to fail.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
worked great, thanks.