If you've never owned any kind of SLR in the past and your initial intentions are just as an upgrade from a PnS to get the no shutter lag and faster start up time and get a better sensor for better pics, then the D40 is a very good camera.
From your post it may be a very good fit for you. Coming from a PnS it really isn't a big camera compared to most of the other dSLR's. While the D40 will not auto focus with older lenses there are still many lens options in the Nikon line (around 30 or so) and Approx 17 from Sigma, not sure about which specific ones from Tamron and Tokina.
It has a great sensor and its high ISO performance is right with the D50, which happens to be one of the best in the line.
While down the road with this particular camera you wont be able to auto focus with the f/1.8 lenses, there are a large number of f/2.8 zoom lenses that do work. You'll want to look for any of the Nikon lenses that have AF-S (don't worry about the AF-I lenses as no one mentions them because they are the $10,000+ lenses and there aren't that many of them). All the new Nikkor lenses are being made AF-S. Some of the more recent ones are farily affordable including the new 50-200mm ED-IF AF-S VR. The kit lens that comes in the D40 package is pretty good, but for me its not long enough at 18-55mm. I originally went with the 18-70mm for my D50 and have since gone up to the 18-135mm (both are AF-S lenses).
No need to worry about flash compatibality as the 3 newest i-TTL flashes will work no worries (SB-400, SB-600 & SB-800).
While the D40 is not for me, it is by no means a bad camera. It produces great images, it's small and managable, easy to use, has full SLR capabilities (fully manual or you choose the modes to control what you want) and is affordable. If it feels comfortable in your hands, then that is really the most important thing.