Parameter for Access Function

[brownmary]

I have an In-Line Function in an Access 2003 database attached to a SQL Server. I am trying to set up a Form to enter a parameter for the function. Everything works fine mechanically with the form (it opens the function) but it does not retrieve any records.

I believe the problem might be related to the Criteria field in the function. According to non-SQL directions for creating a parameter in a form, I enter [Forms]![Name of Form]![Field] in the Criteria. However, Access adds an = sign and ' ' marks. My SQL for the criteria looks like:

ALTER FUNCTION DeansOffice.[Check PT]
()
RETURNS TABLE
AS
RETURN ( SELECT TOP 100 PERCENT Term, PTLastName, PTFirstName, ACC, CourseNum, Section, Salary
FROM dbo.PTFaculty
WHERE (Term = '[Forms]![PT Faculty by Dept and Term]![Term]') )

What am I doing wrong?

Thks.

Mary

 

[hneal98]

What does the code look like from your form. Are you just using the field? I would create code behind the form and have it execute the stored procedure from SQL and pass the perameter at that time.

Others may do it differently.

 

[brownmary]

The Event Procedure when a user clicks the OK button is:

Private Sub OK_Click()
Me.Visible = False
DoCmd.OpenFunction "Check PT", View:=acViewNormal, DataMode:=acReadOnly
DoCmd.Close acForm, "PT Faculty by Dept and Term"
End Sub