Multiple Record Copy Transaction...

[davidgordon]

Hi,

I need some pointers/help on how to do the following if it possible:

In my access db, I have the following:

Tables: Products, Sub-Assembly, Product-Pack
Table, Products
Each Product has 1 ID

Table, Sub-Assembly
Each Product is made up of many Sub-Assemblies, each with their own
seperate ID, and each linked via ProductID

Table, Product-Pack
Each Product is made up of many Product-Pack items, each with their own
seperate ID, and each linked via ProductID

_______________ _______________ _______________ _______
MainForm Source: Products (Table)
Sub-Form Source: Sub-Assembly (Table), linked via ProductID
Linked-Form Source: Product-Pack (Table), linked to MainForm via
ProductID)

Stage 1:
Have a button which creates a pop-up asking:
"Create New or Copy Existing Product?"
This bit is fine. Create New option is fine

Copy Existing is slightly harder:

First there will be a small pop-up form which lists all the existing
products.
The user will then select the product they need to copy. This then will
store the ProductID of the existing Product to copy. They will be asked
to give this New Product a Name, which will replace the one in the
copied record.

1/ I need to copy the record from Products where the ID matches the
selected ProductID...and replace it with the next new ID, How ?

2/ I need to copy ALL the records from Sub-Assembly where the ProductID
matches that of the initial selected product and then replace the
ProductID of those records with the New ProductID, whilst also creating
new recordIDs for each record, How ?
Obviously, once I have managed to do the above stage 2, stage 3 below
is exactly the same, just for a different table :-)

3/ I need to copy ALL the records from Product-Pack where the ProductID
matches that of the initial selected product and then replace the
ProductID of ALL those records with the New ProductID, whilst also
creating new recordIDs for each record,How ?

I'm sure this is easy if you know how....I do not and get very
confused.
I will seriously appreciate any help you can offer.

Thanks
David Gordon

[Allen Browne]

You will need to be comfortable with writing VBA code to achieve this.

You could place a command button on your Product form to "Duplicate this
record". The button will create the new product by using AddNew on the
form's RecordsetClone. This lets you get the new ProductID (which I assume
is an AutoNumber field.) You can then use that value in an Append query
statement to create the related records in the related tables.

This example shows how to duplicate an Invoice, and the line items from the
subform:

Private Sub cmdDupe_Click()
Dim strSql As String
Dim db As DAO.Database
Dim lngInvID As Long

Set db = DBEngine(0)(0)

If Me.Dirty Then
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else

'Duplicate the main record
With Me.RecordsetClo ne
.AddNew
!InvoiceDate = Date
!ClientID = Me.ClientID
'etc for other fields.
.Update
.Bookmark = .LastModified
lngInvID = !InvoiceID

'Duplicate the related records.
If Me.fInvoiceDeta il.Form.Records etClone.RecordC ount > 0 Then
strSql = "INSERT INTO tInvoiceDetail ( InvoiceID, Item,
Amount) " & _
"SELECT " & lngInvID & " As NewInvoiceID,
tInvoiceDetail. Item, " & _
"tInvoiceDetail .Amount FROM tInvoiceDetail " & _
"WHERE (tInvoiceDetail .InvoiceID = " & Me.InvoiceID &
");"
db.Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

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

Set db = Nothing
End Sub
Note: If you are stuck getting the SQL string correct for your tables, mock
up a query, change to Append (Append on Query menu), switch to SQL View
(View menu), and copy what you see there.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<da*********@sc ene-double.co.uk> wrote in message
news:11******** **************@ g47g2000cwa.goo glegroups.com.. .

I need some pointers/help on how to do the following if it possible:

In my access db, I have the following:

Tables: Products, Sub-Assembly, Product-Pack
Table, Products
Each Product has 1 ID

Table, Sub-Assembly
Each Product is made up of many Sub-Assemblies, each with their own
seperate ID, and each linked via ProductID

Table, Product-Pack
Each Product is made up of many Product-Pack items, each with their own
seperate ID, and each linked via ProductID

_______________ _______________ _______________ _______
MainForm Source: Products (Table)
Sub-Form Source: Sub-Assembly (Table), linked via ProductID
Linked-Form Source: Product-Pack (Table), linked to MainForm via
ProductID)

Stage 1:
Have a button which creates a pop-up asking:
"Create New or Copy Existing Product?"
This bit is fine. Create New option is fine

