I've got an in house web app(programmed by a freelancer) that I'm testing before pushing it into production and think I've found a SQL injection point, but can't really figure out how to exploit it.

It basically takes the HTTP User Agent header and adds it to a usrlog table. The syntax is like this:
INSERT INTO usrlog (useragent) VALUES ('Injection Point')

There's obviously no output on the page, so I cant use it to really enumerate anything like that, but none of the input is santitized at all. I can throw all the single quotes at it that I want.
The only weird thing is that using -- to comment out the rest of the line doesn't seem to work. Isn't -- supposed to comment out the rest of  line?

I just wanted to know if there's anything that could be done with this kind of injection. If you have any ideas, please let me know.

I would say that at least you can pollute the log file with a bunch of junk, and possibly some sensitive data.  Is the usrlog table being displayed elsewhere?  You can inject an XSS vector. 

Are you using PHP?  mysql_query?

If you can locate the vulnerable piece of code and find any references to it, then it would be easier for you to exploit the web application and also for others to aid you in that process.

What you should be looking for is $_SERVER['HTTP_USER_AGENT'].

Use Grep if you're on Linux, and perhaps WinGrep if you're on Windows to search through all the files in the Web Application.

Correct, stacked queries does not work on PHP and MySQL implementations  

It is possible to pollute / poison the logs with CSRF and / or XSS vector attacks,
however it is also possible to perform completely blind sql injection if all aspects are known or possible to be predicted or enumerated.

In this case, one thing to check is e.g. is magic_quotes turned on?

Possible attack vectors include but are not limited to:
- Altering user and password credentials
- Uploading backdoors in PHP (this requires special permissions.)
- Loading system files and moving them into the "http" (html) directory. (requires special permissions too.)
- Adding new users with administrator privileges.
- Log Pollution / Poisoning as Ketchup said