A loop or Import Records are both options here for moving data from one table to another, but your duplication of records task does indeed sound like something that may be unecessary.
What kind of information do these records represent?
Why do they need to be copied to another table?
The basic reason they need to be copied is becuase the table they are coming from is a legal document and cannot be changed. They form the are going to is something that is used and altered on a regular basis. The table the records are in will have signatures and other information associated with it so they will never be allowed to be alter once entered after the signute is added.
Unfortunately as much as I do not like it I have to follow certain guidelines and rules within the information to protect authenticity which means I must create seperate copies and records is a second place within the database to preserve the originals. When building the full record for the "patient" information must be able to be transferred and altered as time goes on. So that is why i am looking for something to copy it. I only want to do it one patient at a time so would an import or would that copy the whole table. I only want certain records to copy at certain times.
I'm not clear from your description whether this "to be copied" information is the info in the form that can't be changed or if it also can be edited once it is copied into the other table.
Such a transfer, if it needs to be edited after transfer is a perfectly acceptable set up for a relational database. If this text cannot be changed on the form to which it is being transferred, then you may be able to simply display this data on the form, but with it locked down to disallow editing in the fields that display this data.
Assuming that you really do need to copy the data, here is an outline of two general approaches you can use:
1) Perform a find in the source table to pull up the records to be copied into a found set. Under some circumstances, Go To Related records can be used to produce this found set.
Then use Import records to import this data from the source table into the target table. Unlike doing this by hand, you can set up this step without first selecting the layout of the target table, though you will likely want to do this anyway in order to display the results of this import.
2) Use a looping script to move the data one record at a time. You can define a relationship between a foreign key field in the target table and the primary key field in the source table, then define looked up value settings to copy the data from the matching fields in the source table into the target table. Your script finds all the records to be copied, then loops through them. Each loop of the script does this:
Use set variable to copy the primary key of the current record in the source table to the variable
Switches to a layout for the target table, creates a new record and uses set field to copy the value from the variable to the foreign key field in newly created record. Looked up value settings on fields in the target table that have corresponding fields in the source table then auto-enter the matching data for that record.
Then the script returns to the source table layout and uses go to Record/request/page [next ; exit after last] to go to the next record and exit the loop once it has done so with the last record.
Thank you I went with your second suggestion. After some tweaking of the script it works perfectly. I wasn't able to use the set field, I used a variable for each field as it changed. It was a bit cumbersome but it worked for now. I will most likely go back and alter at a later dater but thanks for the help.