473,701 Members | 2,361 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Multiple Record Copy Transaction...

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

Nov 13 '05 #1
19 3474
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

Nov 13 '05 #2
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


Nov 13 '05 #3
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

Nov 13 '05 #4
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 :-)

Nov 13 '05 #5
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 :-)

Nov 13 '05 #6

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 :-)


Nov 13 '05 #7
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 :-)

Nov 13 '05 #8
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

Nov 13 '05 #9
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

Nov 13 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

16
7511
by: noah | last post by:
Does PHP have a feature to associate Cookie sessions with a persistent database connection that will allow a single transaction across multiple HTTP requests? Here is how I imagine my process: I have an series of interactive HTML forms. The user begins a Cookie session. A database connection is opened and a transaction is begun. After the user goes through any number of pages where they update the database they finish on a page where...
11
9012
by: Alban Hertroys | last post by:
Oh no! It's me and transactions again :) I'm not really sure whether this is a limitation of psycopg or postgresql. When I use multiple cursors in a transaction, the records inserted at the start of the transaction aren't visible to those later on in that transaction (using a different cursor). Attached is a simplified example (the except's are a bit blunt, I know) of what I'm trying to do. In reality, the different cursors are...
11
5310
by: Mike | last post by:
Looking to find any information on how to properly configure multiple instances of DB2. This is on Win2k db2 ver 7.2. I am basically looking for information on how the multiple instance settings should configured to work, how memory is shared or not, etc. I can not seem to find any good links to this information. Thanks, Mike
3
2410
by: PAUL | last post by:
Hello, I have 2 datasets I am trying to update. The parent table seems to update fine but when I go update the chiled table I get an error message that says I need a related record in the parent table. However I put some code in to display the key field of each parent table record (parent dataset) and the value I am trying to put into the child table is there. ParentTable ChildTable ID------------------------<...
10
1945
by: Marc R. | last post by:
Hi all, I edit records using a form that have multiple control bind on Dataview, But I don't want to update right always to database, I would like to delay until all Changes (add all new record using a form or edit various existing records) This way I could minimize transaction on server..
2
1708
by: alee | last post by:
I am writing a stored procedure to handle Order and OrderDetail tables. I created a tempTable physically on the database, and insert the OrderDetails into the tempTable first, then I try to use the tempTable in a select statement to insert the all the records to the OrderDetails. If there is any record failed, rollback all the transaction. But when I run the program, it generates error message saying that the column name of number of...
9
2652
by: master | last post by:
Actually, it is not only the record locking, what I need, and nobody seems to descibe this. Imagine the following scenario. There is a database with, say 10000 records with some unvalidated data. And there is an Intranet ASP.NET application which is an interface to the database in question... and there are 100 pretty girls eager to... uhmm... use the application and validate the data of course ;-). The task is to enable the data...
4
2393
by: corey11 | last post by:
I'm a very low-level developer with limited VB knowledge but nonetheless was able to put together a very user-friendly and extremely helpful Access 97 database for my company some 10 years back. We use the database for inventory, job costing, employee records, timecard recording, nearly everything except the hard core accounting. The database is shared by 20 users over a network. Every 3 or 4 years I archive and purge the data to keep the...
9
8217
by: weirdguy | last post by:
Hello all, I almost complete my mini project - Stock Inventory: To track goods/products enter and exit from warehouse or simply known as Stock Transaction (IN/OUT). This also include a Inventory Catalog which basically show all the products in the warehouse, each product details including category, description and most importantly quantity on hand. As mentioned above, I almost complete the database except the most important Form - New...
0
8649
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9221
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8975
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8933
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6567
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5904
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4410
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3102
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2398
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.