Copying related records from several tables

[El Rebelde]

My database contains several related entities: Versions are made up of
Assemblies which are made up of Components.

I want to select a Version on a form, then make a similar new Version (only
the URN will change) I then want to copy each Assembly related to the old
Version, relating the new Assembly records to the new Version. Similarly, I
want to copy each Component record relating to each old Assembly record and
relate it to the new Assembly record.

I know I can't do it with temporary tables, append and update queries,
because I've tried. Does anyone have some sample code I could amend and
attach to a command button on the form?

 

[Allen Browne]

See:
Duplicate the record in form and subform
at:
http://allenbrowne.com/ser-57.html

The article assumes you have found the record you want do duplicate in the
form, and then click a button to duplicate the record. It does that by
appending a record to the RecordsetClone of the form to get the main record,
so you then have the new primary key value. It then uses an Append query to
duplicate the related records as well.

You seem to have an extra table:
Version => Assemblies
Assemblies => Components.

You could use the same code to duplicate the Version, but you would then
need to OpenRecordset on the Assemblies to AddNew there for each related
assembly, and then use the new primary key value to execute the Append query
to create the related Components.

 

[El Rebelde]

Thanks Allen

I've used your code to successfully duplicate the Version and related
Assemblies, but I don't quite understand your suggestion about how to deal
with the Components records. I'm guessing that it's some iterative process,
going through each assembly and running a similar append query, but I'm not
sure and when I think about it my head starts to spin!

 

[Allen Browne]

Yes.

The middle stage involves OpenRecordset() on a query statement that just
brings in the Assemblies for the record in the form, plus OpenRecordset() in
the same table to append the new records to. Loop through the source
records, with AddNew and Update on the target Recordset.

For each one, you will then execute a SQL statement to duplicate the
grandchild records (Components.)

 

[El Rebelde]

This is where I got to, but it won't work. I only really workm in tables
queries forms and reports. I think I may have got out of my depth?

Private Sub Command56_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the Version record and related records in
Assemblies and Components.
Dim strSql As String 'SQL statement.
Dim VerID As Long 'Primary key value of the new Version record.
Dim AssID As Long 'Primary key value of the new Assembly record.
Dim OldAssID As Long
Dim CompURN As Long
Dim Qty As Long

Dim assQry As Recordset

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
' If Me.NewRecord Then
' MsgBox "Select the record to duplicate."
'Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!ProjectURN = Me.ProjectURN
!Description = Me.Description
!Finish = Me.Finish
!Jointing = Me.Jointing
!Category = Me.Category
' !Furniture = Me.Furniture
'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
VerID = !VersionURN
' End If
Set assQry = OpenRecordset("SELECT AssemblyURN, Description,
ProjectURN, Finish, Jointing, Category, VersionURN) from [Assemblies] where
VersionURN = me.VersionURN")

Do While Not EOF("assQry")
'Get old Assembly Details
Set OldAssID = AssemblyURN
Set CompURN = ComponentURN
Set Qty = Quantity
'Make new Assembly record and add details
With Assemblies
.AddNew
.Fields("ComponentURN") = CompURN
.Fields("AssemblyURN") = OldAssID
.Fields("Quantity") = Qty
.Update
.Bookmark = .LastModified
Set NewAssID = AssemblyURN 'Set new AssemblyURN as variable
End With

'Select component records that have old AssemblyURN and copy
'into Component table with new AssemblyURN
strSql = "INSERT INTO [Components] ( AssemblyURN,
CatalogueURN, Quantity) " & _
"SELECT " & VerID & " As NewID, CatalogueURN, Quantity" & _
"FROM [Components] WHERE AssemblyURN = " & OldAssID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Loop

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"Command56_Click"
Resume Exit_Handler
End Sub

 

[Allen Browne]

The source query string for the middle level table needs the value
concatenated into the string:

"SELECT AssemblyURN, Description, ProjectURN, Finish, Jointing,
Category, VersionURN) from [Assemblies] where VersionURN = " & me.VersionURN
& ";"

You will also need a target recordset to append the values too.
At the top of your procedure:
Dim rsTarget As DAO.Recordset
and then
Set rsTarget = dbengine(0)(0).OpenRecordset("Assemblies", dbOpenDynaset,
dbAppendOnly)
This gives you a source recordset to loop through (your existing one), and a
target record to AddNew and Update.

(The switches for the target are not crucial, just helpful.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

This is where I got to, but it won't work. I only really workm in tables
queries forms and reports. I think I may have got out of my depth?

Private Sub Command56_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the Version record and related records in
Assemblies and Components.
Dim strSql As String 'SQL statement.
Dim VerID As Long 'Primary key value of the new Version record.
Dim AssID As Long 'Primary key value of the new Assembly record.
Dim OldAssID As Long
Dim CompURN As Long
Dim Qty As Long

Dim assQry As Recordset

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
' If Me.NewRecord Then
' MsgBox "Select the record to duplicate."
'Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!ProjectURN = Me.ProjectURN
!Description = Me.Description
!Finish = Me.Finish
!Jointing = Me.Jointing
!Category = Me.Category
' !Furniture = Me.Furniture
'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
VerID = !VersionURN
' End If
Set assQry = OpenRecordset("SELECT AssemblyURN, Description,
ProjectURN, Finish, Jointing, Category, VersionURN) from [Assemblies]
where
VersionURN = me.VersionURN")

Do While Not EOF("assQry")
'Get old Assembly Details
Set OldAssID = AssemblyURN
Set CompURN = ComponentURN
Set Qty = Quantity
'Make new Assembly record and add details
With Assemblies
.AddNew
.Fields("ComponentURN") = CompURN
.Fields("AssemblyURN") = OldAssID
.Fields("Quantity") = Qty
.Update
.Bookmark = .LastModified
Set NewAssID = AssemblyURN 'Set new AssemblyURN as variable
End With

'Select component records that have old AssemblyURN and copy
'into Component table with new AssemblyURN
strSql = "INSERT INTO [Components] ( AssemblyURN,
CatalogueURN, Quantity) " & _
"SELECT " & VerID & " As NewID, CatalogueURN, Quantity" & _
"FROM [Components] WHERE AssemblyURN = " & OldAssID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Loop

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"Command56_Click"
Resume Exit_Handler
End Sub

Yes.

The middle stage involves OpenRecordset() on a query statement that just
brings in the Assemblies for the record in the form, plus OpenRecordset()
in
the same table to append the new records to. Loop through the source
records, with AddNew and Update on the target Recordset.

For each one, you will then execute a SQL statement to duplicate the
grandchild records (Components.)

 

[El Rebelde]

Allen, I don't know where to add the string you've suggested. If I make
something similar to the strSql string, how will it make a recordset through
which I can iterate? Being at a loss, I thought I'd persist with the idea of
the assQry recordset:

Set assQry = db.OpenRecordset("SELECT AssemblyURN, Description, ProjectURN,
Finish, Jointing, Category, VersionURN from [Assemblies] where VersionURN =
me.VersionURN")

It returns the Runtime error 3061. Too few parameters. Expected 1.

I've checked the syntax and it seems to match the examples shown in the VB
Help.

Can you help by telling me what have I done wrong or explaining exactly
where the string suggested in your last post should go.

The source query string for the middle level table needs the value
concatenated into the string:

"SELECT AssemblyURN, Description, ProjectURN, Finish, Jointing,
Category, VersionURN) from [Assemblies] where VersionURN = " & me.VersionURN
& ";"

You will also need a target recordset to append the values too.
At the top of your procedure:
Dim rsTarget As DAO.Recordset
and then
Set rsTarget = dbengine(0)(0).OpenRecordset("Assemblies", dbOpenDynaset,
dbAppendOnly)
This gives you a source recordset to loop through (your existing one), and a
target record to AddNew and Update.