Copy Existing is slightly harder:

First there will be a small pop-up form which lists all the existing
products.
The user will then select the product they need to copy. This then will
store the ProductID of the existing Product to copy. They will be asked
to give this New Product a Name, which will replace the one in the
copied record.

1/ I need to copy the record from Products where the ID matches the
selected ProductID...and replace it with the next new ID, How ?

2/ I need to copy ALL the records from Sub-Assembly where the ProductID
matches that of the initial selected product and then replace the
ProductID of those records with the New ProductID, whilst also creating
new recordIDs for each record, How ?
Obviously, once I have managed to do the above stage 2, stage 3 below
is exactly the same, just for a different table :-)

3/ I need to copy ALL the records from Product-Pack where the ProductID
matches that of the initial selected product and then replace the
ProductID of ALL those records with the New ProductID, whilst also
creating new recordIDs for each record,How ?

I'm sure this is easy if you know how....I do not and get very
confused.
I will seriously appreciate any help you can offer.

Thanks
David Gordon

[davidgordon]

Hi Allen,

Thanks so much for your prompt reply :-)

You may have to bear with me on this one.
So far, i've got my original Products MainForm.
On this form I now have a 'Duplicate' button, which copies the selected
product and then lets me enter a new name for the new product. I can
now easily capture the new ProductId. Obviously the sub-form is empty
on this new record for now.

I created a test table call Sub-Assembly_Copy, just to play with.
I created an append query to select all records from Sub-Assembly where
ProductID = MainForm ProductID, which when run pulls up say 2 records
from Sub-Assembly and then dumps them nicely into Sub-Assembly_Copy.

The Problem. When the required records are appended, they carry the
same unique record ids ?. Lets say there are 10 Sub-Assemblies for the
selected Product A, each one has it's own id in the Sub-Assembly table.
I will require a new ID for each record. They should each be new
records with new ids? I then need to replace the ProductID in each of
those 10 new records with the new productID....an d then my task is
done...

Any further help you can offer would make me very happy :-)

Thanks again
David Gordon

Allen Browne wrote:

You will need to be comfortable with writing VBA code to achieve this.

You could place a command button on your Product form to "Duplicate this
record". The button will create the new product by using AddNew on the
form's RecordsetClone. This lets you get the new ProductID (which I assume
is an AutoNumber field.) You can then use that value in an Append query
statement to create the related records in the related tables.

This example shows how to duplicate an Invoice, and the line items from the
subform:

Private Sub cmdDupe_Click()
Dim strSql As String
Dim db As DAO.Database
Dim lngInvID As Long

Set db = DBEngine(0)(0)

If Me.Dirty Then
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else

'Duplicate the main record
With Me.RecordsetClo ne
.AddNew
!InvoiceDate = Date
!ClientID = Me.ClientID
'etc for other fields.
.Update
.Bookmark = .LastModified
lngInvID = !InvoiceID

'Duplicate the related records.
If Me.fInvoiceDeta il.Form.Records etClone.RecordC ount > 0 Then
strSql = "INSERT INTO tInvoiceDetail ( InvoiceID, Item,
Amount) " & _
"SELECT " & lngInvID & " As NewInvoiceID,
tInvoiceDetail. Item, " & _
"tInvoiceDetail .Amount FROM tInvoiceDetail " & _
"WHERE (tInvoiceDetail .InvoiceID = " & Me.InvoiceID &
");"
db.Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

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

Set db = Nothing
End Sub
Note: If you are stuck getting the SQL string correct for your tables, mock
up a query, change to Append (Append on Query menu), switch to SQL View
(View menu), and copy what you see there.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<da*********@sc ene-double.co.uk> wrote in message
news:11******** **************@ g47g2000cwa.goo glegroups.com.. .

I need some pointers/help on how to do the following if it possible:

In my access db, I have the following:

Tables: Products, Sub-Assembly, Product-Pack
Table, Products
Each Product has 1 ID

Table, Sub-Assembly
Each Product is made up of many Sub-Assemblies, each with their own
seperate ID, and each linked via ProductID

Table, Product-Pack
Each Product is made up of many Product-Pack items, each with their own
seperate ID, and each linked via ProductID

_______________ _______________ _______________ _______
MainForm Source: Products (Table)
Sub-Form Source: Sub-Assembly (Table), linked via ProductID
Linked-Form Source: Product-Pack (Table), linked to MainForm via
ProductID)

