Duplicate the record in form and subform

D

Dorci

I've actually rendered my entire database inoperable. Every command (even
Exit) gives an error message. I had to restore the backup, but now I'm back
at square one. I'm going to set this aside for now and try to give it a
fresh look in a couple of days. (Honestly, I've gotten a bit overwhelmed.)
Thank you so much for your help; I couldn't have gotten as far as I did
without it.

David H said:
Did you try the code that I posted in my last post? I modified the original
routine.

Dorci said:
Sorry, here's the code:

Dim varOrigID, varNewID As Long

' Capture original MTNumber
varOrigID = Me.MTNumber

' To add the records to the main table, add all fields except of the key,
' it will be assign automatically.
DoCmd.RunSQL "INSERT INTO [Material Transfer]( [CustomerID], [CompanyName],
[Address ], " & _
"[City], [PostalCode], [StateOrProvince], [Country], [ContactName], " & _
"[TransportationCharge], [PayMethod], [NumOfCtns], [Carrier],
[ProjectNumber], " & _
"[SalesOrder], [SJNumber], [ShippingPlant], [Reference], [ReceivedBy],
[Category], " & _
"[AuthorizedBy], [Address2], [DateEntered] ) " & _
"SELECT [Material Transfer].CustomerID, [Material Transfer].CompanyName,
" & _
"[Material Transfer].Address , [Material Transfer].City, [Material
Transfer].PostalCode, " & _
"[Material Transfer].StateOrProvince, [Material Transfer].Country, " & _
"[Material Transfer].ContactName, [Material
Transfer].TransportationCharge, " & _
"[Material Transfer].PayMethod, [Material Transfer].NumOfCtns, [Material
Transfer].Carrier, " & _
"[Material Transfer].ProjectNumber, [Material Transfer].SalesOrder, " & _
"[Material Transfer].SJNumber, [Material Transfer].ShippingPlant,
[Material Transfer].Reference, " & _
"[Material Transfer].ReceivedBy, [Material Transfer].Category, [Material
Transfer].AuthorizedBy, " & _
"[Material Transfer].Address2, Date " & _
"FROM [Material Transfer]" & _
"WHERE [MTNumber] = " & varOrigID

' Look for the new key
'varNewID = 'HEEEEEEEEEEEEELP here!

' Insert the new records in the second table based on the old key,
' but with the new key above.
DoCmd.RunSQL "INSERT INTO [MT Items] ([MTNumber], [Quantity], [Description],
[UnitCost], [Amount]) " & _
"SELECT " & varNewID & ", [MT Items].[Quantity], [MT Items].[Description],
[MT Items].[UnitCost], [MT Items].[Amount]" & _
"FROM [MT Items] WHERE [MTNumber] = " & varOrigID

' Refresh the form, so the new record will be added.
Me.Requery

' Look for the new record
With Me.RecordsetClone
.FindFirst "[Mtnumber] = " & varNewID
If .NoMatch Then
MsgBox "Record not found!", vbExclamation
Else
Me.Bookmark = .Bookmark
End If
End With


Dorci said:
Dave,

I am at a total loss so I'm taking a new route. I can almost get the
following code to work, with the exception of identifying the new key value
to assign to the child records (see below). I am stumpted at the comment
"Look for the new key". Can you help?
Click to expand...
Click to expand...
Click to expand...
 
D

David H

Sorry about that. When you are ready to try again, go back two or three of my
posts. I took your code and modified it. Or if you'd like if you can strip
down the database to just the key tables (no data) and the form/subform I can
play with it. I have time over the next couple of days, however on Monday I
fly out for 2 weeks and will be quite busy at that point.

Dorci said:
I've actually rendered my entire database inoperable. Every command (even
Exit) gives an error message. I had to restore the backup, but now I'm back
at square one. I'm going to set this aside for now and try to give it a
fresh look in a couple of days. (Honestly, I've gotten a bit overwhelmed.)
Thank you so much for your help; I couldn't have gotten as far as I did
without it.

David H said:
Did you try the code that I posted in my last post? I modified the original
routine.

Dorci said:
Sorry, here's the code:

Dim varOrigID, varNewID As Long

' Capture original MTNumber
varOrigID = Me.MTNumber

' To add the records to the main table, add all fields except of the key,
' it will be assign automatically.
DoCmd.RunSQL "INSERT INTO [Material Transfer]( [CustomerID], [CompanyName],
[Address ], " & _
"[City], [PostalCode], [StateOrProvince], [Country], [ContactName], " & _
"[TransportationCharge], [PayMethod], [NumOfCtns], [Carrier],
[ProjectNumber], " & _
"[SalesOrder], [SJNumber], [ShippingPlant], [Reference], [ReceivedBy],
[Category], " & _
"[AuthorizedBy], [Address2], [DateEntered] ) " & _
"SELECT [Material Transfer].CustomerID, [Material Transfer].CompanyName,
" & _
"[Material Transfer].Address , [Material Transfer].City, [Material
Transfer].PostalCode, " & _
"[Material Transfer].StateOrProvince, [Material Transfer].Country, " & _
"[Material Transfer].ContactName, [Material
Transfer].TransportationCharge, " & _
"[Material Transfer].PayMethod, [Material Transfer].NumOfCtns, [Material
Transfer].Carrier, " & _
"[Material Transfer].ProjectNumber, [Material Transfer].SalesOrder, " & _
"[Material Transfer].SJNumber, [Material Transfer].ShippingPlant,
[Material Transfer].Reference, " & _
"[Material Transfer].ReceivedBy, [Material Transfer].Category, [Material
Transfer].AuthorizedBy, " & _
"[Material Transfer].Address2, Date " & _
"FROM [Material Transfer]" & _
"WHERE [MTNumber] = " & varOrigID

