title: Access Call Stored Procedure with Pass Through Parameter
body: <p>I'm using a Access 2010 front-end linked to a SQL Server 2012 database back-end.</p>
  
  <p>In the Access frontend I have a general search screen, consisting of a text box whereby the results are displayed in a listbox.  I want the text box to be able to search multiple fields that will also need to be wild cards.</p>
  
  <p>So, my stored procedure is this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Ok, so now back to Access.  On the search screen, I have textbox where the user inputs, a button to click to search and a listbox for the results.  On the click event of the button I have the following code:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>However, when I click on the search button I keep receiving the below error:</p>
  
  <p>runtime error 3708 parameter object improperly defined</p>
  
  <p>and it highlights </p>
  
  <pre><code>.Parameters.Append prm
  </code></pre>
  
  <p>The row source of the lstJobQuickSearch is a pass through query as well using the following:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Where have I gone wrong?  Any help would be greatly appreciated.</p>
  
  <p>As I just want to return the values, is it more efficient just to use a pass through query and code the parameters?  I'm not sure, I've been working on this for days : -( </p>
  
  <p>Regards,</p>
  
  <p>Michael</p>