Stage 1:
Have a button which creates a pop-up asking:
"Create New or Copy Existing Product?"
This bit is fine. Create New option is fine

Copy Existing is slightly harder:

First there will be a small pop-up form which lists all the existing
products.
The user will then select the product they need to copy. This then will
store the ProductID of the existing Product to copy. They will be asked
to give this New Product a Name, which will replace the one in the
copied record.

1/ I need to copy the record from Products where the ID matches the
selected ProductID...and replace it with the next new ID, How ?

2/ I need to copy ALL the records from Sub-Assembly where the ProductID
matches that of the initial selected product and then replace the
ProductID of those records with the New ProductID, whilst also creating
new recordIDs for each record, How ?
Obviously, once I have managed to do the above stage 2, stage 3 below
is exactly the same, just for a different table :-)

3/ I need to copy ALL the records from Product-Pack where the ProductID
matches that of the initial selected product and then replace the
ProductID of ALL those records with the New ProductID, whilst also
creating new recordIDs for each record,How ?

I'm sure this is easy if you know how....I do not and get very
confused.
I will seriously appreciate any help you can offer.

Thanks
David Gordon

[Allen Browne]

David, what I suggested was that you copy the current record in the form
into a new record in the form's RecordsetClone. If you want the new product
name to be blank, then don't assign it anything in the AddNew block in the
code. If you want it to be "Copy of product 43" then you can assign it that
in the code.

After that, the code gets the primary key value for the new record, and
saves it in the variable lngInvID. (Use any name you want for this
variable.) Then the INSERT string incorporates that value, so that when you
duplicate the related child records, they are related to the new number that
just got created in the first stage of the operation. You can see where that
number gets concatenated into the strSql. When you execute that string, all
10 subassemblies are copied (that's determined by the WHERE clause, which
matches the primary key of the main form), and so 10 records are created in
the related table.

Hope you can adapt that code to your specific table and field names.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<da*********@sc ene-double.co.uk> wrote in message
news:11******** *************@g 49g2000cwa.goog legroups.com...

Hi Allen,

Thanks so much for your prompt reply :-)

You may have to bear with me on this one.
So far, i've got my original Products MainForm.
On this form I now have a 'Duplicate' button, which copies the selected
product and then lets me enter a new name for the new product. I can
now easily capture the new ProductId. Obviously the sub-form is empty
on this new record for now.

I created a test table call Sub-Assembly_Copy, just to play with.
I created an append query to select all records from Sub-Assembly where
ProductID = MainForm ProductID, which when run pulls up say 2 records
from Sub-Assembly and then dumps them nicely into Sub-Assembly_Copy.

The Problem. When the required records are appended, they carry the
same unique record ids ?. Lets say there are 10 Sub-Assemblies for the
selected Product A, each one has it's own id in the Sub-Assembly table.
I will require a new ID for each record. They should each be new
records with new ids? I then need to replace the ProductID in each of
those 10 new records with the new productID....an d then my task is
done...

Any further help you can offer would make me very happy :-)

Thanks again
David Gordon

Allen Browne wrote:

You will need to be comfortable with writing VBA code to achieve this.

You could place a command button on your Product form to "Duplicate this
record". The button will create the new product by using AddNew on the
form's RecordsetClone. This lets you get the new ProductID (which I
assume
is an AutoNumber field.) You can then use that value in an Append query
statement to create the related records in the related tables.

This example shows how to duplicate an Invoice, and the line items from
the
subform:

Private Sub cmdDupe_Click()
Dim strSql As String
Dim db As DAO.Database
Dim lngInvID As Long

Set db = DBEngine(0)(0)

If Me.Dirty Then
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else

'Duplicate the main record
With Me.RecordsetClo ne
.AddNew
!InvoiceDate = Date
!ClientID = Me.ClientID
'etc for other fields.
.Update
.Bookmark = .LastModified
lngInvID = !InvoiceID

'Duplicate the related records.
If Me.fInvoiceDeta il.Form.Records etClone.RecordC ount > 0 Then
strSql = "INSERT INTO tInvoiceDetail ( InvoiceID, Item,
Amount) " & _
"SELECT " & lngInvID & " As NewInvoiceID,
tInvoiceDetail. Item, " & _
"tInvoiceDetail .Amount FROM tInvoiceDetail " & _
"WHERE (tInvoiceDetail .InvoiceID = " & Me.InvoiceID &
");"
db.Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

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

