Duplicate the record in form and subform

  • Thread starter Thread starter Dorci
  • Start date Start date
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...
 
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...
 
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...
 
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...
 
Back
Top