Creating new record after update

[pmontalto]

Hello. Have a quick question that I can't seem to figure out...looking for some help.

What I have:

I have a table called [Tasks] which has the following relevant fields:

[Task ID]
[Location]

I have a form that people use to create new tasks and update current ones. Location on the form is a combo box where people can pick from 5-6 different locations.

What I want:

After someone selects a new location from the combo box and changes the task location, I want to create a new record in a table called [Task Progress] with the [Task ID] of the task whose location was changed, the new [Location], and the [Date] and [Time] it was changed. It would look like

[Progress ID] [Task ID]  [Location]  [Date]     [Time]
1               5        Station 1   1/1/2011  12:13:01
2               8        Station 2   1/3/2011  01:53:29
3               5        Station 2   1/5/2011  11:13:05
4               5        Station 3   1/6/2011  12:35:22

What should I put in the AfterUpdate event for the [Location] combobox to make the above happen? Thanks!

Note: All my tables are ODBC linked to SQL Server (can't use data macros).

 

[JHB]

Then use an append query, which is run in the after update event.