Set db = Nothing
End Sub
Note: If you are stuck getting the SQL string correct for your tables,
mock
up a query, change to Append (Append on Query menu), switch to SQL View
(View menu), and copy what you see there.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<da*********@sc ene-double.co.uk> wrote in message
news:11******** **************@ g47g2000cwa.goo glegroups.com.. .

>
> I need some pointers/help on how to do the following if it possible:
>
> In my access db, I have the following:
>
> Tables: Products, Sub-Assembly, Product-Pack
>
>
> Table, Products
> Each Product has 1 ID
>
> Table, Sub-Assembly
> Each Product is made up of many Sub-Assemblies, each with their own
> seperate ID, and each linked via ProductID
>
> Table, Product-Pack
> Each Product is made up of many Product-Pack items, each with their own
> seperate ID, and each linked via ProductID
>
> _______________ _______________ _______________ _______
> MainForm Source: Products (Table)
> Sub-Form Source: Sub-Assembly (Table), linked via ProductID
> Linked-Form Source: Product-Pack (Table), linked to MainForm via
> ProductID)
>
> Stage 1:
> Have a button which creates a pop-up asking:
> "Create New or Copy Existing Product?"
> This bit is fine. Create New option is fine
>
> Copy Existing is slightly harder:
>
> First there will be a small pop-up form which lists all the existing
> products.
> The user will then select the product they need to copy. This then will
> store the ProductID of the existing Product to copy. They will be asked
> to give this New Product a Name, which will replace the one in the
> copied record.
>
> 1/ I need to copy the record from Products where the ID matches the
> selected ProductID...and replace it with the next new ID, How ?
>
> 2/ I need to copy ALL the records from Sub-Assembly where the ProductID
> matches that of the initial selected product and then replace the
> ProductID of those records with the New ProductID, whilst also creating
> new recordIDs for each record, How ?
>
>
> Obviously, once I have managed to do the above stage 2, stage 3 below
> is exactly the same, just for a different table :-)
>
> 3/ I need to copy ALL the records from Product-Pack where the ProductID
> matches that of the initial selected product and then replace the
> ProductID of ALL those records with the New ProductID, whilst also
> creating new recordIDs for each record,How ?
>
> I'm sure this is easy if you know how....I do not and get very
> confused.
> I will seriously appreciate any help you can offer.
>
> Thanks
>
>
> David Gordon

[davidgordon]

Thanks Allen,

Nearly there...

I cannot quite get the SQL correct:

You have:

If Me.fInvoiceDeta il.Form.Records etClone.RecordC ount > 0 Then
strSql = "INSERT INTO tInvoiceDetail ( InvoiceID, Item,
Amount) " & _
"SELECT " & lngInvID & " As NewInvoiceID,
tInvoiceDetail. Item, " & _
"tInvoiceDetail .Amount FROM tInvoiceDetail " & _
"WHERE (tInvoiceDetail .InvoiceID = " & Me.InvoiceID
&
");"

_______________ _______________ _______

I then tried:

If Me.SubBuildLine .Form.Recordset Clone.RecordCou nt > 0 Then
strSql = "INSERT INTO SubBuildLine (ProductID,
Quantity,
SubID) " & _
"SELECT " & lngInvID & " as ProductID ???),
SubBuildLine.Qu antity, " & _
"SubBuildLine.S ubID FROM SubBuildLine " & _
"WHERE ( SubBuildLine.Pr oductID = " & Me.InvoiceID
&
");"

_______________ _______________ _______________ _______________ ___

This did not seem to work, I got a new product, but with no records in
the subform.
Perhaps I am confused where you state: ......As NewInvoiceID ???

Please can you look at this for me.
The fields I require in the table 'subbuildline' are:
'SubID', 'Quantity' and 'ProductID', where ProductID is the new
ProductID and each of these records auto gets it's own unqique ID.

Thanks again Allen.....your so kind to help people like myself who
could only aspire to know as much as you :-)

[Allen Browne]

Yes. Something like this:
strSql = "INSERT INTO SubBuildLine (ProductID, Quantity, SubID) " & _
"SELECT " & lngInvID & " as ProductID, Quantity, SubID FROM SubBuildLine "
& _
"WHERE ( SubBuildLine.Pr oductID = " & Me.ProductID & ");"

