Error calling a function in SQL Server

[Robe]

Hi there,

I’m doing a form using two ComboBoxes the second depends on the first. The
first let me select a worker, once I select the worker then the second is
filled with some data that I get from a stored procedure. Everything is
working very well but now I need to convert the stored procedure to a
function because I need to reuse the code inside it and when I select a
worker I get this error “The parameter is invalid†in the following statement
Set objRs = objCmd.Execute.

Here is the code for the workers combobox in the form

Private Sub ComboWorker_AfterUpdate()
Dim objConn As New ADODB.Connection
Dim objCmd As New ADODB.Command
Dim objParm1 As New ADODB.Parameter
Dim objRs As New ADODB.Recordset

' Connect to the data source.
objCmd.ActiveConnection = CurrentProject.Connection
' Set CommandText to retrieve data from the stored proc
objCmd.CommandText = "spWorkerTools"
objCmd.CommandType = adCmdStoredProc
' Automatically fill in parameter info from stored procedure.
objCmd.Parameters.Refresh
' Set the param value.
objCmd(1) = ComboWorker.Value
' Execute the stored procedure
Set objRs = objCmd.Execute
' Assign the result to the ComboBox
Set ComboTools.Recordset = objRs
End Sub

The question is how I can get the table through the function instead the
stored proc?

Thanks,

 

[Clifford Bass]

Hi,

Seems like you could just make your second combo box's Record Source
dependent on the first combo box. Here is an example of the Record Source
for a combo box named cbTypeSubCodes that is dependent on a combo box named
cbTypeCode, both on a form named Form6.

SELECT tblTypeSubCodes.TypeSubCode FROM tblTypeSubCodes WHERE
(((tblTypeSubCodes.TypeCode)=[Forms]![Form6]![cbTypeCode])) ORDER BY
tblTypeSubCodes.TypeSubCode;

You will also need an On Change event for the first combo box that
would look something like this:

Private Sub cbTypeCode_Change()

cbTypeSubCode.Requery
' Select first item in new list (use when null is not allowed in
TypeSubCode)
cbTypeSubCode.Value = cbTypeSubCode.Column(0, 0)

End Sub

Clifford Bass

 

[Clifford Bass]

Hi,

However, if you want to get the code below to work try this:

Private Sub ComboWorker_AfterUpdate()

Dim objCmd As New ADODB.Command
Dim objRs As ADODB.Recordset

Set objCmd.ActiveConnection = CurrentProject.Connection
' Set CommandText to retrieve data from the stored proc
objCmd.CommandText = "spWorkerTools"
objCmd.CommandType = adCmdStoredProc
' Execute the stored procedure
Set objRs = objCmd.Execute(, Array(ComboWorker.Value))
' Assign the result to the ComboBox
Set ComboTools.Recordset = objRs

End Sub

You may need to preface the stored procedure name by its owner.

Clifford Bass

 

[Robe]

Hi Clifford,

I've tried your idea that in fact is simpler than mine because I don't have
to implement a function in the server but the query builder doesn’t recognize
this “[Forms]![Form6]![cbTypeCode]†in the where clause. I did the same some
time ago in an Access database but it seems to fail in an Access project.

Can you do it in an Access project to see if it works with you?

Thanks for your time.

 

[Clifford Bass]

Hi Robe,

I don't use Access projects, so it was time to expand my knowledge. As
described, it did not work. However, I could get something similar to work.
I don't know if there is a better way. There probably is. You could try
posting in the ...access.adp.sqlserver discussion group. Use the On Change
event of the primary combo box to construct the row source for the
subordinate combo box. Maybe something like:

Private Sub cbTypeCode_Change()

cbTypeSubCode.RowSource = _
"select SomeColumn from SomeTable where KeyColumn = " & [cbTypeCode]
' Select first item in new list (use when null is not allowed in
TypeSubCode)
cbTypeSubCode.Value = cbTypeSubCode.Column(0, 0)

End Sub

Setting the row source seems to invoke a requery. Also, in your form's
On Current event, you would need to accomplish something similar. However,
you would need to get it to select the current value without making the
current row dirty. Otherwise you will invoke the saving of every record that
gets viewed. You might have the same issue if you set the subordinate combo
box's recordset. I am not sure how you would deal with that.

Again, maybe a good idea to repost in the other group and indicate you
are using an Access Data Project.

Good Luck,

Clifford Bass