Need Help Time Stamp when updating a rows data

[jimbo62]

Access 2000 .ADP and SQL Server 2000

Hello All,

I have created a table that allows users to input data fom a form and update the TOTAL_RESOURCES_ASSIGNED column. When a user modifies this value on this row I need the current date time inserted in a field for time-Stamping.

SEQ int 4 0
Status char 50 1
CATEGORY char 25 1
[ZONE] char 50 1
TOTAL_RESOURCES_ASSIGNED decimal 9 1
Update_By char 10 1
Update_Time datetime 8 1

I have tried setting the default value on the form but the value is not saved in the table. I also have assigned the GETDATE function to default on the Server side and it throws errors because it says that another user has updated the value.

Can I do this with an update statement on the click event or something.

I have tried everything I could think of. It has to be simple this is a common practice I am new to SQL server and ADP.

Please..... any help would be appreciated.

Jimbo

 

[cmmrfrds]

When you say time stamping does this mean the data type of the field is TimeStamp or is it DateTime. TimeStamp fields are updated by sql server not the user. My understanding is that it is a sequential binary number. If you want to see the date that you put a date and time into then it must be a DateTime field type.

 

[jimbo62]

It is a date time field and I want to have the date and time the row was updated.

Thanks...
Sorry for the delay I was traveling...

Jimbo

 

[cmmrfrds]

The best, so that you don't need to deal with it on the Forms is a trigger on the table to update the date. Example.

Example of Mulitple record trigger.
Parent/Child relationship

create trigger trig_ModDate_InvoiceDetail
on InvoiceDetail
for update
as
declare @ID int
select @ID = invoiceID from deleted
if @@rowcount = 1
BEGIN
UPDATE InvoiceDetail
SET modifyDate = getdate()
WHERE invoiceID = @ID
END
ELSE
BEGIN
UPDATE InvoiceDetail
SET modifyDate = getdate()
WHERE InvoiceDetail.invoiceID IN
(Select InvoiceDetail.invoiceID from inserted)
END

Note, sql server has 2 hidden tables that are used when dealing with triggers. inserted and deleted. During an update there is a delete and then an insert. You can access these tables from the trigger.