duplicating data

[RYKS]

hi,
i am working on a project in access which has several tables and all this tables have a common field called village name. there are about five forms which are fiileed sequentially to fill up the table. what i need now is to be able to fill up the table and then save the data under a new name from any of the forms. i tried using clone recordsets but it does not work. i cloned a recordset but but could not save it using a different name. can anyone help?

[Ecniv]

Can you elaborate on what you are trying to do?

[RYKS]

i want to duplicate a row containing a particular name in all the tables and use its data for new rows in the table. i need this to be done when a user clicks a save as button on any of the forms and then enters a new name. this is similar to a save as operation in other applications. hope am a bit clearer now. i have been trying to solve this all weekend

[Ecniv]

So you are holding four or more records of the same information?

I personally think your database design is wrong... however. You could do four (or more) append (insert into) queries from a table. If you use your imagination and use a sub, you can get it to call the sub from each form, and run the insert queries, passing the ID of the record from the one table to use as the record to be duplicated.

Or you could open a recordset object in code, for the source, open a destination recordset (the othe table) loop through all the fields in the source and write them to the destination, repeat for all destinations.

Or rethink the db layout so you hold the records once, and the other forms reference it for the main data. Any additional data can be stored in fields with an ID number (foreign key) pointing back to the main data.

Do any of them sound like something you want to try?

[RYKS]

ok will trying opening the rcordsets and loop through all the fields in the sources and write them to the destination. is just that i have so many fields b/t 5 to 10 on most tables so i thougth there is a shorter way.

But maybe you still dont get my problem. So let me try again using just one table , how can i copy data from one row eg with its name field = newyork,add a new record and insert the data from the newyork row and change the name from newyork to chicago. programatically.

so simply put i just wanna copy a row and paste it as a new record in the same table. is there no shorter way than actually writing to each field?

hope am clearer.

[Ecniv]

Um not that I know of. Probably a shortcoming in all dbs.

Yeah easiest is to to loop through a record.

You can use the recordset.fields value to count th efields and put that in the loop if that makes things easier.


dim rstSrc as adodb.recordset, rstDest as adodb.recordset
dim lngFlds as long, lngFld as long

rstsrc.open "sql statement",connection,3,3,1
rstdest.open "sql statement",connection,2,3,1

lngflds = rstsrc.fields.count
'---- assuming first col is primary key and autonumber
rstdest.addnew
for lngFld = 1 to lngflds-1
rstdest(lngfld) = rstsrc(lngfld)
next
rstdest.update

Something like the above. Perhaps.


If you are using a form, you could have a button that loops through all the controls and stores their values to the Tag property. then click the create new button to get a clean form and as long as the form hasn't closed and reopend you could have another button to set the controls on the form from the values held in the tag properties. Almost like a clipboard type operation. Then the user could overwrite some of the data and save as a new record.

[RYKS]

all rigth will try that. thanks.