I have 2 tables on a SQL Server. I have created an access project to connect to the tables. The first table lets say is called OldValues, and the second NewValues.
I need to create a form wherby an operator can go record by record through the old table, copy the records from the old table into the new table and make changes to the new table. The 2nd table does not have the same field names, and does not have all of the fields from the first table.
So, I want a form on the left with the oldvalues, a form on the right with the newvalues and a button to copy / update etc...
I have been at this for a while, but have not been able to successfully link the 2 tables and copy the records from one to the other.. ( I am more of a sql person than vba)
You can either using VBA set the row which you are editing on the left as a recordset, then insert it into the a recordset from the newvalue table.
Or you can create a append query based on the current row. eg.
INSERT INTO new (fieldone,fieldtwo) SELECT old.field1,old.field2 FROM old WHERE old.id = FORMS!frmLeft!id
you can have a "=>" button on the left for each row. Once pushed, you run the append query. You should be able to create a macro to run the append query whenever the button is pushed, this way, no coding is needed.
Depending on what do you need to update, I think both ways can accomplish.