I need to copy an entire record from one table to another which worked great using the Insert command until I added an Attachment type field. Is there a way to copy this record without doing it one field at a time (there are nearly 100 fields in the record)? If not, how would my loop look that would cylce through each field, one at a time, and copy it to my new table?
It's generally not a good idea to copy full records. It would be better to have an identifying number (usually an autonumber set as the primary key) on each record, and record that number in your other table (to be a foreign key). Then you can use queries in forms or reports to pull the information together.
But maybe your reason is an exception to this so what is your purpose behind wanting to do this?
It is part of a delete operation. There are two tables that contain critical financial information that the user will be able to delete. Instead of deleting, however, the records are copied into a deleted record table where management can then access to learn who is deleting records and why. I also need to create an archive procedure that will move entire records from one database to an archive database. I use a similar procedure for both (at least I used to before I started using attachement fields).
Hmmm, well, your reason seems like a good one, although I have read in other threads on here, that it might be a better idea to mark it somehow, rather then moving records around... Such as a yes/no field that is marked yes, and maybe called something like "archive".
If you want to do it this way, however, I would lookinto using an insert statement which pulls its values from a selected record or records on a form.... Then after the insert is run, then it could delete the record/s from the first table. You could do both in VBA tied behind the click event of a button on your form maybe.
Google how to write and sql insert statement to write the insert query, or alternatively you can build it in the QBE, then change to Sql view anre copy and paste into the VBA editor.
Maybe something like:
Code:
'Table Name will Be TNames for example purposes
Dim Field1 as String
Dim Field2 as string
Dim SQLInsert as string
Field1 = Me.FirstName
Field2 = Me.LastName
SQLInsert = "INSERT INTO TNames" & _
" VALUES (Filed1, Field2);)
Docmd.RunSQL "SQLInsert"
This code is air code, probably not perfect, but it gives you an idea. Like this it would only work for one record at a time.... I am not good at looping for multiple records, so someone else could nudge you in the right direction from here maybe.
Ohhh, this is something new to access? Version 2007 or 2010 maybe? I hate when I miss something important in the OP.... Please forgive me, as I have no idea.
I did find this in google search though, which I am sure you could use to better refine a google search of your own.
