Run a query to append only the current record to another table

[ephi]

I have 2 tables in my database (Assets and AssetMovements) .
What I'm trying to achieve is that when the value of the 'Location' field is modified in a record in the 'Assets' table, once the user clicks the save button the record should be saved normally and a copy should be appended to the 'AssetMovements ' table.

The problem is that my current query appends all the records in the 'Assets' table to the 'AssetMovements ' table. I want only the current record to be appended. Also, I want the query to only run when the value of the Location field is modified.

Below is my VBA code for the button's OnClick event:

DoCmd.RunCommand acCmdSaveRecord
 
DoCmd.OpenQuery "AppendQuery"

I would appreciate any suggestions.
Thanks.

[Seth Schrock]

Your query needs the WHERE clause to filter it down to the current record. Without seeing your query, it is hard to say more than that. If you could copy and paste the SQL code of your query into here (using <CODE/> tags) we can provide a more specific solution. We would also need to know which field is the primary key field.

I do have an idea for how to only execute your query if the location field was changed, but once again, this is a separate question and needs asked in a separate thread. We can work on the query in this thread as that question matches the thread title.

[ephi]

Hello Seth, here is my query code:

INSERT INTO AssetMovement ( ID, Location, Condition, AssignedTo, AssetTag )
SELECT Assets.ID, Assets.Location, Assets.Condition, Assets.AssignedTo, Assets.AssetTag
FROM Assets;

Thanks

[Seth Schrock]

Okay, you just need to add a WHERE clause before the ; that is at the end of your query.

WHERE ID = Forms![I]your_form_name[/I]![I]your_control_name[/I]

The control name that you need is the control that has the data source of ID.

[ephi]

Thank you Seth. The WHERE clause fixed the problem.
I will post the second part of the question ion another thread.
Thanks so much for your assistance.

[Seth Schrock]

No problem. Glad to help. I will keep a lookout for your new thread and explain my idea there.