Does the Prophet's — peace and blessings be upon him — saying: "Whoever goes to a soothsayer and believes what he says, his prayer will not be accepted for forty nights" apply to someone who went to a charlatan, thinking he was an herbal healer? And is going to him considered a sin that necessitates repentance?

Going to seers and sorcerers is forbidden (haram). Whoever goes to a fortuneteller and asks him something, his prayers will not be accepted for forty nights. And he is not of us who practices sorcery or has sorcery done for him. If your questioning of him was before you knew of his sorcery and charlatanism, and you stopped asking him immediately after you found out, then there is nothing upon you. Otherwise, you are sinful. You both must offer sincere repentance, which is abstaining from the sin, regretting it, and resolving not to return to it.