(The switches for the target are not crucial, just helpful.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

This is where I got to, but it won't work. I only really workm in tables
queries forms and reports. I think I may have got out of my depth?

Private Sub Command56_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the Version record and related records in
Assemblies and Components.
Dim strSql As String 'SQL statement.
Dim VerID As Long 'Primary key value of the new Version record.
Dim AssID As Long 'Primary key value of the new Assembly record.
Dim OldAssID As Long
Dim CompURN As Long
Dim Qty As Long

Dim assQry As Recordset

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
' If Me.NewRecord Then
' MsgBox "Select the record to duplicate."
'Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!ProjectURN = Me.ProjectURN
!Description = Me.Description
!Finish = Me.Finish
!Jointing = Me.Jointing
!Category = Me.Category
' !Furniture = Me.Furniture
'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
VerID = !VersionURN
' End If
Set assQry = OpenRecordset("SELECT AssemblyURN, Description,
ProjectURN, Finish, Jointing, Category, VersionURN) from [Assemblies]
where
VersionURN = me.VersionURN")

Do While Not EOF("assQry")
'Get old Assembly Details
Set OldAssID = AssemblyURN
Set CompURN = ComponentURN
Set Qty = Quantity
'Make new Assembly record and add details
With Assemblies
.AddNew
.Fields("ComponentURN") = CompURN
.Fields("AssemblyURN") = OldAssID
.Fields("Quantity") = Qty
.Update
.Bookmark = .LastModified
Set NewAssID = AssemblyURN 'Set new AssemblyURN as variable
End With

'Select component records that have old AssemblyURN and copy
'into Component table with new AssemblyURN
strSql = "INSERT INTO [Components] ( AssemblyURN,
CatalogueURN, Quantity) " & _
"SELECT " & VerID & " As NewID, CatalogueURN, Quantity" & _
"FROM [Components] WHERE AssemblyURN = " & OldAssID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Loop

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"Command56_Click"
Resume Exit_Handler
End Sub

Yes.

The middle stage involves OpenRecordset() on a query statement that just
brings in the Assemblies for the record in the form, plus OpenRecordset()
in
the same table to append the new records to. Loop through the source
records, with AddNew and Update on the target Recordset.

For each one, you will then execute a SQL statement to duplicate the
grandchild records (Components.)

Thanks Allen

I've used your code to successfully duplicate the Version and related
Assemblies, but I don't quite understand your suggestion about how to
deal
with the Components records. I'm guessing that it's some iterative
process,
going through each assembly and running a similar append query, but I'm
not
sure and when I think about it my head starts to spin!

:

My database contains several related entities: Versions are made up of
Assemblies which are made up of Components.

I want to select a Version on a form, then make a similar new Version
(only
the URN will change) I then want to copy each Assembly related to the
old
Version, relating the new Assembly records to the new Version.
Similarly,
I
want to copy each Component record relating to each old Assembly
record
and
relate it to the new Assembly record.

I know I can't do it with temporary tables, append and update queries,
because I've tried. Does anyone have some sample code I could amend
and
attach to a command button on the form?

 

[Allen Browne]

Concatentate the value into the string:
"SELECT AssemblyURN, Description, ProjectURN, " & _
"Finish, Jointing, Category, VersionURN from [Assemblies] " & _
"where VersionURN = " & me.VersionURN & ";"

If VersionURN is a Text field (not a Number type field), you need extra
quotes, as explained here:
http://allenbrowne.com/casu-17.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen, I don't know where to add the string you've suggested. If I make
something similar to the strSql string, how will it make a recordset
through
which I can iterate? Being at a loss, I thought I'd persist with the idea
of
the assQry recordset:

