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.RecordsetClone
.AddNew
!InvoiceDate = Date
!ClientID = Me.ClientID
'etc for other fields.
.Update
.Bookmark = .LastModified
lngInvID = !InvoiceID

'Duplicate the related records.
If Me.fInvoiceDetail.Form.RecordsetClone.RecordCount > 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*********@scene-double.co.uk> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.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....and 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.RecordsetClone
.AddNew
!InvoiceDate = Date
!ClientID = Me.ClientID
'etc for other fields.
.Update
.Bookmark = .LastModified
lngInvID = !InvoiceID

'Duplicate the related records.
If Me.fInvoiceDetail.Form.RecordsetClone.RecordCount > 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*********@scene-double.co.uk> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.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*********@scene-double.co.uk> wrote in message
news:11*********************@g49g2000cwa.googlegro ups.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....and 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.RecordsetClone
.AddNew
!InvoiceDate = Date
!ClientID = Me.ClientID
'etc for other fields.
.Update
.Bookmark = .LastModified
lngInvID = !InvoiceID

'Duplicate the related records.
If Me.fInvoiceDetail.Form.RecordsetClone.RecordCount > 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*********@scene-double.co.uk> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.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.fInvoiceDetail.Form.RecordsetClone.RecordCount > 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.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO SubBuildLine (ProductID,
Quantity,
SubID) " & _
"SELECT " & lngInvID & " as ProductID ???),
SubBuildLine.Quantity, " & _
"SubBuildLine.SubID FROM SubBuildLine " & _
"WHERE ( SubBuildLine.ProductID = " & 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.ProductID = " & 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*********@scene-double.co.uk> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

Thanks Allen,

Nearly there...

I cannot quite get the SQL correct:

You have:

If Me.fInvoiceDetail.Form.RecordsetClone.RecordCount > 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.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO SubBuildLine (ProductID,
Quantity,
SubID) " & _
"SELECT " & lngInvID & " as ProductID ???),
SubBuildLine.Quantity, " & _
"SubBuildLine.SubID FROM SubBuildLine " & _
"WHERE ( SubBuildLine.ProductID = " & 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.ProductID = " & 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*********@scene-double.co.uk> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

Thanks Allen,

Nearly there...

I cannot quite get the SQL correct:

You have:

If Me.fInvoiceDetail.Form.RecordsetClone.RecordCount > 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.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO SubBuildLine (ProductID,
Quantity,
SubID) " & _
"SELECT " & lngInvID & " as ProductID ???),
SubBuildLine.Quantity, " & _
"SubBuildLine.SubID FROM SubBuildLine " & _
"WHERE ( SubBuildLine.ProductID = " & 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*********@scene-double.co.uk> wrote in message
news:11********************@f14g2000cwb.googlegrou ps.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.ProductID = " & 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*********@scene-double.co.uk> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

> Thanks Allen,
>
> Nearly there...
>
> I cannot quite get the SQL correct:
>
> You have:
>
> If Me.fInvoiceDetail.Form.RecordsetClone.RecordCount > 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.RecordsetClone.RecordCount > 0 Then
> strSql = "INSERT INTO SubBuildLine (ProductID,
> Quantity,
> SubID) " & _
> "SELECT " & lngInvID & " as ProductID ???),
> SubBuildLine.Quantity, " & _
> "SubBuildLine.SubID FROM SubBuildLine " & _
> "WHERE ( SubBuildLine.ProductID = " & 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.RecordsetClone
.AddNew
!ProdCode = "TEST PRODUCT 2"
!Description = Me.Description
!TestPackCost = Me.TestPackCost
!ProductIdentify = Me.ProductIdentify
!SDProdCode = Me.SDProdCode
!Availability = Me.Combo107.Column(0)
!PSLmarker = Me.Combo133.Column(0)
!BBProducts = Me.BBProducts
!BBProductsUSA = Me.BBProductsUSA
.Update


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

Me.Requery
End With


'With Me.SubBuildLine.Form.RecordsetClone

Set rst = Forms![Product
Details]!SubBuildLine.Form.RecordsetClone
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.BBProductsUSA
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*********@scene-double.co.uk> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.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.RecordsetClone
.AddNew
!ProdCode = "TEST PRODUCT 2"
!Description = Me.Description
!TestPackCost = Me.TestPackCost
!ProductIdentify = Me.ProductIdentify
!SDProdCode = Me.SDProdCode
!Availability = Me.Combo107.Column(0)
!PSLmarker = Me.Combo133.Column(0)
!BBProducts = Me.BBProducts
!BBProductsUSA = Me.BBProductsUSA
.Update


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

Me.Requery
End With


'With Me.SubBuildLine.Form.RecordsetClone

