I've been going for the past 3 years and I love it. I make it a point to visit during the day and then stay for the event at night.
The park completely changes at night so it's like you're visiting two different theme parks in one location.
Halloween Horror Nights is geared more for older kids and adults though since alcohol is served and the shows get a little wild. It's a definite party atmosphere so it may be a little too much for young children.
Beware! If you go once, you'll want to go year after year since the event changes all the time. You'll end up spending all of your Octobers in Orlando.