Sending Parameter to Stored Procedure from a Combo Box

  • Thread starter Thread starter CSDunn
  • Start date Start date
C

CSDunn

Hello,
I have a situation in which I need to have a combo box in a main form in
which the bound column is an Integer value, send the selected value as a
parameter called '@SchoolNum' to a SQL Server 2000 Stored Procedure called
'InstrumentStudentAssign3_sp'. This procedure is the record source of
another combo box in a subform. Once the procedure gets the value based on
the selection made in the combo box of the main form, the combo box in the
subform should show records consistent with the parameter value.

The combo box in the main form(the form is named 'frmMain2') is named
'Combo18'. I have the following code set up for the 'AfterUpdate' event of
this combo box:

Private Sub Combo18_AfterUpdate()
Dim cmd As ADODB.Command
Dim prmInput As ADODB.Parameter

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "dbo.InstrumentStudentAssign3_sp"
Set prmInput = cmd.CreateParameter("@SchoolNum", adInteger,
adParamInput, , Forms!frmMain2!Combo18)
cmd.Parameters.Append prmInput
cmd.Execute , , adExecuteNoRecords
DoCmd.Save acForm, "frmMain2"

Set prmInput = Nothing
Set cmd = Nothing
End Sub

Notice at the end of the code I have 'DoCmd Save acForm, "frmMain2". I
thought that by doing this, that the combo box in the subform would update
to show records consistent with the parameter value, but it does not.

When I test the code by selecting a schoolname that has a corresponding
value of '430' from 'Combo18' with a Breakpoint at 'cmd.Execute , ,
adExecuteNoRecords', I can see that 'Forms!frmMain2!Combo18'="430", and
'cmd.Parameters.Append prmInput'= 430.

So it seems like I am getting the parameter to the procedure, but the combo
box in the subform does not reflect the correct values. Is there an
additional step I need to take so that once the parameter is passed, the
combo box in the subform will show the correct values?

Please let me know if you need more information.

Thanks for your help!

CSDunn
 
I would use something like this in the after update event
of the combo box on the parent form.
me.<Subform
Name>.Form.<SubFormComboName>.RowSource="EXEC
InstrumentStudentAssign3_sp " & me.COMBO18

This will set the row source of your subform combo box in
a manner that passes in the parameter, and filters it for
just the records you need.
 
Back
Top