' Look for the new key
'varNewID = 'HEEEEEEEEEEEEELP here!

' Insert the new records in the second table based on the old key,
' but with the new key above.
DoCmd.RunSQL "INSERT INTO [MT Items] ([MTNumber], [Quantity], [Description],
[UnitCost], [Amount]) " & _
"SELECT " & varNewID & ", [MT Items].[Quantity], [MT Items].[Description],
[MT Items].[UnitCost], [MT Items].[Amount]" & _
"FROM [MT Items] WHERE [MTNumber] = " & varOrigID

' Refresh the form, so the new record will be added.
Me.Requery

' Look for the new record
With Me.RecordsetClone
.FindFirst "[Mtnumber] = " & varNewID
If .NoMatch Then
MsgBox "Record not found!", vbExclamation
Else
Me.Bookmark = .Bookmark
End If
End With


:

Dave,

I am at a total loss so I'm taking a new route. I can almost get the
following code to work, with the exception of identifying the new key value
to assign to the child records (see below). I am stumpted at the comment
"Look for the new key". Can you help?
Click to expand...
Click to expand...
Click to expand...
 
D

Dorci

David, thanks for the offer. Good news! I cleared my head, said a prayer,
tried again, and it worked!!! I searched the discussion group for Allen
Browne's original code and found that several others had had success with it.
Starting with a clean slate, I carefully entered the code, resolved a few
very minor hiccups, and voila!

Thanks for all your help. Have a safe trip.

David H said:
Sorry about that. When you are ready to try again, go back two or three of my
posts. I took your code and modified it. Or if you'd like if you can strip
down the database to just the key tables (no data) and the form/subform I can
play with it. I have time over the next couple of days, however on Monday I
fly out for 2 weeks and will be quite busy at that point.

Dorci said:
I've actually rendered my entire database inoperable. Every command (even
Exit) gives an error message. I had to restore the backup, but now I'm back
at square one. I'm going to set this aside for now and try to give it a
fresh look in a couple of days. (Honestly, I've gotten a bit overwhelmed.)
Thank you so much for your help; I couldn't have gotten as far as I did
without it.

David H said:
Did you try the code that I posted in my last post? I modified the original
routine.

:

Sorry, here's the code:

Dim varOrigID, varNewID As Long

' Capture original MTNumber
varOrigID = Me.MTNumber

' To add the records to the main table, add all fields except of the key,
' it will be assign automatically.
DoCmd.RunSQL "INSERT INTO [Material Transfer]( [CustomerID], [CompanyName],
[Address ], " & _
"[City], [PostalCode], [StateOrProvince], [Country], [ContactName], " & _
"[TransportationCharge], [PayMethod], [NumOfCtns], [Carrier],
[ProjectNumber], " & _
"[SalesOrder], [SJNumber], [ShippingPlant], [Reference], [ReceivedBy],
[Category], " & _
"[AuthorizedBy], [Address2], [DateEntered] ) " & _
"SELECT [Material Transfer].CustomerID, [Material Transfer].CompanyName,
" & _
"[Material Transfer].Address , [Material Transfer].City, [Material
Transfer].PostalCode, " & _
"[Material Transfer].StateOrProvince, [Material Transfer].Country, " & _
"[Material Transfer].ContactName, [Material
Transfer].TransportationCharge, " & _
"[Material Transfer].PayMethod, [Material Transfer].NumOfCtns, [Material
Transfer].Carrier, " & _
"[Material Transfer].ProjectNumber, [Material Transfer].SalesOrder, " & _
"[Material Transfer].SJNumber, [Material Transfer].ShippingPlant,
[Material Transfer].Reference, " & _
"[Material Transfer].ReceivedBy, [Material Transfer].Category, [Material
Transfer].AuthorizedBy, " & _
"[Material Transfer].Address2, Date " & _
"FROM [Material Transfer]" & _
"WHERE [MTNumber] = " & varOrigID

' Look for the new key
'varNewID = 'HEEEEEEEEEEEEELP here!

' Insert the new records in the second table based on the old key,
' but with the new key above.
DoCmd.RunSQL "INSERT INTO [MT Items] ([MTNumber], [Quantity], [Description],
[UnitCost], [Amount]) " & _
"SELECT " & varNewID & ", [MT Items].[Quantity], [MT Items].[Description],
[MT Items].[UnitCost], [MT Items].[Amount]" & _
"FROM [MT Items] WHERE [MTNumber] = " & varOrigID

