By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,401 Members | 793 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,401 IT Pros & Developers. It's quick & easy.

How can I do this please

P: n/a
Hi,
Please can anyone tell me how to achieve multiple inserts using the key
value from a newly inserted record of one table into several other tables.

I have a company table, an address table, a contact table.

when I insert into the company table, I also want to insert the new id
into the address and contact tables under companyID field.

The primary key in the company table is auto number, so the new key is
not known until it is inserted.

is it possible to retrieve the new key as a resultset of the insert
statement so I can then use this to insert into the other tables?

Any help appreciated
Yeric
Aug 11 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
eric wrote:
when I insert into the company table, I also want to insert the new id
into the address and contact tables under companyID field.

The primary key in the company table is auto number, so the new key is
not known until it is inserted.

is it possible to retrieve the new key as a resultset of the insert
statement so I can then use this to insert into the other tables?
Dim c As ADODB.Connection
Set c = New ADODB.Connection
With c
..ConnectionString = CurrentProject.BaseConnectionString
..Open
..Execute "INSERT INTO [Order Details] " _
& "(OrderID, ProductID, UnitPrice, Quantity, Discount) " _
& "Values (12, 23, 15.5, 12, 3/1000)"
Debug.Print .Execute("SELECT @@Identity").Collect(0)
' prints the autonumber of the newly inserted record
..Close
End With
Set c = Nothing
End Sub

Aug 11 '06 #2

P: n/a
Dim c As ADODB.Connection
Set c = New ADODB.Connection
With c
.ConnectionString = CurrentProject.BaseConnectionString
.Open
.Execute "INSERT INTO [Order Details] " _
& "(OrderID, ProductID, UnitPrice, Quantity, Discount) " _
& "Values (12, 23, 15.5, 12, 3/1000)"
Debug.Print .Execute("SELECT @@Identity").Collect(0)
' prints the autonumber of the newly inserted record
.Close
End With
Set c = Nothing
End Sub
Sorry should have mentioned that I am only using access and vba not vb6,
there is no connection string it opens, and if I try and create a new
connection string I get an error, I can do most of what you said with
the docmd.runsql except the SELECT @@IDENTITY I am unable to assign this
to a variable.

I have tried intNewID = docmd.runsql("SELECT @@Identity) but I get an
error expected function or variable error

Thanks for help so far
Eric
Aug 11 '06 #3

P: n/a
eric wrote:
Dim c As ADODB.Connection
Set c = New ADODB.Connection
With c
.ConnectionString = CurrentProject.BaseConnectionString
.Open
.Execute "INSERT INTO [Order Details] " _
& "(OrderID, ProductID, UnitPrice, Quantity, Discount) " _
& "Values (12, 23, 15.5, 12, 3/1000)"
Debug.Print .Execute("SELECT @@Identity").Collect(0)
' prints the autonumber of the newly inserted record
.Close
End With
Set c = Nothing
End Sub

Sorry should have mentioned that I am only using access and vba not vb6,
there is no connection string it opens, and if I try and create a new
connection string I get an error, I can do most of what you said with
the docmd.runsql except the SELECT @@IDENTITY I am unable to assign this
to a variable.

I have tried intNewID = docmd.runsql("SELECT @@Identity) but I get an
error expected function or variable error
This code should run in any version of Access from 2000 on. It is NOT
VB6 code.

Aug 11 '06 #4

P: n/a
On Fri, 11 Aug 2006 13:06:35 +0100, eric <er**@NoSpamForMe.comwrote:

You did set a reference to ADO, right?
-Tom.

>Dim c As ADODB.Connection
Set c = New ADODB.Connection
With c
.ConnectionString = CurrentProject.BaseConnectionString
.Open
.Execute "INSERT INTO [Order Details] " _
& "(OrderID, ProductID, UnitPrice, Quantity, Discount) " _
& "Values (12, 23, 15.5, 12, 3/1000)"
Debug.Print .Execute("SELECT @@Identity").Collect(0)
' prints the autonumber of the newly inserted record
.Close
End With
Set c = Nothing
End Sub

Sorry should have mentioned that I am only using access and vba not vb6,
there is no connection string it opens, and if I try and create a new
connection string I get an error, I can do most of what you said with
the docmd.runsql except the SELECT @@IDENTITY I am unable to assign this
to a variable.

I have tried intNewID = docmd.runsql("SELECT @@Identity) but I get an
error expected function or variable error

Thanks for help so far
Eric
Aug 11 '06 #5

P: n/a
You did set a reference to ADO, right?
-Tom.
Where would I set this reference?
sorry but I have very little experience with VB or VBA and databases, I
have been trying to read a ton of books to get upto speed, wish I could
speed read

The error I get is

-2147467259 The database has been placed in a state by user 'Admin' on
machine 'XYZ' that prevents it from being opened or locked.

Using Access 2003

I get this error when I the code hits .open

I checked properties and default open mode is set to Shared and default
record locking is set to no locks

and the open database using record level locking option is ticked

Any ideas?
Yeric
Aug 15 '06 #6

P: n/a

Try this in the After Update event of your main form
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("NameofAddresstable")
rst.AddNew
rst!CompanyID = Forms!NameofMainForm!CompanyID
rst.Update

Set rst = db.OpenRecordset("Nameofcontactstable")
rst.AddNew
rst!CompanyID = Forms!NameofMainForm!CompanyID
rst.Update

Set rst = Nothing
Set db = Nothing

Aug 16 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.