Set assQry = db.OpenRecordset("SELECT AssemblyURN, Description,
ProjectURN,
Finish, Jointing, Category, VersionURN from [Assemblies] where VersionURN
=
me.VersionURN")

It returns the Runtime error 3061. Too few parameters. Expected 1.

I've checked the syntax and it seems to match the examples shown in the VB
Help.

Can you help by telling me what have I done wrong or explaining exactly
where the string suggested in your last post should go.

The source query string for the middle level table needs the value
concatenated into the string:

"SELECT AssemblyURN, Description, ProjectURN, Finish, Jointing,
Category, VersionURN) from [Assemblies] where VersionURN = " &
me.VersionURN
& ";"

You will also need a target recordset to append the values too.
At the top of your procedure:
Dim rsTarget As DAO.Recordset
and then
Set rsTarget = dbengine(0)(0).OpenRecordset("Assemblies",
dbOpenDynaset,
dbAppendOnly)
This gives you a source recordset to loop through (your existing one),
and a
target record to AddNew and Update.

(The switches for the target are not crucial, just helpful.)

This is where I got to, but it won't work. I only really workm in
tables
queries forms and reports. I think I may have got out of my depth?

Private Sub Command56_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the Version record and related records in
Assemblies and Components.
Dim strSql As String 'SQL statement.
Dim VerID As Long 'Primary key value of the new Version
record.
Dim AssID As Long 'Primary key value of the new Assembly
record.
Dim OldAssID As Long
Dim CompURN As Long
Dim Qty As Long

Dim assQry As Recordset

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
' If Me.NewRecord Then
' MsgBox "Select the record to duplicate."
'Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!ProjectURN = Me.ProjectURN
!Description = Me.Description
!Finish = Me.Finish
!Jointing = Me.Jointing
!Category = Me.Category
' !Furniture = Me.Furniture
'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for
the
related records.
.Bookmark = .LastModified
VerID = !VersionURN
' End If
Set assQry = OpenRecordset("SELECT AssemblyURN, Description,
ProjectURN, Finish, Jointing, Category, VersionURN) from [Assemblies]
where
VersionURN = me.VersionURN")

Do While Not EOF("assQry")
'Get old Assembly Details
Set OldAssID = AssemblyURN
Set CompURN = ComponentURN
Set Qty = Quantity
'Make new Assembly record and add details
With Assemblies
.AddNew
.Fields("ComponentURN") = CompURN
.Fields("AssemblyURN") = OldAssID
.Fields("Quantity") = Qty
.Update
.Bookmark = .LastModified
Set NewAssID = AssemblyURN 'Set new AssemblyURN as
variable
End With

'Select component records that have old AssemblyURN and copy
'into Component table with new AssemblyURN
strSql = "INSERT INTO [Components] ( AssemblyURN,
CatalogueURN, Quantity) " & _
"SELECT " & VerID & " As NewID, CatalogueURN, Quantity"
& _
"FROM [Components] WHERE AssemblyURN = " & OldAssID &
";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Loop

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"Command56_Click"
Resume Exit_Handler
End Sub


:

Yes.

The middle stage involves OpenRecordset() on a query statement that
just
brings in the Assemblies for the record in the form, plus
OpenRecordset()
in
the same table to append the new records to. Loop through the source
records, with AddNew and Update on the target Recordset.

For each one, you will then execute a SQL statement to duplicate the
grandchild records (Components.)

Thanks Allen

I've used your code to successfully duplicate the Version and
related
Assemblies, but I don't quite understand your suggestion about how
to
deal
with the Components records. I'm guessing that it's some iterative
process,
going through each assembly and running a similar append query, but
I'm
not
sure and when I think about it my head starts to spin!

:

My database contains several related entities: Versions are made up
of
Assemblies which are made up of Components.

I want to select a Version on a form, then make a similar new
Version
(only
the URN will change) I then want to copy each Assembly related to
the
old
Version, relating the new Assembly records to the new Version.
Similarly,
I
want to copy each Component record relating to each old Assembly
record
and
relate it to the new Assembly record.

