Focus stacking can indeed be done manually using layers and layermasks - however this approach can take a very long time, especailly to do properly. Further more using software approaches one can use the stacked shots to both improve the overall sharpnes and reduce nosie present in the shot - often when I get a (working) stacked shot its far less noisy and far sharper than anything else I can produce with my setup.
Software though does have its limits as Ron points out so its not a totally safe bet that a series of shots will work. This is why most who use this process will also grab a quick single shot exposure to record the sight incase the stacking fails.
With a working image stack from software you can get results like this
stack1 on Flickr - Photo Sharing!
and
stack 2 on Flickr - Photo Sharing!
There are more details about each stack on the respective flickr pages linked above. The software I now use is Combine ZP which is free to download and use.
Errors can come in all forms and sometimes be totaly baffling as to the cause - eg below
http://farm3.static.flickr.com/2740/4291932412_67503214d1_o.jpg
However most of the the cause is that you have a frame that is out of sequence or a missing frame. It is key to ensure that when you take the shots you take each shot one after the other with the focus moving into the scene (or out of, though the former is easier for many). You should also ensure that there is overlap between each frame so that the software can match up the different segments.
Below is a common problem where one frame is missing from the end shot and you get a strange duel focused shot
http://farm3.static.flickr.com/2775/4500820507_2d63b3e69e_o.jpg
note the band of out of focus frame in the eye and around the face areas of the fly - its a good shot overall, but the two frames miss out that middle area