Well...
.... I have 4+ proposals... one above/ one AT and the 3rd well below the $500 budget...
1st) Given that the best way to get away from grainy images is to shoot with a cleaner and more powerful sensor. I suspect this is no surprise to you. Currently the cleanest and most sensitive sensors are in the DSLRs. Your grainey shots were probably school functions or events in relatively low light (indoor auditorium or outdoor athletic events at dusk or nite). So
a Rebel XT with a kit lense is $684 after the Canon rebate from B&H. You could shave a little more off that price with other retailors - but I might not do it as B&H is "drop dead" reliable. PLUS - if you can wait 6 months then an XT replacement is probably coming out and you'll save a little when that routine starts up and the XT is discontinued.
PLUS - if you want to really keep it close to $500 now then SELL your S40 for $100 on eBay. I have not searched the HISTORY of past S40 sales - but I imagine you would get over $75 for it if you had most of the parts and possibly $100+ if you had everything plus the box. Now the gap in price whoudl be tolerable if not negligible. And the gain is a brand new camera with near maximum shooting power and flexability!
2nd) Buy a used XT, 300d or 10D on ebay. I bought my 10D used on ebay about a year ago for $590. Then take your pick of lenses to suit your taste. I imagine a used XT should fetch $500 or less. And a used 300D is possibly in the low $400s. I sold my old 300D for $490 about 9 months ago. Today - 10d bodies sell on ebay for the same as what I paid. BUt XT and 300D should be less. This brings you under $500 with a KIT lense... but I think this method is NOT worth it since a new camera with ZERO actuations is onlt $184 over your budget. These DSLR bodies in particular have an assumed 35,000 MTBF on actuations. Being a MTBF implies some may need shutter replacement at 25,000 actuations while others need it at 50,000. ow many frames can you shoot in a busy month? SO.... I would come up with the extra moola and sleep soundly with a fresh new body. (Hmmm.... that sort of sounds kind of "entertaining" as a "recommendation"
I am ONLY talking about camera equipment!!!! SHAME on you!
) At any rate.... again, sell the old S40 to bring this comfortably under $500.
3rd) if you buy a light monopod - or make use of a clamp pod/mini tripod then "set" your S40 for ISO 100 and shoot at slow shutter speeds to control the grain. This will NOT stop motion in your subject - and a flash may not be enough to compensate. I don't recall the S40 as having an external flash shoe. But you get my drift. This approach will NOT handle all your likely needs... but it is cheap to implement now without the reselling ritual.
Alternate idea #3)... buy a digicam with an external flash shoe and attach a powerful flash. Shoot at ISO 100 and limit your shots to the flash range of the external gun. The upside is that you'll probably be able to keep it well under or around $400 with used stuff off eBay... like a G-1 for $125 or G-2 for $180-200 and a 420EX flash for the low $200s. AND the flash is usable when you upgrade to a DSLR down the road. The down side is you're stuck at the limit of your flash illumination range... plus many shooting opportunities call for NO FLAsH USAGE... like gymnastics, sports and stage events.
4th) Buy the cheapest Pentax DSLR KIT. That is slightly cheaper then the Rebel XT KIT after rebate. HOWEVER... one step up from the cheapest Pentax gains you IS in the body! Image stabilization in the body! Interesting??? But the lenses and everything else are not like the XT. I frankly don't have any idea how well the Pentax DSLRs perform. All I know is no one is reselling them on eBay - so very few are being purchased. Canon alone commands 60% of the DSLR market last I heard.
Oh well... hope this helped!
