My preferred way would be to create a subclass of a textbox. This would have no control source but would have custom SetValue and SaveValue methods.
The SetValue would be called from the Form's Refresh method and it reads the value from the database, converts it to $US and puts the result in the value property. if you do this, make sure that you put a dodefault() in the Refresh method otherwise the rest of the form won't undate. In the InteractiveChange event of the textbox call the SaveValue method. This will calculate the $CAN from the Value property and update the database with it.

Andrew