Auto click on a field which updates to show status of order

[Guest]

I have a database which logs customer orders. One of my forms is an Order
Queue in which various dates and times are recorded to update the status of
the order, (the form displays multiple orders in one liners), I have a
'Status' Field which shows the status of each call, i.e. Logged, Scheduled,
Complete etc - The problem I have is that I can only update the field by
clicking on it for every record. I wnat this to update automatically for
every field. I have tried various methods to automate this so that when a
date is entered the status field updates automatically - I have also tried to
get it to work by clicking on a separate button on the form (which will
refresh the field for all records) - but cant get anything to work!
The code I have used in the Status field (and which doeas work) is below:

Public Sub Status_Click()
If Not IsNull(dtmCompleteDate) Then
Status.Text = "Complete"
ElseIf Not IsNull(dtmPickDate) Then
Status.Text = "Picked"
ElseIf Not IsNull(dtmSchedDate) Then
Status.Text = "Scheduled"
ElseIf Not IsNull(dtmCallLog) Then
Status.Text = "Logged"
End If
End Sub

If anyone an help I would greatly appreciate it!

 

[Douglas J. Steele]

You could put that same code in the form's Current event, and it'll fire as
you move from row to row. However, my advice would be to add a computed
field to the query that's being used as the form's recordsource, and figure
out the status there.

The following IIf statement is equivalent to what you have:

IIf(IsNull(dtmCompleteDate)
,IIf(IsNull(dtmPickDate),IIf(IsNull(dtmSchedDate),IIf(IsNull(dtmCallLog),"Unknown",
"Logged"), "Scheduled") ,"Picked"), "Complete)

 

[Guest]

Thanks Douglas, we've added that and it works a treat!
Cheers