通过传递参数访问呼叫存储过程

我正在使用链接到SQL Server 2012数据库后端的Access 2010前端。

在Access前端中，我有一个常规的搜索屏幕，该屏幕由一个文本框组成，结果将显示在列表框中。我希望文本框能够搜索多个字段，这些字段也必须是通配符。

所以，我的存储过程是这样的：

ALTER PROCEDURE [dbo].[SalesGeneralSearch]
   @Search nvarchar(50) = ''
AS
BEGIN
   SET NOCOUNT ON;

   SELECT 
      tblJobHead.JobNum, 
      tblCustomer.LastName, 
      tblCustomer.M3DealerCode, 
      tblCustomer.TradeRef, 
      tblCustomer.Postcode, 
      tblJobHead.[Item Number], 
      tblJobHead.Description, 
      tblStatus.[Desc] AS Status
   FROM 
      tblCustomer 
   INNER JOIN 
      (tblJobHead 
   INNER JOIN 
      tblStatus ON tblJobHead.Status = tblStatus.StatusID) ON tblCustomer.RepNum = tblJobHead.[Rep Num]
   WHERE 
      (((tblJobHead.JobNum) Like '%'+ @Search + '%')) 
       OR (((tblCustomer.LastName) Like '%' + @Search + '%')) 
       OR (((tblCustomer.M3DealerCode) Like '%' + @Search + '%')) 
       OR (((tblCustomer.TradeRef) Like '%' + @Search + '%')) 
       OR (((tblCustomer.Postcode) Like '%' + @Search + '%'))
   ORDER BY 
      tblJobHead.JobNum DESC;
END

好的，现在回到Access。在搜索屏幕上，我有一个供用户输入的文本框，一个单击以进行搜索的按钮以及一个结果列表框。在按钮的click事件上，我有以下代码：

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim adString As Variant


Set cn = New ADODB.Connection

cn.ConnectionString = "DRIVER=SQL Server;SERVER=XXX;Database=XXX;Trusted_Connection=YES;"
cn.Open

    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = cn
        .CommandText = "dbo.SalesGeneralSearch"
        .CommandType = adCmdStoredProc
        Set prm = .CreateParameter("@Search", adString, adParamInput)
        .Parameters.Append prm
        cmd.Execute
        prm.Value = Me.Search.Text
    End With

    Set rs = New ADODB.Recordset
    With rs
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockReadOnly
        .Open cmd
    End With
    Set Me!lstJobQuickSearch.Recordset = rs
    Me.lstJobQuickSearch.Requery
Set prm = Nothing
Set cmd = Nothing

但是，当我单击搜索按钮时，我继续收到以下错误：

运行时错误3708参数对象定义不正确

它突出了

.Parameters.Append prm

lstJobQuickSearch的行源也是使用以下内容的传递查询：

SELECT tblJobHead.JobNum, tblCustomer.LastName, tblCustomer.M3DealerCode, tblCustomer.TradeRef, tblCustomer.Postcode, tblJobHead.[Item Number], tblJobHead.Description, tblJobHead.FN, tblStatus.[Desc] AS Status
FROM tblCustomer INNER JOIN (tblJobHead INNER JOIN tblStatus ON tblJobHead.Status = tblStatus.StatusID) ON tblCustomer.RepNum = tblJobHead.[Rep Num]
ORDER BY tblJobHead.JobNum DESC;

我哪里出问题了？任何帮助将不胜感激。

正如我只想返回值一样，仅使用传递查询并对参数进行编码会更有效吗？我不确定，我已经为此工作了好几天：-（

问候，

麦可