How you use music in a film is going to be dependent upon the film.
It can be an underscore, simply highlighting the emotional moods of the film.
It can be a Greek chorus or narrator helping to tell the story, especially by using pop songs/songs with lyrics, or giving a sense of era, i.e. "Forrest Gump".
It can be a counterpoint to the action to create irony ("Pulp Fiction"), comedy, etc.
Music can also be diagetic; a juke box in a bar or the radio for example ("Bohemian Rhapsody" in "Wayne’s World"). The use of diagetic music can be blurred to great effect like the way Aimee Mann’s "Wise Up" was used in the film "Magnolia".
You have to consider the type of mood you want to set. If you are doing a film in an gritty urban setting you may want to use a hip-hop/electronica type of score ("Colors"). Sports films can use a rock-oriented score ("Friday Night Lights") implying power. A light orchestral score would work well for a romantic comedy. If the story is set in a rural or wilderness setting a bluegrass/folk/country type of score may be appropriate. War films tend to use very martial sounding music (“Patton”). Then there are period films that may use blues, jazz, classical, etc. (“Ragtime”). Or the film may be set in a "foreign" place so you may want a score using a specific instrumentation ("Memoirs Of A Geisha") to highlight the location. Action/adventure films can combine all of these.
Make sure that you give your composer/music supervisor a very definite direction. A temp score can be very useful in giving your composer that direction, but don’t fall in love with it; it is merely meant to be representational. And don’t forget that silence can be very powerful, as can a well-done sound design; you don’t need music everywhere. Edit the film without any music at all and then decide where music will have the most impact and what you would like to highlight. Many scenes can stand up on their own without any musical help; and music will sometimes detract from the scene. I recently re-watched "Saving Private Ryan" and there is very little music in the film.
Mixing all of the various sonic parts of your project can be very tricky. Many directors have contracted the “louder is better” disease, especially in the action/adventure genre. All this does is assault your audience with volume and detracts from effective sonic impact. The entire soundtrack works better with a lot of sonic contrast. Let your re-recording mixer do his job without your interference until the very end when you can make your comments.
Be cautious of using music in an overly "theatrical" way in an attempt to heighten the emotions of a scene when the emotions are not there to begin with. If a scene needs music to make it work it was probably not well written and/or performed and/or shot and/or edited, unless, of course, the scene was written to use a specific piece of music in the first place. Filmmaking is all about audience manipulation, but your audience shouldn't be aware that it is being manipulated.
As far as royalty free music sources, there are dozens of them out there. Many can be auditioned on-line and purchased by the song so you don't have to buy entire libraries.
