Combo Boxes and Stored Procedures

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Combo Boxes and Stored Procedures

Author

Topic

PETE314
Starting Member

37 Posts

Posted - 2004-12-29 : 11:45:38

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.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-02 : 10:49:14

Quite easy, Pete. Simply use VB instead of Props Sheet. E.g.:

Private Sub myForm_Open()
Me.myCombo.RowSource="exec myProcedure " & Me.myTextBox
OR
Me.myCombo.RowSource="exec myProcedure " & "'" & Me.myTextBox & "'"
End Sub

Here I assume the myTextBox has some valid default value. Then,
for to refresh it:

Private Sub myTextBox_AfterUpdate()
Me.myCombo.Requery
End Sub

PETE314
Starting Member

37 Posts

Posted - 2005-01-03 : 13:48:37

That was what I was thinking I was going to have to do....would it be better to set the row source on the "Got Focus" event for the combo box???

I have 3 comboboxes being treated this way and the last 2 depend upon the values of the combobox(es) before them.

also just to double check my syntax....for multiple parameters would the parameters be separated by a comma???

ie.
Me.myCombo.RowSource="exec myProcedure " & "'" & parameter1 & "', '" & parameter2 & "'"

jhermiz

3564 Posts

Posted - 2005-01-03 : 14:08:08

No you should not load it in the got focus, it seems like a logical solution however, to the end user waiting for records to load while already at the screen is painful. The logical thing to do is load the combo box if it isnt a cascaded box in the load event of the form.

Otherwise if it is cascaded and dependant on another combo box you should load it in the after update event of the first combo box.

Yes for your rowsource on the last question. You could also just create a string to store the result.

Dim strSQL as String

strSQL = "exec myProcedure " & blah & "," & blah2 ...
Me.cboMyCombo.RowSource=strSQL

BTW, just an FYI, you can use the command object to call a stored procedure in access. Its just like calling it in vb6, you just need to make sure you reference the ADO object library.

Jon

A new beat on the web -- http://www.web-impulse.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources