Update field based on the value in another field

[SMosley804]

Is there a way to update a field on a form with the current time based on the value of another field.

I have a field called [Status] and [DateDelivered], I need the [dateDelivered] field to be updated when the [Status] field changes to 'Complete'

I've tried to make an expression that reads:

iif ([Status]="Complete",Now()," ")

I also tried to create a macro but neither seems to be working properly. the [DateDelivered] field gets updated but it gets updated no matter what the status changes to.

Please help!

 

[ClydeData]

In the after update property of the status field put this code



If Me.Status = "Complete" Then
Me.dateDelivered = Now()
End If

Hope this helps

Jimmy

 

[SMosley804]

i inserted the code but i get an error;

'compile error: end if without block if.'

 

[ClydeData]

have you put the code within another if statement which hasnt been closed?

You could perhaps post all the code in your after update event

Jimmy

 

[SMosley804]

I believe that's what happened. I had some resi=dual things that needed to be removed. Once I did that, it worked fine.

thanks!

 

[ClydeData]

Cheers, glad to help. you should perhaps look at having a status history table

a simple arraingement of a table called tblStatusHistory with fields
YourPrimaryKey
Status
StatusDate

The following code in the status field after update event would keep a history in this table


Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblStatusHistory", dbOpenDynaset)
rs.AddNew
rs!YourPrimaryKey = Me.YourPrimaryKey
rs!Status= Me.Status
rs!StatusDate = Now()

rs.Update




cheers

Jimmy