Set rst = Forms![Product
Details]!SubBuildLine.Form.RecordsetClone
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

[davidgordon]

Allen,

Reading the new Autonumber before the .update throws up an error:
Invalid use of Null

I have commented out the sub-records code for now, I just want to
understand what is happening with the main product first.

Thanks

PS: If you feel I have taken too much of your time, please let me know
and i'll try to continue working it out :-(. Do you just answer posts
for a full time job ?, what else do you do ?

David

[Allen Browne]

Are these Access tables?
Or attached tables from SQL Server or other database?
Access assigns the autonum *before* the update; SQL Server does not.

--
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*********@scene-double.co.uk> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

Allen,

Reading the new Autonumber before the .update throws up an error:
Invalid use of Null

I have commented out the sub-records code for now, I just want to
understand what is happening with the main product first.

Thanks

PS: If you feel I have taken too much of your time, please let me know
and i'll try to continue working it out :-(. Do you just answer posts
for a full time job ?, what else do you do ?

David

[davidgordon]

Allen,

These tables are Import Linked from our MySQL DB on our Local Server.
I've just checked the field on the server. The Allow Null checkbox is
unchecked, and the Auto Increment checkbox is checked, and ProductID is
the PK. My MS Access front end works fine, and I also have asp web
applications running of the data.

When we view pages on the MS Access front-end, the data is really on
the MySQL DB, and when we load data into access, it auto updates MySQL.

Thanks

David.

[Allen Browne]

Ah, now it comes out. We are not dealing with Access tables here.

A dim memory suggests there was a problem with LastModified with other
databases, but I don't have the details to hand.

You might need a different approach, such as executing an Append query to
write the main record also, and then reading the @@IDENTITY or whatever
MySQL uses.

I'm not sure I can go any further with this one, as you're using an engine
I'm not familiar with, so I can't really debug it.

--
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*********@scene-double.co.uk> wrote in message
news:11*********************@g47g2000cwa.googlegro ups.com...

Allen,

These tables are Import Linked from our MySQL DB on our Local Server.
I've just checked the field on the server. The Allow Null checkbox is
unchecked, and the Auto Increment checkbox is checked, and ProductID is
the PK. My MS Access front end works fine, and I also have asp web
applications running of the data.

When we view pages on the MS Access front-end, the data is really on
the MySQL DB, and when we load data into access, it auto updates MySQL.

Thanks

David.

[davidgordon]

Fair enough, i'll try that now.
I really do appreciate all the time & effort you have spent on this :-)

Thanks again

David.

[davidgordon]

Hello Allen,

I've been working on this issue on & off.
I've managed to get the initial product to duplicate and refresh the
form to the new Product by using the following code with
DMax("ProductID", "Products")

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

With Me.RecordsetClone
..AddNew

!ProdCode = "TEST PRODUCT 2"
!Description = Me.Description
!TestPackCost = Me.TestPackCost
!ProductIdentify = Me.ProductIdentify
!SDProdCode = Me.SDProdCode
!Availability = Me.Combo107.Column(0)
!PSLmarker = Me.Combo133.Column(0)
!BBProducts = Me.BBProducts
!BBProductsUSA = Me.BBProductsUSA
..Update

NewProdID = DMax("ProductID", "Products")
Me.Text204.SetFocus
Me.Text204.text = NewProdID

'''''''' run append query (see below) ''''''

stDocName = "test210905"
DoCmd.OpenQuery stDocName, acNormal, acEdit

