Update table after field change

[hlkelly]

I have a table which contains a "tracking number" field. After the field is updated I would like a check box to reflect a "true" state. The tracking number field is populated by an import of sorts (ODBC connection to a Fed Ex program). If I update the tracking number field in a form, this works:

Private Sub TrackingNumber_AfterUpdate()
Me![Processed] = True
End Sub

But if the tracking number field is populated straight to the table, it doesn't work.

I just want the order to reflect a "processed" state after a tracking number is no longer null.

Any ideas?

Thanks.

Heather

 

[GingerR]

How about after the "import" and the field is updated by the imported table contents, run an UPDATE QUERY to just set the checkbox to TRUE for every row where tracking number is not null.

By the way, in your After Update statement: what if someone blanks out the tracking number? The AfterUpdate will still fire, and check your box TRUE. you might instead want to put something like:

if isnull(me.TrackingNumber) then
Me![Processed] = false
else
Me![Processed] = True
end if

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[hlkelly]

This sounds great but the update query would be placed in a form, right?

Good point about changing my existing statement.

I'll just give them a button to push I guess that runs the update query. I was trying to eliminate steps but I guess that isn't going to be possible.

 

[PHV]

In the Open event procedure of the form:
[tt]CurrentDb.Execute "UPDATE yourTable SET Processed=(Trim(TrackingNumber & '')<>''"[/tt]

In the BeforeUpdate update event procedure of the form:
Me![Processed] = (Trim(Me![TrackingNumber] & "") <> "")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886