I know I can't do it with temporary tables, append and update
queries,
because I've tried. Does anyone have some sample code I could amend
and
attach to a command button on the form?

 

[El Rebelde]

OK Allen

Here's the bit I'm struggling with:

Set rsAssQry = New ADODB.Recordset
strSQLAss = "SELECT AssemblyURN, Description, ProjectURN, Finish, Jointing,
Category, VersionURN) from [Assemblies] where VersionURN = " & Me.VersionURN
& ";" ' Allen's string to get Assembly records related to Version

rsAssQry.Open strSQLAss ' My attempt to open the recordset which fails
with Error 3709

'This section designed to iterate through recordset
rsAssQry.MoveFirst
Do Until rsAssQry.EOF

'Get old Assembly Details
OldAssID = AssemblyURN
CompURN = ComponentURN
Qty = Quantity
'Make new Assembly record and add details
Set rsTarget = DBEngine(0)(0).OpenRecordset("Assemblies",
dbOpenDynaset, dbAppendOnly)
With Assemblies
.AddNew
.Fields("ComponentURN") = CompURN
.Fields("AssemblyURN") = OldAssID
.Fields("Quantity") = Qty
.Update
.Bookmark = .LastModified
Set NewAssID = AssemblyURN 'Set new AssemblyURN as variable
End With

'Select component records that have old AssemblyURN and copy
'into Component table with new AssemblyURN
strSql = "INSERT INTO [Components] ( AssemblyURN, CatalogueURN, Quantity) "
& _
"SELECT " & VerID & " As NewID, CatalogueURN, Quantity" & _
"FROM [Components] WHERE AssemblyURN = " & OldAssID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Loop

 

[Allen Browne]

There appears to b esomething wrong with the SQL statement, e.g.:
- It has a closing bracket without an opening bracket.
- If VersionURN is a Text field, you need extra quotes.

If it still fails, indicate which field the error message refers to.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

OK Allen

Here's the bit I'm struggling with:

Set rsAssQry = New ADODB.Recordset
strSQLAss = "SELECT AssemblyURN, Description, ProjectURN, Finish,
Jointing,
Category, VersionURN) from [Assemblies] where VersionURN = " &
Me.VersionURN
& ";" ' Allen's string to get Assembly records related to Version

rsAssQry.Open strSQLAss ' My attempt to open the recordset which fails
with Error 3709

'This section designed to iterate through recordset
rsAssQry.MoveFirst
Do Until rsAssQry.EOF

'Get old Assembly Details
OldAssID = AssemblyURN
CompURN = ComponentURN
Qty = Quantity
'Make new Assembly record and add details
Set rsTarget = DBEngine(0)(0).OpenRecordset("Assemblies",
dbOpenDynaset, dbAppendOnly)
With Assemblies
.AddNew
.Fields("ComponentURN") = CompURN
.Fields("AssemblyURN") = OldAssID
.Fields("Quantity") = Qty
.Update
.Bookmark = .LastModified
Set NewAssID = AssemblyURN 'Set new AssemblyURN as variable
End With

'Select component records that have old AssemblyURN and copy
'into Component table with new AssemblyURN
strSql = "INSERT INTO [Components] ( AssemblyURN, CatalogueURN, Quantity)
"
& _
"SELECT " & VerID & " As NewID, CatalogueURN, Quantity" & _
"FROM [Components] WHERE AssemblyURN = " & OldAssID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Loop

Concatentate the value into the string:
"SELECT AssemblyURN, Description, ProjectURN, " & _
"Finish, Jointing, Category, VersionURN from [Assemblies] " & _
"where VersionURN = " & me.VersionURN & ";"

If VersionURN is a Text field (not a Number type field), you need extra
quotes, as explained here:
http://allenbrowne.com/casu-17.html

 

[El Rebelde]