You will need extra quotes if your ProductID field is a Text field (not a
Number field.)

You can mock up a query to get Access to show you what the SQL statement
should look like.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<da*********@sc ene-double.co.uk> wrote in message
news:11******** **************@ g44g2000cwa.goo glegroups.com.. .

Thanks Allen,

Nearly there...

I cannot quite get the SQL correct:

You have:

If Me.fInvoiceDeta il.Form.Records etClone.RecordC ount > 0 Then
strSql = "INSERT INTO tInvoiceDetail ( InvoiceID, Item,
Amount) " & _
"SELECT " & lngInvID & " As NewInvoiceID,
tInvoiceDetail. Item, " & _
"tInvoiceDetail .Amount FROM tInvoiceDetail " & _
"WHERE (tInvoiceDetail .InvoiceID = " & Me.InvoiceID
&
");"

_______________ _______________ _______

I then tried:

If Me.SubBuildLine .Form.Recordset Clone.RecordCou nt > 0 Then
strSql = "INSERT INTO SubBuildLine (ProductID,
Quantity,
SubID) " & _
"SELECT " & lngInvID & " as ProductID ???),
SubBuildLine.Qu antity, " & _
"SubBuildLine.S ubID FROM SubBuildLine " & _
"WHERE ( SubBuildLine.Pr oductID = " & Me.InvoiceID
&
");"

_______________ _______________ _______________ _______________ ___

This did not seem to work, I got a new product, but with no records in
the subform.
Perhaps I am confused where you state: ......As NewInvoiceID ???

Please can you look at this for me.
The fields I require in the table 'subbuildline' are:
'SubID', 'Quantity' and 'ProductID', where ProductID is the new
ProductID and each of these records auto gets it's own unqique ID.

Thanks again Allen.....your so kind to help people like myself who
could only aspire to know as much as you :-)

[davidgordon]

Hi Allen,

I just cannot seem to get this working.
I can get the main product duplicated, no problem.

I've tried coying the sub-records with sql as per your code, no luck.
I've tried copying the sub-records with the same method as for the main
product, no luck.
I've tried copying the sub-records by running an append query. This
creates the correct number of new records in the table, except I
cannot get the new ProductID, it always takes the original ?

In your code

.Bookmark = .LastModified
lngInvID = !InvoiceID

somehow 'lngInvID' always seems to be '2' ?, this is when the Original
ProductID is something like 632, lngInvID should then become 633, but
it does'nt.

If I refresh the form after the bookmark, the form displays the data
for ProductID '2' ???? why ??

Also when I leave the .bookmark statement to run, I get a message
stating the record is deleted.

Where am I going wrong

Thanks Allen
David

Allen Browne wrote:

Yes. Something like this:
strSql = "INSERT INTO SubBuildLine (ProductID, Quantity, SubID) " & _
"SELECT " & lngInvID & " as ProductID, Quantity, SubID FROM SubBuildLine "
& _
"WHERE ( SubBuildLine.Pr oductID = " & Me.ProductID & ");"

You will need extra quotes if your ProductID field is a Text field (not a
Number field.)

You can mock up a query to get Access to show you what the SQL statement
should look like.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<da*********@sc ene-double.co.uk> wrote in message
news:11******** **************@ g44g2000cwa.goo glegroups.com.. .

Thanks Allen,

Nearly there...

I cannot quite get the SQL correct:

You have:

If Me.fInvoiceDeta il.Form.Records etClone.RecordC ount > 0 Then
strSql = "INSERT INTO tInvoiceDetail ( InvoiceID, Item,
Amount) " & _
"SELECT " & lngInvID & " As NewInvoiceID,
tInvoiceDetail. Item, " & _
"tInvoiceDetail .Amount FROM tInvoiceDetail " & _
"WHERE (tInvoiceDetail .InvoiceID = " & Me.InvoiceID
&
");"

_______________ _______________ _______

I then tried:

If Me.SubBuildLine .Form.Recordset Clone.RecordCou nt > 0 Then
strSql = "INSERT INTO SubBuildLine (ProductID,
Quantity,
SubID) " & _
"SELECT " & lngInvID & " as ProductID ???),
SubBuildLine.Qu antity, " & _
"SubBuildLine.S ubID FROM SubBuildLine " & _
"WHERE ( SubBuildLine.Pr oductID = " & Me.InvoiceID
&
");"

