Apply filter to recordset combo box?

[Hoving]

When a user clicks buttons "School A" or "School B" on a
switchboard, a form opens with records filtered according
to which button was pressed (e.g., records of students in School A). What doesn't get filtered is a recordset combo box on the form where the user can select a student's name to call that record. Students from both schools are still listed in the combo box.

How do I set things up so that when the School A or School B button is pressed, the recordset combo box on the form is also filtered to list only names associated with the selected school?

Thanks.

- Hoving

******
The OnClick event for the School A button:

Private Sub SchoolA_Click()
' Open frmStudents filtered for students in
' School A only.

DoCmd.OpenForm "frmStudents", , , "SchoolID = 1"
End Sub

The Row Source for the combo box that I'd like filtered as well (cboShowRecord):

SELECT [tblStudents].[StudentID], [tblStudents].
[LastName] & ", " & [FirstName] AS Name
FROM tblStudents
ORDER BY [tblStudents].[LastName];

The AfterUpdate event for the combo box (if it matters)
is:

Private Sub cboShowrecord_AfterUpdate()

' Find the record that matches the name selected.

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[StudentID] = " & Str(Me!
[cboshowrecord])
Me.Bookmark = rs.Bookmark
End Sub

 

[jebry]

Hi!

I don't know if this will work or not, but you can try it:

Create a hidden text box called txtSchlID.
Change your form call to:
DoCmd.OpenForm "frmStudents", , , "SchoolID = 1", , " And SchoolID = 1 "

In the form's Load event, add the line:
txtSchlID = Me.OpenArgs

Change the combo box's RowSource to:

"SELECT [tblStudents].[StudentID], [tblStudents].[LastName], [FirstName] AS Name FROM tblStudents
Where " & txtSchlID &
" ORDER BY [tblStudents].[LastName];"

Alternatively, and potentially easier, you can create a query to base the combo box on that reference the hidden text box and fill the text box with the schoolID either 1 or 2.

hth
Jeff Bridgham
bridgham@purdue.edu