One of the 'fun' things that Access will do is when you are typing in the ControlSource, it will also make the textbox's name the same as what you typed for the ControlSource. And basically that makes the #Name error when the ControlSource is a function. I am not sure if it is a circular reference or just it gets confused.
Try changing the textbox's Name to txtSomething with your original ControlSource.
The Query_ranking is a query that has 10 different categories from a table and the total ranking is calculated using an expression. I'm trying to populate the value of the total of the categories on the form if that helps at all.
For starters, nothing is ever equal to Null; you want:
If IsNull(Ranking) Then
As mentioned earlier, I'm not sure this will work in the before update event. Try it in the after update event. Either requires you to be changing the value in that textbox, which doesn't really make sense if your intention is to set the value programmatically. I'd probably use the current event of the form or some other event to trigger this.