Creating a New Record After Update

[pmontalt]

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).

[June7]

Something like:

CurrentDb.Execute "INSERT INTO [Task Progress] (Location, [Date], Time) VALUES('" & Me.Location & "', #" & Date() & "#, #" & Format(Now(), "hh:nn:ss") & "#")

Why date and time in separate fields? Is Time a date/time or text field type?

Date is a reserved word. Should avoid reserved words as field names.

[pmontalt]

Thanks!! :-)
I created the date and time fields in SQL as Date and Time datatypes, and then linked it. I saw access treats them now as text fields. Should i just have one DateTime field as datatype datetime instead?

[June7]

One field should be easier to work with. This is one case where separate fields for data elements is not usually best.