_______________ _______________ _______________ _______________ ___

This did not seem to work, I got a new product, but with no records in
the subform.
Perhaps I am confused where you state: ......As NewInvoiceID ???

Please can you look at this for me.
The fields I require in the table 'subbuildline' are:
'SubID', 'Quantity' and 'ProductID', where ProductID is the new
ProductID and each of these records auto gets it's own unqique ID.

Thanks again Allen.....your so kind to help people like myself who
could only aspire to know as much as you :-)

[Allen Browne]

Do you have a field named InvoiceID in your form?

Or would you save your primary key value like this:
lngInvID = !ProductID

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<da*********@sc ene-double.co.uk> wrote in message
news:11******** ************@f1 4g2000cwb.googl egroups.com...

Hi Allen,

I just cannot seem to get this working.
I can get the main product duplicated, no problem.

I've tried coying the sub-records with sql as per your code, no luck.
I've tried copying the sub-records with the same method as for the main
product, no luck.
I've tried copying the sub-records by running an append query. This
creates the correct number of new records in the table, except I
cannot get the new ProductID, it always takes the original ?

In your code

.Bookmark = .LastModified
lngInvID = !InvoiceID

somehow 'lngInvID' always seems to be '2' ?, this is when the Original
ProductID is something like 632, lngInvID should then become 633, but
it does'nt.

If I refresh the form after the bookmark, the form displays the data
for ProductID '2' ???? why ??

Also when I leave the .bookmark statement to run, I get a message
stating the record is deleted.

Where am I going wrong

Thanks Allen
David

Allen Browne wrote:

Yes. Something like this:
strSql = "INSERT INTO SubBuildLine (ProductID, Quantity, SubID) " & _
"SELECT " & lngInvID & " as ProductID, Quantity, SubID FROM SubBuildLine
"
& _
"WHERE ( SubBuildLine.Pr oductID = " & Me.ProductID & ");"

You will need extra quotes if your ProductID field is a Text field (not a
Number field.)

You can mock up a query to get Access to show you what the SQL statement
should look like.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<da*********@sc ene-double.co.uk> wrote in message
news:11******** **************@ g44g2000cwa.goo glegroups.com.. .

> Thanks Allen,
>
> Nearly there...
>
> I cannot quite get the SQL correct:
>
> You have:
>
> If Me.fInvoiceDeta il.Form.Records etClone.RecordC ount > 0 Then
> strSql = "INSERT INTO tInvoiceDetail ( InvoiceID, Item,
> Amount) " & _
> "SELECT " & lngInvID & " As NewInvoiceID,
> tInvoiceDetail. Item, " & _
> "tInvoiceDetail .Amount FROM tInvoiceDetail " & _
> "WHERE (tInvoiceDetail .InvoiceID = " & Me.InvoiceID
> &
> ");"
>
> _______________ _______________ _______
>
> I then tried:
>
> If Me.SubBuildLine .Form.Recordset Clone.RecordCou nt > 0 Then
> strSql = "INSERT INTO SubBuildLine (ProductID,
> Quantity,
> SubID) " & _
> "SELECT " & lngInvID & " as ProductID ???),
> SubBuildLine.Qu antity, " & _
> "SubBuildLine.S ubID FROM SubBuildLine " & _
> "WHERE ( SubBuildLine.Pr oductID = " & Me.InvoiceID
> &
> ");"
>
> _______________ _______________ _______________ _______________ ___
>
> This did not seem to work, I got a new product, but with no records in
> the subform.
> Perhaps I am confused where you state: ......As NewInvoiceID ???
>
> Please can you look at this for me.
> The fields I require in the table 'subbuildline' are:
> 'SubID', 'Quantity' and 'ProductID', where ProductID is the new
> ProductID and each of these records auto gets it's own unqique ID.
>
> Thanks again Allen.....your so kind to help people like myself who
> could only aspire to know as much as you :-)

[davidgordon]

Hi Allen,

In the 2 tables in question I have the following:

Table: Products
PK: ProductID (AutoNumber)
Other Product fields....

Table: SubBuildLine
PK: SubBuildLineID (AutoNumber)

Fields required to copy over:
SubID
ProductID (The new one)
Quantity

