Why did Major Fambrough commit suicide on Dances with Wolves?

Answer 1

With a bit of explanation from the novel, it is revealed that Fambrough had lost his sanity. This ultimately led to Dunbar's departure to Fort Sedgewick being unknown to the military. Captain Cargill, the Commander of Fort Sedgewick, was abandoned by Farmbrough's command. He hadn't consistently kept the men of Sedgewick stocked with supplies. In desperation, they left to return to Fort Hays and ultimately requested that Fort Sedgewick be abandoned. From the novel:

"...ending his report with the recommendation that Fort Sedgewick be permanently abandoned, at least until further notice. General Tide was inclined to agree wholeheartedly, and within days Fort Sedgewick ceased to be connected with the United States government. It became a nonplace."

This led unfortunately to the reality that only 2 people knew that Dunbar had been shipped out to Fort Sedgewick: Fambrough and the civilian, Timmons. The latter had been dispatched by Fambrough to take provisions to Fort Sedgewick with Dunbar in tow, and the former being without his mental faculties and in no condition to relay Dunbar's whereabouts. From the novel:

"So it came to be that only one person on earth was left with any notion as to the whereabouts or even the existence of Lieutenant Dunbar.

And that person was a poorly groomed bachelor civilian who mattered very little to anyone.

Timmons."

The suicide of Fambrough in the film seemed an expedited way of letting the audience know that Dunbar's whereabouts were lost when Fambrough killed himself. This eliminated the need for the scene depicting the abandoning of Fort Sedgewick, the events that led to the Fort being abandoned outright by the military, and the subsequent dispatch of Fambrough "east" to most likely deal with his mental instability.

Answer 2

The reason he commits suicide is because he has abandoned the fort where Costner is being shipped. That's the reason he pissed his pants when he found out where Costner was going. That's the reason he reacted so haughtily when Costner asked how was he going to get to the fort. The expensive wine is a sign that he may have been pocketing money from the supplies to the fort instead of sending the fort supplies.