using a store proc as a record source

[grmman]

I am using access 2000 and this is an ADP project.
Does anyone see anything wrong with the function.
the first if works find, its finds the store proc and runs it.
When I get into the case section I get error 2580 it cant find the store proc.
I can see all 3 proc in the store proc tab in.
I can run all three proc from sql anlay with nop problems.

thanks for any help
G

Private Sub cmd_search_Click()
 Dim strSql As String
  'MsgBox Me.Fram_search.Value
  'MsgBox Me.txt_search.Value
 
' this one works great 
If IsNull(Me.txt_search.Value) Then
    Me.subfrmdata.Visible = True
    Me.subfrmdata.Form.RecordSource = "EXEC dbo.PROCGETALL"
Else
    Select Case Me.Fram_search.Value
    Case 1
' this one it cant find.

        Me.subfrmdata.Visible = True
        Me.subfrmdata.Form.RecordSource = "EXEC dbo.getinv"
        Me.subfrmdata.Form.InputParameters = "@invno=[forms]![frmsearch]![txt_search].Value"
    Case 2
        Me.subfrmdata.Visible = True
        Me.subfrmdata.Form.RecordSource = "EXEC dbo.procgetmatterno"
        Me.subfrmdata.Form.InputParameters = "@matterno=[forms]![frmsearch]![txt_search].Value"
    End Select
End If
End Sub

 

[cmmrfrds]

Do you have execute permissions on dbo.getinv

 

[grmman]

yes I check that .

 

[grmman]

They all have the same permissions set.
the first proc works but the other 2 DO NOT.

 

[grmman]

I go it to work now
this is how I had to do it

 Me.subfrmdata.Visible = True
       Me.subfrmdata.Form.RecordSource = "EXEC dbo.procgetinvno" & "'" & [Forms]![frmsearch]![txt_search].Value & "'"

thanks for the help

 

[fearnan]

I think all you where missing from the original code was a ?

when you specify the recordsource for the stored procs with parameters, u need to replace the parameters bit with ?

so for example

your code:

Me.subfrmdata.Form.RecordSource = "EXEC dbo.getinv"
Me.subfrmdata.Form.InputParameters = "@invno=[forms]![frmsearch]![txt_search].Value"

becomes

Me.subfrmdata.Form.RecordSource = "EXEC dbo.getinv ?"
Me.subfrmdata.Form.InputParameters = "@invno=[forms]![frmsearch]![txt_search].Value"

if the stored proc required two parameters, then you would use two ? holders.

I've not checked it meself but I reckon that could be the prob.

;-)