Copy record and Update with New

[DarkOne72]

Hello All,

I have a situation to which I don't know the best way to do this, can someone please assist me in doing the following.

I have a search that when you search for a spcific item it displays the record in another form named: frmSearch_Results.
Which works fine. Now here is the tricky part...
I need a button that when click will copy the current record to another table named: tblSwap_History. After it copies to the other table I would like to be able to have a combobox that has a list all the records you can choose one and it auto fills the current record items (replacing whats there).
What this does is backup the original record and allows you to search for a record to replace the current record items so basically your doing a Swap.

Example: You have computer A at location 123, you want to backup that current record to tblSwap_history and then search for a new computer B to replace the current record and it autofill the other fields but keeping some fields the same like location.

Any help would be appreciated.
Thanks in advance...

 

[Andrzejek]

If the structure of the table you copy from (no name given) is the same as the tblSwap_History, consider:

Insert Into tblSwap_History
Select * from tblFromTable
Where SomeID = 1234

Would that work for you?

Have fun.

---- Andy

 

[DarkOne72]

Yes the two tables are the same (Inv1 and tblSwap_History).
The where clause in the statement above would have to be hard coded or can it be the value of the field of the current record.
Also, the current record would have one unique number and then when you want to have a combobox drop down with a listing of records and you select one how would it then populate the fields of choice?

 

[Andrzejek]

Don't hard-code it, just use whatever ID to identify just the (one) record you want to copy. Something like:

strSQL = "Insert Into tblSwap_History " & _
  " Select * from Inv1 " & _
  " Where SomeID = " & SomeVariableHere

Have fun.

---- Andy

 

[DarkOne72]

I copied the code and changed the ID but it did not work but no error either.

 

[DarkOne72]

I actually created an append query to run when the button is pressed which is working fine! =)
but the next part I am lost at to where after the append part runs you can goto a combobox on same form and select a new record and after you select it it will populate that records items in the current form for certain fields. Any ideas?