Politicians never listen to "Joe Public". The only things they listen to is who can further their cause for staying in power (which means conning the voters every 3-4 years) and who feeds them the money. But all that is beside the point.
The Apollo Program cost $25 billion over it's lifetime. The Vietnam War was costing more than that per year. If "Joe Public" was concerned about fiscal propriety and expediency, then they should've looked harder at what their government was really doing with the money they spend and what they spend it on. Just to give you a more modern example, the US DoD in the last few years has "misplaced" $2.3 Trillion!!!!!!!!!!!, and can't account for where it went and what it's been spent on!!. NASA's yearly budget is around $15-$16 billion. If NASA even saw 1/10 or 1/50 of what the DoD "misplaced"...can you imagine!!!!
Sure, going to the Moon is risky and has its dangers, but so does booking a ticket and getting on a plane...in actual fact, I'd stake my life on NASA being able to build a safe spacecraft to get me to the Moon than I'd put my faith into most airlines these days. You only have to look at the track record of many of them to see it's not exactly 100% foolproof. Everything we do invites risk. If we're going to pull back from doing something just because it seems too hard or too risky, we'd still be walking around in bearskins and living in caves. I like the romantic notions of going to the Moon or Mars...or anywhere else for that matter. But to pull the rug from out under something momentous, something that could've brought new opportunities and enterprises we can't even imagine, just because they felt it was not the "in thing" to be doing (they'd rather be killing people for some pseudo sociopolitical claptrap) is nothing short of criminal.
It wasn't making "America" (and by that, I mean the military-industrial complex) enough money at the time, so they had their political lackeys pull the chain on it.