Copy, Paste and Append

[Josue Barbosa]

I have a Microsoft Access 2010 database, which has all of the my clients information and transports. I want to create a button called return trip on my main form which does the following.

What I want the button to do is to look at the main form which has all of the transport information, with the subform having all of the clients information and then create a new record based on the current one and keep the same client attached to the new record while at the same time on the new record for the main form filling in the pickup and drop off locations for the transport based on the original record. So it would take all of the pickup information swap it for the drop off information, and the drop off information would now be the pickup information, below are the fields that I have for both pickup and drop off.

Pickup Name
Pickup Address
Pickup City
Pickup County
Pickup State
Pickup Zip Code
Pickup Phone Number

Thank you for any and all the help you can provide on this. Its been driving me nuts for over a month now. I have to admit that obviously I am still learning a lot when it come to microsoft access so any help you can provide will be immensely appreciated. By the way I am using Microsoft Access 2010 Pro if that helps. I know that there were many changes between the 2000 version to now.

THANKS AGAIN!

[ADezii]

Swaps are typically performed via the use of an Intermediate Variable (strTemp in this case), such as in:

Dim strValue1 As String
Dim strValue2 As String
Dim strTemp As String

strValue1 = "Philadelphia"
strValue2 = "New York City"

Debug.Print "Before Swap:"
Debug.Print "|-- strvalue1: " & strValue1
Debug.Print "|-- strvalue2: " & strValue2

'*** Start the Swap Process here: ***
strTemp = strValue2
strValue2 = strValue1
strValue1 = strTemp
'************************************

Debug.Print

Debug.Print "After Swap:"
Debug.Print "|-- strvalue1: " & strValue1
Debug.Print "|-- strvalue2: " & strValue2

[B]OUTPUT:[/B]
Before Swap:
|-- strvalue1: Philadelphia
|-- strvalue2: New York City

After Swap:
|-- strvalue1: New York City
|-- strvalue2: Philadelphia

[NeoPa]

I would recommend that you reconsider your design. A design such as you describe for storing this information is clumsy to say the least. The data should be stored once only and not twice. Database Normalisation and Table structures may help you understand some of the issues involved with database design.