Copying a record from one table to another table

[Mechele]

I have a form where the user will select a name of company from a combo box and then click a command button. I would like the click event on the command button to copy the record from one table to another table based on the name of company the user selects. Next, have code to change the client id of the record in the initial table.

(Basically I would like to copy a record from one table to another table and change the client id in the first table. But, I have several tables I need to copy one record from one table and "archive it" to another table. Next, have the clientid of the origin table changed.)

Any suggestions on how to do that?

I've started with the following code:

Dim StrSQL as String
Dim rs as recordset

StrSQL "SELECT * From GeneralInformationTable WHERE GeneralInformationTable.ClientID = me.ClientID;"

Set rs = CurrentDB.OpenRecordset("GeneralInformationTableArchive")

With rs
.AddNew
.Fields("ClientID") = StrSQL.ClientID
.Update
.Close
End With

me.ClientID = me.ClientID + 1

The above code is something I've started. There are more fields to the GeneralInformationTable, but I just listed one to show as an example of what I've started. I have 17 tables I need to archive the records by company name during renewal of that company's contract. So, basically I'm not trying to copy all the records from one table to another all at once. Just each company at different times depending on their renewal dates, if they renew their contracts.

 

[mary.h]

Hi,

you could try the following:

Dim StrSQL as String

StrSQL = "INSERT INTO GeneralInformationTableArchive " & _
"SELECT * FROM GeneralInformationTable " & _
"WHERE (((GeneralInformationTable.[ClientID])=" & Me.ClientID & "));"

DoCmd.RunSQL StrSQL

Me.ClientID = Me.ClientID + 1

Mary.h

 

[Mechele]

You Guys Are The Greatest!!!!