Using Proc to return RecordSet to Subform

[CSDunn]

Hello,
In Access ADP's that connect to SQL Server databases, any time I have
a situation where I have a combo box in a main form that looks up a
record in a subform, the subform record source has to be based on
either a View or a Table. I can almost always use a View, and it helps
to do this since I can have better control over the size of the
RecordSet of the subform.

There are times when the use of a Stored Procedure would give me
greater control than a View would, but I've always had problems when
trying to use a Proc as the RecordSource of a sub form. If I'm really
just interested in the content of the RecordSet, maybe I could still
use a Proc to fill a RecordSet for use in the subform without having
to address the RecordSource of the subform(?).

I n my current situation, I have a main form that can accommodate one
of several subforms. This application will be used in a K-12 school
system. In the main form, the user selects the name of a test from a
combo box, then the grade level of the test in a second combo box.
After the user makes these selections, they click a button named
‘Set', and the appropriate subform appears in the Detail section of
the main form.

The subforms reflect the selected test and grade level. The
RecordSource for each subform are separate SQL Server Views that are
made distinct by the test and grade level they filter for. It s kind
of annoying to have to have all of these Views, but using a Proc that
takes parameters as the single RecordSource for all subforms has not
worked for me.

Continuing with the main form, after the test and grade level have
been selected and the appropriate sub form is present, the user then
uses a combo box to select a school site. The After Update event of
the cmbSiteLocation combo box executes a Proc that fills the RowSource
of a ‘cmbTeacher' combo box so that the correct teachers for the
selected site will appear in the ‘Teacher' combo box:

Private Sub Combo0_AfterUpd ate()
Me.cmbTeachers. RowSource = "EXEC dbo.ADTeacherCo mbo_sp " &
Me.cmbSiteLocat ion
End Sub

The After Update event of the cmbTeachers combo box does the same
thing to populate the RowSource of a combo box called cmbStudents:

Private Sub Combo7_AfterUpd ate()
Me.cmbStudents. RowSource = "EXEC dbo.ADStudentCo mbo_sp " &
Me.cmbTeachers
End Sub

The cmbStudent combo box is designed to look up a record in the
subform (or start a new record).

What I would like to do, is create the RecordSet for any of the
subforms at the point where a selection is made from cmbTeachers. I
would like to use a Proc for this, one that would take the TeacherID
value from cmbTeachers and return just the student records for that
teacher.

I have been given the following sample code to return a RecordSet from
a Proc:
*************** *************** *****
dim cn as new adodb.connectio n
dim rs as adodb.recordset
dim strSQL as string
dim lngRecords as long

strSQL = "Exec GetStudentRecor ds_sp '" & _
Me.cmbTeachers

cn.open CurrentProject. Connection
set rs = cn.execute(strS QL, lngRecords, adCmdText)
*************** *************** *****
Can I use something like this to create a RecordSet for any given
subform after a selection is made from cmbTeachers regardless of which
subform appears? If so, how might I implement this? Would I just leave
the RecordSource property of the subforms empty if I used something
like this?

Thank you for your help!

CSDunn