Thanks Allen. I've removed the offending bracket. The program fails at the line
rsAssQry.Open strSQLAss

and the message is:

Run-time error '3709':
The connection cannot be used to perform this operation. It os either closed
or invalid in this context.

There appears to b esomething wrong with the SQL statement, e.g.:
- It has a closing bracket without an opening bracket.
- If VersionURN is a Text field, you need extra quotes.

If it still fails, indicate which field the error message refers to.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

OK Allen

Here's the bit I'm struggling with:

Set rsAssQry = New ADODB.Recordset
strSQLAss = "SELECT AssemblyURN, Description, ProjectURN, Finish,
Jointing,
Category, VersionURN) from [Assemblies] where VersionURN = " &
Me.VersionURN
& ";" ' Allen's string to get Assembly records related to Version

rsAssQry.Open strSQLAss ' My attempt to open the recordset which fails
with Error 3709

'This section designed to iterate through recordset
rsAssQry.MoveFirst
Do Until rsAssQry.EOF

'Get old Assembly Details
OldAssID = AssemblyURN
CompURN = ComponentURN
Qty = Quantity
'Make new Assembly record and add details
Set rsTarget = DBEngine(0)(0).OpenRecordset("Assemblies",
dbOpenDynaset, dbAppendOnly)
With Assemblies
.AddNew
.Fields("ComponentURN") = CompURN
.Fields("AssemblyURN") = OldAssID
.Fields("Quantity") = Qty
.Update
.Bookmark = .LastModified
Set NewAssID = AssemblyURN 'Set new AssemblyURN as variable
End With

'Select component records that have old AssemblyURN and copy
'into Component table with new AssemblyURN
strSql = "INSERT INTO [Components] ( AssemblyURN, CatalogueURN, Quantity)
"
& _
"SELECT " & VerID & " As NewID, CatalogueURN, Quantity" & _
"FROM [Components] WHERE AssemblyURN = " & OldAssID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Loop

Concatentate the value into the string:
"SELECT AssemblyURN, Description, ProjectURN, " & _
"Finish, Jointing, Category, VersionURN from [Assemblies] " & _
"where VersionURN = " & me.VersionURN & ";"

If VersionURN is a Text field (not a Number type field), you need extra
quotes, as explained here:
http://allenbrowne.com/casu-17.html

 

[Douglas J. Steele]

I don't see where you've provided the connection information.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks Allen. I've removed the offending bracket. The program fails at the
line
rsAssQry.Open strSQLAss

and the message is:

Run-time error '3709':
The connection cannot be used to perform this operation. It os either
closed
or invalid in this context.

There appears to b esomething wrong with the SQL statement, e.g.:
- It has a closing bracket without an opening bracket.
- If VersionURN is a Text field, you need extra quotes.

If it still fails, indicate which field the error message refers to.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

OK Allen

Here's the bit I'm struggling with:

Set rsAssQry = New ADODB.Recordset
strSQLAss = "SELECT AssemblyURN, Description, ProjectURN, Finish,
Jointing,
Category, VersionURN) from [Assemblies] where VersionURN = " &
Me.VersionURN
& ";" ' Allen's string to get Assembly records related to Version

rsAssQry.Open strSQLAss ' My attempt to open the recordset which
fails
with Error 3709

'This section designed to iterate through recordset
rsAssQry.MoveFirst
Do Until rsAssQry.EOF

'Get old Assembly Details
OldAssID = AssemblyURN
CompURN = ComponentURN
Qty = Quantity
'Make new Assembly record and add details
Set rsTarget = DBEngine(0)(0).OpenRecordset("Assemblies",
dbOpenDynaset, dbAppendOnly)
With Assemblies
.AddNew
.Fields("ComponentURN") = CompURN
.Fields("AssemblyURN") = OldAssID
.Fields("Quantity") = Qty
.Update
.Bookmark = .LastModified
Set NewAssID = AssemblyURN 'Set new AssemblyURN as variable
End With

