Referencing a control on a form in an ADP query/sp/function

[RobSindle]

I have developed Access front-ends (forever) for Access backends and also for SQL backends via ODBC. This is my first attempt to work with an ADP. I'm having trouble with the syntax for something that I do often in Access front-ends, i.e., base a form off of a query that references a control box on the form itself. In the Access .MDB the query looks like this:

SELECT Projects.IDNum, Projects.Project, Projects.CCountry
FROM Projects
WHERE (((Projects.Project) Like '*' & [Forms]![frmProjectsList]![txtProject] & '*'));

I upsized a trivial form and query and in the ADP the 'function' that was created looks like this (and doesn't work).

ALTER FUNCTION dbo.qryProjectsList
(@Forms_frmProjectsList_txtProject nvarchar(60))
RETURNS TABLE
AS
RETURN ( SELECT IDNum, Project, CCountry
FROM dbo.Projects
WHERE (Project LIKE N'*' + @Forms_frmProjectsList_txtProject + N'*') )


When I open the form, I get a prompt to enter a parameter value for
Forms_frmProjectsList_txtProject

No matter what I put in the control, I never get a returned recordset.
Any help would be greatly appreciated.
Rob

 

[PHV]

In SQL-Server the wildcar is % (not *)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cmmrfrds]

How are you calling the sql server function dbo.qryProjectsList?

The wizard should have converted the Access wildcard character * to the ANSI wildcard %, but it looks like you will need to do that yourself.

 

[RobSindle]

Changing the * to % was the first thing I did. No difference.

I am not CALLING the function per se. It is the recordsource behind my form.

 

[PHV]

And this ?
WHERE Project LIKE '%' + @Forms_frmProjectsList_txtProject + '%')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RobSindle]

I got the answer from another post.
I was not putting anything in the "Input Parameters" property for the form.
Once I did that it worked fine as is.
Thanks.
Rob