Can I use a sp w/ parameter as combo box row source?

[NKTower]

I have a working stored proc that takes one parameter and gives me exactly
what I need for populating a combo box. The stored proc does sums on the
results of a UNION query with 3 very distinct components - the only variable
is a 'region_id' parameter. Is there a way to reference the sp as the combo
box source and pass in the parameter? I have no problem with tweaking the
cbo's properties at run time.

If not, then I guess I could define the view to do the summing with the
region as one of the GROUP BY's and then in the front end write SQL to only
grab the summed rows with the appropriate region.

I expect to get pehaps a dozen rows in the result set, but they are
gathered from 3 tables with about 35,000 rows each.

Access 2003 front end, SQL Server 2005 back end.

 

[Sylvain Lafontaine]

All you have to do is to set the RowSource of the combobox to an EXEC
statement:

Me.MyComboBox.RowSource = "EXEC MySP " & region_id

Dim dummy as long
dummy = Me.MyComboBox.ListCount

Set the RowSource each time you need to change the value of region_id. The
dummy instruction is there only to give a better visual feedback when the
user open the combobox the first time.

 

[NKTower]

Perfect. Thank you.

All you have to do is to set the RowSource of the combobox to an EXEC
statement:

Me.MyComboBox.RowSource = "EXEC MySP " & region_id

Dim dummy as long
dummy = Me.MyComboBox.ListCount

Set the RowSource each time you need to change the value of region_id. The
dummy instruction is there only to give a better visual feedback when the
user open the combobox the first time.