well the question is do you plan on using 2x single core chips or 2x dual core chips? If you had intended to use 2x single core chips, you may as well just grab a single socket AM2/939 and just drop in a single dual core chip. At least that way you'll have an easier time finding an AGP board and you would be able to use standard DDR 400 (939) or DDR2 800 (AM2) which are cheaper than registered DDR 333/400 dimms you would use on a single or dual socket 940 system.
The 940 boards have fallen out of retail light, but you will find them as highly popular items on a lot of other sites, such as 2cpu, the for sale section over there is full of em

Your best bet for a 940 board is ebay (which is kinda bare right now) or forums like 2cpu (a multi processor enthusiast website). Most of the places with retail 940 boards are only gunna have the latest of discontinued boards, meaning pci express instead of AGP. But you will find some great boards out there like the Asus K8N-DL which is small, full featured, and affordable.
But really, you wont have any trouble selling an AGP x1950 pro as they are among the fastest AGP cards available, save for the x1950xt which is getting harder and harder to find and the new HD3950 AGP which is yet to hit most sites. X1900 series are great for Folding At Home enthusiasts, so even many of the people here would be interested in it

And pci express cards as fast or faster retail for pretty cheap, many options in the $100 range that are gunna perform very well.
The easiest upgrade would be to buy a single socket system that uses 1x dual core or quad core chip as it will be a big upgrade from what you have, and you would be able to use components that are much cheaper. Dual core AM2 chips start at about $70 and solid performers at about $100, solid boards about $60+, 2gb DDR2 800 is about $50. Quad core AM2 chips like the Phenom X4 9500 can be had for about $190.
Dual core LGA775 Intel chips start at about $70 on the cheap side, solid performers in the $150 range, great boards can be had for $80-$100, and that same 2gb DDR2 800 for $50, but hell 4gb can be had for less than $100 these days. Quad core LGA 775 are about $260-$280.
All of these options would be great upgrades from where you are now, and selling a dual 2800 system and AGP 1950 pro would cover most of the upgrade costs involved. Going with a modern 1207 or LGA771 system build will be very costly.
Check 2cpu, put up a WTB (want to buy) thread, I'm sure you'll find someone with a dual 940 AGP board, and there are tons of people selling 2x matched single core 940 chips or 2x matched dual core 940 chips.