title: Search Access database with an Excel Userform and display results in Listbox using VBA
body: <p>I have a userform in Excel I intend to use as a search form and has a listbox to display the result.</p>
  <p>The form will search for an Account number from a table in my Access database.<br />
  Aside from a Search field (TextBox), it has a search button (CommandButton) and a Listbox.</p>
  <p>My goals:</p>
  <ol>
  <li>Connect to my Access database from Excel</li>
  <li>Validate the search field.<br />
  If the field is empty, display message to enter an account number.<br />
  If the entry is not among those found in the table, return a message that the Account Number does not exist.<br />
  If account number in the table, display the results in the Listbox.</li>
  </ol>
  <p>I am trying to create an Excel Worksheet to place the results of my query.</p>
  <ol>
  <li>AcctNo is one of the headers from my MemberAccts Table in Access.</li>
  <li>acctNoField is the name of the search field in my search form.</li>
  </ol>
  <p>The error is</p>
  <blockquote>
  <p>&quot;No value given for one or more parameters&quot;.</p>
  </blockquote>
  <p>When debugging, <code>rst.Open qry, cnn, adOpenKeyset, adLockOptimistic</code> is highlighted.</p>
  <p>Which parameter am I am?</p>
  <pre class="lang-vb prettyprint-override"><code>Private Sub acctSearchBtn_Click()
      
      Dim sh As Worksheet
      Set sh = ThisWorkbook.Sheets(&quot;AcctInfo&quot;)
      sh.Cells.ClearContents
      
      Dim cnn As New ADODB.Connection
      Dim rst As New ADODB.Recordset
          
      Dim qry As String, 
      Dim AcctNo As String
       
      If Me.acctNoField.Value = &quot;&quot; Then
          MsgBox &quot;Please enter an Account Number&quot;, vbCritical
          Exit Sub
          
      ElseIf Me.acctNoField.Value &lt;&gt; qry Then
              
          cnn.Open &quot;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\MasterDb.accdb&quot;
          qry = &quot;SELECT * FROM MembrAccts WHERE Me.acctNoField.Value= '&quot; &amp; AcctNo &amp; &quot;'&quot;
  
          rst.Open qry, cnn, adOpenKeyset, adLockOptimistic
      
          MsgBox &quot;The Account Number does not exists&quot;, vbCritical
          Exit Sub
          
      Else
          MsgBox &quot;Place Retrieve_account function here&quot;, vbCritical
                                             
      End If
      
  rst.Close
  cnn.Close
      
  End Sub
  </code></pre>