' Refresh the form, so the new record will be added.
Me.Requery

' Look for the new record
With Me.RecordsetClone
.FindFirst "[Mtnumber] = " & varNewID
If .NoMatch Then
MsgBox "Record not found!", vbExclamation
Else
Me.Bookmark = .Bookmark
End If
End With


:

Dave,

I am at a total loss so I'm taking a new route. I can almost get the
following code to work, with the exception of identifying the new key value
to assign to the child records (see below). I am stumpted at the comment
"Look for the new key". Can you help?
Click to expand...
Click to expand...
Click to expand...
 
D

David H

Don't be losing any freight. Especially if its time-sensitive, extensive
studies have shown that the loss of time sensitive freight has a way of
making everyone involved grumpy.

Dorci said:
David, thanks for the offer. Good news! I cleared my head, said a prayer,
tried again, and it worked!!! I searched the discussion group for Allen
Browne's original code and found that several others had had success with it.
Starting with a clean slate, I carefully entered the code, resolved a few
very minor hiccups, and voila!

Thanks for all your help. Have a safe trip.

David H said:
Sorry about that. When you are ready to try again, go back two or three of my
posts. I took your code and modified it. Or if you'd like if you can strip
down the database to just the key tables (no data) and the form/subform I can
play with it. I have time over the next couple of days, however on Monday I
fly out for 2 weeks and will be quite busy at that point.

Dorci said:
I've actually rendered my entire database inoperable. Every command (even
Exit) gives an error message. I had to restore the backup, but now I'm back
at square one. I'm going to set this aside for now and try to give it a
fresh look in a couple of days. (Honestly, I've gotten a bit overwhelmed.)
Thank you so much for your help; I couldn't have gotten as far as I did
without it.

:

Did you try the code that I posted in my last post? I modified the original
routine.

:

Sorry, here's the code:

Dim varOrigID, varNewID As Long

' Capture original MTNumber
varOrigID = Me.MTNumber

' To add the records to the main table, add all fields except of the key,
' it will be assign automatically.
DoCmd.RunSQL "INSERT INTO [Material Transfer]( [CustomerID], [CompanyName],
[Address ], " & _
"[City], [PostalCode], [StateOrProvince], [Country], [ContactName], " & _
"[TransportationCharge], [PayMethod], [NumOfCtns], [Carrier],
[ProjectNumber], " & _
"[SalesOrder], [SJNumber], [ShippingPlant], [Reference], [ReceivedBy],
[Category], " & _
"[AuthorizedBy], [Address2], [DateEntered] ) " & _
"SELECT [Material Transfer].CustomerID, [Material Transfer].CompanyName,
" & _
"[Material Transfer].Address , [Material Transfer].City, [Material
Transfer].PostalCode, " & _
"[Material Transfer].StateOrProvince, [Material Transfer].Country, " & _
"[Material Transfer].ContactName, [Material
Transfer].TransportationCharge, " & _
"[Material Transfer].PayMethod, [Material Transfer].NumOfCtns, [Material
Transfer].Carrier, " & _
"[Material Transfer].ProjectNumber, [Material Transfer].SalesOrder, " & _
"[Material Transfer].SJNumber, [Material Transfer].ShippingPlant,
[Material Transfer].Reference, " & _
"[Material Transfer].ReceivedBy, [Material Transfer].Category, [Material
Transfer].AuthorizedBy, " & _
"[Material Transfer].Address2, Date " & _
"FROM [Material Transfer]" & _
"WHERE [MTNumber] = " & varOrigID

' Look for the new key
'varNewID = 'HEEEEEEEEEEEEELP here!

' Insert the new records in the second table based on the old key,
' but with the new key above.
DoCmd.RunSQL "INSERT INTO [MT Items] ([MTNumber], [Quantity], [Description],
[UnitCost], [Amount]) " & _
"SELECT " & varNewID & ", [MT Items].[Quantity], [MT Items].[Description],
[MT Items].[UnitCost], [MT Items].[Amount]" & _
"FROM [MT Items] WHERE [MTNumber] = " & varOrigID

' Refresh the form, so the new record will be added.
Me.Requery

' Look for the new record
With Me.RecordsetClone
.FindFirst "[Mtnumber] = " & varNewID
If .NoMatch Then
MsgBox "Record not found!", vbExclamation
Else
Me.Bookmark = .Bookmark
End If
End With


:

Dave,

I am at a total loss so I'm taking a new route. I can almost get the
following code to work, with the exception of identifying the new key value
to assign to the child records (see below). I am stumpted at the comment
"Look for the new key". Can you help?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copy Master and Sub record to an exisiting record with specified P 30
Copy old subform records to new Form's subform 1
Duplicate record in Form and Subform 0
how to append index key to the field in access 1
Duplicate values 4
importing from outlook problem 1
Duplicate Subform and SubSubForm Records but not Main 1
On Error Goto.... 1

Top