Combo Boxes and Stored Procedures

[PETE314]

Hopefully this is a simple question to answer.....

Access 2000 .adp FrontEnd to a SQL 2000 BackEnd

I am using a stored procedure as the RowSource for a Combo Box. The Stored procedure is basically a parameter Query. When I use this sort of stored procedure as a record source for a form, I have an "Input Parameters" Property to use to enter the parameters for the stored procedure. I do not have this property for the Combo Box.

So how do I pass the parameters to the stored procedure????

Thanks for any help in this matter.

 

[cmmrfrds]

It is quirky. For some reason if you put all the sql in a string and then reference the string it works. Here is an example of passing 1 parameter.


Private Sub title_id_GotFocus()
Dim str As String
str = "exec spGetTitles " & "PS7777"
Me.title_id.RowSource = str

End Sub

 

[PETE314]

So the row source needs to be programmatically set everytime the combo box has focus?....it cannot be done within the combo box's properties????

 

[cmmrfrds]

The focus event is a method of the combobox, it could be done in other events if that works better for you.

What properties are you thinking of???

 

[PETE314]

Well all the objects have a properties window that can be brought up.....I was just trying to confirm that the RowSource for the combobox would have to be handled in code as opposed to the Properties Window. I mean what is to stop me from entering the following into the RowSource property in the Properties Window...

exec_spGetTitles PS7777

or

exec_spGetTitles me.object.value

I understand tho that in code would probably be a better method.

 

[PETE314]

Ooops...copied the syntax incorrectly

exec spGetTitles me.object value

 

[cmmrfrds]

My understanding is that you tried that and it did not work. If it works go ahead. What I was suggesting is that if you prebuild the full string and put it into a variable that it will work.

 

[TomCarnahan]

Any recommendations of how to extend this solution to a ListBox?

The contents of my unbound ListBox are based on several values obtained from controls on the form. I want to invoke a stored procedure with input parameters and have it set as the rowsource of my Listbox.

The listbox will be in the multiselect mode so that whatever the user selects with CTRL-Click or ALT-Click, will be selected. I need to get the PKs of those selected records back to SQL Server to run an update query.

Any thoughts on how to populate the ListBox using the Input Parameter? Ideas of how to pass the PKs of the selected records back to another stored procdure that will do an update of the original table?

Your assistance is appreciated!



--- Tom

 

[cmmrfrds]

Tom, did you try my suggestion of putting all the information in a variable for the rowsource on the listbox.

To use parameters on a stored procedure in VBA code, use the ADO Command Object. There are numerous examples on the Web just do a search.