Trouble with input parameters and subforms.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to filter a subform using an input parameter and a function. The
main form (frmStudent) and the subform (frmSubStudentYears) are linked
together on a unique ID. The value of the filter is determined from a combo
box on a seperate form (frmSelectYear.ComboxYear).

The properties of the subform are:
recordsource = "Select * from fnSubStudentYears
(@[Forms]![frmSelectYear]![ComboxYear])"

input parameter = " ? = (@[Forms]![frmSelectYear]![ComboxYear]

The SQL Statement for the function is:

ALTER FUNCTION dbo.fn_SubStudentYears
(@SY char(4))
RETURNS TABLE
AS
RETURN ( SELECT dbo.STUDENT_YR_DATA.*
FROM dbo.STUDENT_YR_DATA
WHERE (SCHOOL_YEAR = @SY) OR
(@SY = 'ALL') )

If I open frmSelectYear and select an option from ComboBoxYear then open
just frmSubStudentYears the parameter passes correctly and the records
returned are correct. However, when I open frmStudent which has
frmSubStudentYears as a subform I get the error message " Must declare the
variable ". " I spaced out the quotes from the statement because the
statement it's self contains a quote. If I change the recordset of the
subform to be the base table that the function is built off of and remove the
input parameter I get no filtering and no errror messages respectively.

Any advice would be greatly appreciated. I can't find anything on this
anywhere. I've followed KB article KB278400 to get the idea on how to use an
input variable with a function. I need to use a function rather then a
storeprocedure because it seems that when I use a storeprocedure the link
between the main form and the subform becomes disconnected.

Thanks in advance!
 
This is an interresting situation. I know what you are talking about with
subforms becoming disconnected when using SQL Server as the backend. My
solution has been to not try and make them connected. I simply reset the
recordsource of the subform everytime the parent form changes. It has worked
well so far. I would have to do a little research to figure out the function
thing though. I'll try to take a look at it and get back to you in a day or
so.
 
Back
Top