Filter continuous form

[LarryBIS]

I have a continuous form that lists books assigned to students. The data displayed comes from 2 tables, so I created a stored procedure "spBookReturn" as the record source The fileds are: StudentID, StudentName, BookTitle.

Then I created a combo box "cboStudent" in a header that lists the student names, and is bound to the StudentID.

I would like the form to open with No records returned until I select a name from the combo box. I then want to filter the form to show all the books assigned to the student that I select in the combo box.

In Access I just added the following parameter to the query under the StudentId field --
"=forms!BooksReturnFrm!StudentID"

Then did a requery of the form.

How can I accomplish the same thing in Access Project.

Thanks

 

[cjowsey]

I won't give the longer explanation right now, so suffice it to say that an SQL View is the SQL Server equivalent of an MDB query. A stored procedure is much more powerful and requires parameters to return exactly the data you want.

Solution 1 is to make your stored procedure a view "vewBookReturn". Don't set it as the form's recordsource because it will return all rows by default. When you select a student from the combobox (I would use the LostFocus event), set the RecordSource to "vewBookReturn" and the ServerFilter to [blue]"StudentID = " & Me.StudentID[/blue].

Solution 2 is to add a parameter to your stored procedure

Create Procedure spBookReturn
[blue]    @StudentID INT = NULL[/blue]
AS
SELECT <insert original code>
[blue]WHERE StudentID = @StudentID[/blue]
GO

Then when a student is selected (again in the LostFocus event), set the form's InputParameters and requery.

Me.InputParameters = "@StudentID = " & Me.StudentID
Me.Requery

You might have to fiddle with whichever solution you choose as I written this without testing the code.