'''''''''then refresh form to new product and sub-records'''''''

Me.Requery
Me.RecordsetClone.FindFirst "ProductID = " & NewProdID
Me.Bookmark = Me.Recordsetlone.Bookmark
End With
-------

The append query is using the NewProdID held in the textbox 'Text204'on
the main form. I would rather load this id into a global/public
variable, but I cannot get that to work properly. I would would like to
then make this variable available to the query.

The append query 'test210905' I have is:

INSERT INTO subbuildline ( Quantity, ProductID, SubID )
SELECT subbuildline.Quantity, subbuildline.ProductID,
subbuildline.SubID
FROM subbuildline
WHERE (((subbuildline.Quantity)=[Forms]![Product
Details]![SubBuildLine].[Form]![Quantity]) AND
((subbuildline.ProductID)=[Forms]![Product Details]![Text204]) AND
((subbuildline.SubID)=[Forms]![Product
Details]![SubBuildLine].[Form]![SubID]));

___________________________
It still does not append any records to the table......why ?

Appreciate a closer look, thanks.
I do not think that my MySQL link is causing any problems as my system
works perfectly and has done for ages. As you can see, there is always
a workaround.

During my testing I tried loading the related records by use of a
recordsetclone for the sub-form and looping through the
sub-records....this worked, but it duplicated all sub-records for ALL
the products in my db....DOH !

I look forward to hearing from you
David


__________________________________________________ _

Allen Browne wrote:

Ah, now it comes out. We are not dealing with Access tables here.

A dim memory suggests there was a problem with LastModified with other

databases, but I don't have the details to hand.

You might need a different approach, such as executing an Append query to
write the main record also, and then reading the @@IDENTITY or whatever
MySQL uses.

I'm not sure I can go any further with this one, as you're using an engine
I'm not familiar with, so I can't really debug it.

--
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*********@scene-double.co.uk> wrote in message
news:11*********************@g47g2000cwa.googlegro ups.com...

Allen,

These tables are Import Linked from our MySQL DB on our Local Server.
I've just checked the field on the server. The Allow Null checkbox is
unchecked, and the Auto Increment checkbox is checked, and ProductID is
the PK. My MS Access front end works fine, and I also have asp web
applications running of the data.

When we view pages on the MS Access front-end, the data is really on
the MySQL DB, and when we load data into access, it auto updates MySQL.

Thanks

David.

[Allen Browne]

Not sure I can help you with the nuances of what's going on, David. I'm not
sure of the behavior or timing of how MySQL assigns whatever it calls the
equivalent of the AutoNumber.

If you are the only process/user assigning values, and the AutoNumber is
sequential (not random), you might be able to read it with a DMax() directly
on the table, immediately after adding the new record.

It should not be too difficult to duplicate just the records matching the
record in the main form: you already have it's primary key value visible, so
you can use it in the WHERE clause of your query to duplicate just its
related records.

--
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*********@scene-double.co.uk> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Hello Allen,

I've been working on this issue on & off.
I've managed to get the initial product to duplicate and refresh the
form to the new Product by using the following code with
DMax("ProductID", "Products")

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

With Me.RecordsetClone
.AddNew

!ProdCode = "TEST PRODUCT 2"
!Description = Me.Description
!TestPackCost = Me.TestPackCost
!ProductIdentify = Me.ProductIdentify
!SDProdCode = Me.SDProdCode
!Availability = Me.Combo107.Column(0)
!PSLmarker = Me.Combo133.Column(0)
!BBProducts = Me.BBProducts
!BBProductsUSA = Me.BBProductsUSA
.Update

NewProdID = DMax("ProductID", "Products")
Me.Text204.SetFocus
Me.Text204.text = NewProdID

'''''''' run append query (see below) ''''''

stDocName = "test210905"
DoCmd.OpenQuery stDocName, acNormal, acEdit

'''''''''then refresh form to new product and sub-records'''''''

Me.Requery
Me.RecordsetClone.FindFirst "ProductID = " & NewProdID
Me.Bookmark = Me.Recordsetlone.Bookmark
End With
-------

The append query is using the NewProdID held in the textbox 'Text204'on
the main form. I would rather load this id into a global/public
variable, but I cannot get that to work properly. I would would like to
then make this variable available to the query.

The append query 'test210905' I have is:

INSERT INTO subbuildline ( Quantity, ProductID, SubID )
SELECT subbuildline.Quantity, subbuildline.ProductID,
subbuildline.SubID
FROM subbuildline
WHERE (((subbuildline.Quantity)=[Forms]![Product
Details]![SubBuildLine].[Form]![Quantity]) AND
((subbuildline.ProductID)=[Forms]![Product Details]![Text204]) AND
((subbuildline.SubID)=[Forms]![Product
Details]![SubBuildLine].[Form]![SubID]));

___________________________
It still does not append any records to the table......why ?

Appreciate a closer look, thanks.
I do not think that my MySQL link is causing any problems as my system
works perfectly and has done for ages. As you can see, there is always
a workaround.

During my testing I tried loading the related records by use of a
recordsetclone for the sub-form and looping through the
sub-records....this worked, but it duplicated all sub-records for ALL
the products in my db....DOH !

I look forward to hearing from you
David

[davidgordon]

Allen,

I think I've just worked out what is going wrong..

Duplicating the initial Product works fine.
The append query I have works fine also, except..

The data needs to be appended to the same table ?

How can I write the query to select all the records where ProductID =
ProdutID of the product to copy, and then replace that ID with the new
one ?.

My current append query:

INSERT INTO subbuildline ( Quantity, SubID, ProductID )
SELECT subbuildline.Quantity, subbuildline.SubID,
subbuildline.ProductID
FROM subbuildline
WHERE (((subbuildline.Quantity)=[Forms]![Product
Details]![SubBuildLine].[Form]![Quantity]) AND
((subbuildline.SubID)=[Forms]![Product
Details]![SubBuildLine].[Form]![SubID]) AND
((subbuildline.ProductID)=[Forms]![Product Details]![Text204]));

