I feel this is a problem more common than people realize or want to admit. I myself have a hard time staying hard when I'm about to fuck someone; it's why I usually bottom. I have no issues getting hard any other time; it's just when I'm about to enter an ass.
For me, I'm not even thinking about "I'm about to go in" or "Hope it goes well". I just legit start softening until it's nothing. And strangely enough, it's always when I use a condom that it goes down. Kinda makes me feel like George from that one Seinfeld episode where he complains about losing an erection between the time of getting a condom and opening it.
But seriously, I have no mental qualms about wearing condoms. It just seems to affect me as such when I try my hand at topping. It's somewhat better when I bareback fuck someone, but I reserve that for only one person whom I trust but is a total top who miraculously lets me at him once in a blue moon.
That said, it's best to talk to your doctor to see if there's anything you can do to change it.
A doctor though may help may not..
::)