_______________ _______________

I just use something like 'NewProductID' to hold the new ProductID,
i.e. NewProductID = !ProductID.

If I print out the variable or !ProductID, I get '2' ?? (productID 2)
??? bizzare ?

_______________ _______________ ________

This is the code I currentl have for the click event of the button on
my Products mainform:

-------------------------------------------

Dim strSql As String
Dim db As DAO.Database
Dim NewProdID As Long

Set db = DBEngine(0)(0)

If Me.Dirty Then
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
With Me.RecordsetClo ne
.AddNew
!ProdCode = "TEST PRODUCT 2"
!Description = Me.Description
!TestPackCost = Me.TestPackCost
!ProductIdentif y = Me.ProductIdent ify
!SDProdCode = Me.SDProdCode
!Availability = Me.Combo107.Col umn(0)
!PSLmarker = Me.Combo133.Col umn(0)
!BBProducts = Me.BBProducts
!BBProductsUSA = Me.BBProductsUS A
.Update


.Bookmark = .LastModified
NewProdID = !ProductID
'MsgBox !ProductID

Me.Requery
End With


'With Me.SubBuildLine .Form.Recordset Clone

Set rst = Forms![Product
Details]!SubBuildLine.F orm.RecordsetCl one
rst.MoveFirst
Do While Not rst.EOF

.AddNew
!SubID = [SubBuildLine].Form![SubID]
!ProductID = NewProdID
!Quantity = [SubBuildLine].Form![Quantity]

rst.MoveNext
Loop

End With
End If

_______________ _____________

Appreciate your help. I just hate it when I know i'm so close, thanks
to your advice, but just cannot crack the last turn :-(

Thanks

David

[Allen Browne]

Not sure why you are getting ProductID 2 if that is not the record you are
duplicating.

An option (assuming these are Access tables) is to read the new AutoNumber
before the Update, i.e.:
!BBProducts = Me.BBProducts
!BBProductsUSA = Me.BBProductsUS A
NewProductID = !ProductID
.Update

Lose the Requery: that will take you back to the first record in the form.

Although it would be possible to AddNew to the subforms' RecordsetClone as
well, that would be messy: you would need to loop through each of the
records in the subform to generate a copy of each. The Append query
statement would be much simpler.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<da*********@sc ene-double.co.uk> wrote in message
news:11******** *************@f 14g2000cwb.goog legroups.com...

In the 2 tables in question I have the following:

Table: Products
PK: ProductID (AutoNumber)
Other Product fields....

Table: SubBuildLine
PK: SubBuildLineID (AutoNumber)

Fields required to copy over:
SubID
ProductID (The new one)
Quantity

_______________ _______________

I just use something like 'NewProductID' to hold the new ProductID,
i.e. NewProductID = !ProductID.

If I print out the variable or !ProductID, I get '2' ?? (productID 2)
??? bizzare ?

_______________ _______________ ________

This is the code I currentl have for the click event of the button on
my Products mainform:

-------------------------------------------

Dim strSql As String
Dim db As DAO.Database
Dim NewProdID As Long

Set db = DBEngine(0)(0)

If Me.Dirty Then
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
With Me.RecordsetClo ne
.AddNew
!ProdCode = "TEST PRODUCT 2"
!Description = Me.Description
!TestPackCost = Me.TestPackCost
!ProductIdentif y = Me.ProductIdent ify
!SDProdCode = Me.SDProdCode
!Availability = Me.Combo107.Col umn(0)
!PSLmarker = Me.Combo133.Col umn(0)
!BBProducts = Me.BBProducts
!BBProductsUSA = Me.BBProductsUS A
.Update


.Bookmark = .LastModified
NewProdID = !ProductID
'MsgBox !ProductID

Me.Requery
End With


'With Me.SubBuildLine .Form.Recordset Clone

Set rst = Forms![Product
Details]!SubBuildLine.F orm.RecordsetCl one
rst.MoveFirst
Do While Not rst.EOF

.AddNew
!SubID = [SubBuildLine].Form![SubID]
!ProductID = NewProdID
!Quantity = [SubBuildLine].Form![Quantity]

rst.MoveNext
Loop

End With
End If

_______________ _____________

Appreciate your help. I just hate it when I know i'm so close, thanks
to your advice, but just cannot crack the last turn :-(

Thanks

David