'Select component records that have old AssemblyURN and copy
'into Component table with new AssemblyURN
strSql = "INSERT INTO [Components] ( AssemblyURN, CatalogueURN,
Quantity)
"
& _
"SELECT " & VerID & " As NewID, CatalogueURN, Quantity" & _
"FROM [Components] WHERE AssemblyURN = " & OldAssID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Loop


:

Concatentate the value into the string:
"SELECT AssemblyURN, Description, ProjectURN, " & _
"Finish, Jointing, Category, VersionURN from [Assemblies] " & _
"where VersionURN = " & me.VersionURN & ";"

If VersionURN is a Text field (not a Number type field), you need
extra
quotes, as explained here:
http://allenbrowne.com/casu-17.html

 

[El Rebelde]

I didn't Doug. I'm really doing this by trial and error, mostly the latter,
I'm afraid! I copied the line from an example in VB help (Open and Close
Methods Example (VB):

Set rstEmployees = New ADODB.Recordset
strSQLEmployees = "employee"
rstEmployees.Open strSQLEmployees, Cnxn, adOpenKeyset, adLockOptimistic,
adCmdTable

I thought that the connection was already established so wouldn't need to be
defined. OK, I was wrong, (and wildly optimistic) but I can't work out the
syntax of how to define it anyway.

Is there any other simpler way of opening the recordset, so that I can
iterate through the records?

 

[Allen Browne]

Try:
CurrentProject.Connection
in place of:
Cnxn
i.e. as the connection object.

 

[El Rebelde]

Things have moved on, in that I've found a way to open the recordset, but
I've now got a new problem. The program gets stuck in the loop that iterates
through the recordset, creating thousands of duplicate Assembly records. I've
put in another loop to count the number of records in the recordset, which
indicates that the SELECT statement is erroneous somehow. Can you tell me
where I've gone wrong?

'On Error GoTo Err_Handler
'Purpose: Duplicate the Version record and related records in
' Assemblies and Components.
Set db = CurrentDb
Dim strSql As String 'SQL statement.
Dim VerID As Long 'Primary key value of the new Version record.
Dim AssID As Long 'Primary key value of the new Assembly record.
Dim OldAssID As Long
Dim CompURN As Long
Dim Qty As Long
Dim rsTarget As DAO.Recordset
Dim rsAssQry As ADODB.Recordset
Dim strSQLAss As String
Dim intRcdCnt As Integer
Dim strDesc As String
Dim strFnsh As String
Dim strJnt As String
Dim strCat As String

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
' If Me.NewRecord Then
' MsgBox "Select the record to duplicate."
'Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!ProjectURN = Me.ProjectURN
!Description = Me.Description
!Finish = Me.Finish
!Jointing = Me.Jointing
!Category = Me.Category
'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the
' related records.
.Bookmark = .LastModified
VerID = !VersionURN
MsgBox ("Old Version ID: " & Me!VersionURN)
MsgBox ("New Version ID: " & VerID)

'Get Assembly records relating to old Version
Set rsAssQry = New ADODB.Recordset
strSQLAss = "SELECT AssemblyURN, Description, ProjectURN, Finish,
Jointing, Category, VersionURN from [Assemblies] where VersionURN = " &
Me.VersionURN & ";"
rsAssQry.Open strSQLAss, CurrentProject.Connection

rsAssQry.MoveFirst
OldAssID = AssemblyURN
intRcdCnt = 1
MsgBox ("Record count = " & intRcdCnt)

'Loop to test why thousands of Assembly records were created
Do Until rsAssQry.EOF 'Why do we never reach the end of file? Recordset
should only contain 5 records for VersionURN =1
intRcdCnt = intRcdCnt + 1
MsgBox ("Record count = " & intRcdCnt & " " & OldAssID)

Loop

 

[Allen Browne]

Should there be a MoveNext or something in that loop?