This is the crux of his issue (IMO). This event and the need to feel like a parent and provider is what is dropping him into stoic/martyr/whatever mode he has in his mind.
From what I have read and hopefully you don't mind the share from a male POV, he may feel completely inadequate sexually. As you feel and know and have written about, you are a highly sexual person that has great capacity for intimacy and he maybe talking about coming up really short and a failure.
So until he resolves both of those issues, he will remain disconnected from his sexuality and emotions, IMO.
What makes it additionally hard is given how men have not been assertive enough in their own masculinity and emotional development, it will take all that much longer if at all. He is in his own hellscape.
Is it for you to fix or help? Hell no, but you are showing humanity by trying to help. I don't know if you have talked or share (and not for me to know either), but maybe tell him he needs to get a threapist and spend time visiting a sex worker to release his needs if solo loving is not enough.
It is really hard watching a person we loved or love go down the rabbit's hole of hell. It just sucks.