Requests the new productID (written to Text204), which obviously has no
sub-records to copy yet, hence it works but nothing is added. If I set
the criteria to the current productID to copy, it copies the records as
required.

How can an append query do both at the same time, to the same field in
the same table ?

I see in your initial code you have an extra bit in your select
statement:

....SELECT " & lngInvID & " As NewInvoiceID... (where lngInvID = new
ID).
I only have 1 field in my table called ProductID. How can I select that
field as the newID, whilst also selecting it as the original to search
on ?

Please can you review my query sql above to show me what I am missing ?

Appreciate you help. Once I understand this, it should be full throttle
all the way...

Thanks again

David Gordon
__________________________________
Allen Browne wrote:

Not sure I can help you with the nuances of what's going on, David. I'm not
sure of the behavior or timing of how MySQL assigns whatever it calls the
equivalent of the AutoNumber.

If you are the only process/user assigning values, and the AutoNumber is
sequential (not random), you might be able to read it with a DMax() directly
on the table, immediately after adding the new record.

It should not be too difficult to duplicate just the records matching the
record in the main form: you already have it's primary key value visible, so
you can use it in the WHERE clause of your query to duplicate just its
related records.

--
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*********@scene-double.co.uk> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Hello Allen,

I've been working on this issue on & off.
I've managed to get the initial product to duplicate and refresh the
form to the new Product by using the following code with
DMax("ProductID", "Products")

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

With Me.RecordsetClone
.AddNew

!ProdCode = "TEST PRODUCT 2"
!Description = Me.Description
!TestPackCost = Me.TestPackCost
!ProductIdentify = Me.ProductIdentify
!SDProdCode = Me.SDProdCode
!Availability = Me.Combo107.Column(0)
!PSLmarker = Me.Combo133.Column(0)
!BBProducts = Me.BBProducts
!BBProductsUSA = Me.BBProductsUSA
.Update

NewProdID = DMax("ProductID", "Products")
Me.Text204.SetFocus
Me.Text204.text = NewProdID

'''''''' run append query (see below) ''''''

stDocName = "test210905"
DoCmd.OpenQuery stDocName, acNormal, acEdit

'''''''''then refresh form to new product and sub-records'''''''

Me.Requery
Me.RecordsetClone.FindFirst "ProductID = " & NewProdID
Me.Bookmark = Me.Recordsetlone.Bookmark
End With
-------

The append query is using the NewProdID held in the textbox 'Text204'on
the main form. I would rather load this id into a global/public
variable, but I cannot get that to work properly. I would would like to
then make this variable available to the query.

The append query 'test210905' I have is:

INSERT INTO subbuildline ( Quantity, ProductID, SubID )
SELECT subbuildline.Quantity, subbuildline.ProductID,
subbuildline.SubID
FROM subbuildline
WHERE (((subbuildline.Quantity)=[Forms]![Product
Details]![SubBuildLine].[Form]![Quantity]) AND
((subbuildline.ProductID)=[Forms]![Product Details]![Text204]) AND
((subbuildline.SubID)=[Forms]![Product
Details]![SubBuildLine].[Form]![SubID]));

___________________________
It still does not append any records to the table......why ?

Appreciate a closer look, thanks.
I do not think that my MySQL link is causing any problems as my system
works perfectly and has done for ages. As you can see, there is always
a workaround.

During my testing I tried loading the related records by use of a
recordsetclone for the sub-form and looping through the
sub-records....this worked, but it duplicated all sub-records for ALL
the products in my db....DOH !

I look forward to hearing from you
David

[davidgordon]

Ahah !!!.....I've worked it out.....Yipeeeee !

My working SQL is:

MySql = "INSERT INTO subbuildline (ProductID, Quantity, SubID) "
MySql = MySql & "SELECT " & testid & " as NewProductID,
subbuildline.Quantity, subbuildline.SubID FROM subbuildline "
MySql = MySql & "WHERE subbuildline.ProductID = " & currentid
DoCmd.RunSQL MySql

Nobody told me you have to give the ProductID field a different name to
trick SQL and have the insert fields in the same orer as the selct
fields. Clever stuff !!
Now I can get on with my new programmes !!! yipee !!!....i'm a clever
old sod....it took me about a week of messing around to suddenly
understand how it works.

Thanks Allen for your initial code. I worked it out from someone else's
post using this code but with:
SELECT " & testid & " as Expr1.....and I thought, hold on.....I
understand this.

Thanky you, thank you, thank you

[Allen Browne]

<da*********@scene-double.co.uk> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

Ahah !!!.....I've worked it out.....Yipeeeee !

My working SQL is ...

Thanky you, thank you, thank you

Excellent!

Great news.

--
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.