That had better be Wednesday, Thursday, Friday or else you might find your posts are lacking in repsponses.
Perhaps state what you want rather than what doesn't work because some of what I see makes no sense.
First, the On Change event does not fire when a form opens:
When the form opens or moves to another record I have two pieces of code which run.
It fires every time you press a key when the control that has code for that event has the focus. This means you're creating a recordset and executing the code with every keystroke.
As far as things seeming to work when flipping between design and form view, that usually means that some part of the code will not result in a setting that is being attempted. The fact that it seems to perform otherwise means that the setting is not crucial to the function being performed. Ignoring errors because something seems to work is risky.
EDIT
Forgot to ask you to post code within code tags (# on forum toolbar).
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.