White only wins one point in material for this tactic but loses a load full in position. It is complicated and you must run through the line to see why it fails.
The game will continue like this:
[FEN ""]
[Title "Halloween Gambit Game"]
[StartPly "23"]
1. e4 e5 2. Nf3 Nc6 3. Nc3 Nf6 4. Nxe5 Nxe5 5. d4 Nc6?! 6. d5 Ne5 7. f4 Ng6 8. e5 Ng8 9. d6 cxd6 10. exd6 Nf6?? 11. Nb5 Qb6 12. Nc7+?? Kd8 { Pretty much forced. This looks weak but actually it will become evident that Kd8 will allow the rook to develop, whereas White is losing a tempo on a piece that will soon be lost. } 13. Nxa8 Qc5 { The queen must move and it is debatable which square is best but let’s examine Qc5. }
White cannot save the knight with 14. Nc7, because after 14… Bxd6 the knight has nowhere to go except back to a8 where it is effectively out of the game. 15. Nb5 doesn’t work because of Re8+, and whichever piece White uses to block the check becomes pinned and the knight is left hanging.
So White should not even consider trying to save the knight and focus on defense. Because, irrelevant of what White now does, the Black attack is coming. Black will soon play Bxd6, Re8 and Black is attacking the king in the middle of the board with queen, rook, bishop and two knights. White’s pieces are all poorly positioned to defend.
However, if White avoids 12.Nc7+, the d6 pawn is actually far more powerful as it prevents Black from developing the bishop and rook into the attack. Hence, black does not have the same powerful attack and Nc7 remains on the cards as a threat for later.
Conclusion: This is a lesson in permanent weaknesses and strengths. The d6 pawn is worth more than a pawn because it is preventing the development of black pieces. Capturing the a8 rook not only loses a tempo but also loses a permanent positional strength.
Consider the following alternate line suggested by Stockfish:
[FEN ""]
[Title "Halloween Gambit Game"]
[StartPly "22"]
1. e4 e5 2. Nf3 Nc6 3. Nc3 Nf6 4. Nxe5 Nxe5 5. d4 Nc6?! 6. d5 Ne5 7. f4 Ng6 8. e5 Ng8 9. d6 cxd6 10. exd6 Nf6?? 11. Nb5 Qb6 12. f5 Ne5 13. Bf4 Qc5
Now it is evident that White will be able to complete development while Black will struggle to develop the rest of its pieces. Black never has time to play Kd8 or Rb8, so the fork always remains a tactic in play.