Can I pass a variable to a function parameter?

[Katie Howard]

I have an Access 2007 database that's linked to a SQL server. There's a function GetTLMembers on the SQL server that takes an employee ID parameter and returns all of the people who are managed by that employee.

In addition to this, I have a function in Access that returns that Windows log-in name of the person connecting, and then I'm matching that login with a table to return the employee ID number. This part works fine.

Then I'm trying to call the GetTLMembers function in a query, and my parameter is my stored variable of the ID number. This doesn't work - it just shows me the query without any data.

select * from GetTLMembers('strUserID');

But when I use a constant for the parameter, it works great - but isn't very useful.

select * from GetTLMembers('1451');

Can someone tell me what I'm doing wrong? Or is it just not possible to do what I'm trying to do?

thanks!

[NeoPa]

I'm a little restricted here as I no longer have a SQL Server to play with. That said, I believe the way is to use a Pass-Thru query. Either that or do it in code using a fully-qualified connection to the SQL Server. That is where I run short I'm afraid. The details are not something I can easily check up on.