How can I do this please

[eric]

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

[Lyle Fairfield]

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

[eric]

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

[Lyle Fairfield]

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.

[Tom van Stiphout]

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

[eric]

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

[Rick]

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