Yes it is the Orpheo's Curse game and YES it truely is great fun
Since then I lost it but have been wanting to play it recently so I looked it up on grabbed it on the bay for like 10 bucks (shipping too). It took me a looooong time to get it to run on today's PC (I had to use the MSDOS emulator called DOSBOX) but I'm glad I did, brought back good memories, just not as scary anymore as I had hoped.
The game pretty much is just you walk around a deserted wax museum and click on things to solve puzzles. There's a curse on the building so by clicking on things sometimes the wax people/paintings/objects come alive and make you "jump" like in movies. The graphics/audio has REALLY held up all this time and is still great. Honestly if you like these role-playing games you can't go wrong with it (if you can find it and get it to work...)
http://www.mrbillsadventureland.com/reviews/a-b/afraiddkR/afraiddkR.htm
(Some pics, story, how to get it to work)
http://www.mobygames.com/game/dos/are-you-afraid-of-the-dark-the-tale-of-orpheos-curse/screenshots
(Here's some more screens that are REAL gameplay, not cutscenes)