title: Record search in MS Access with recordset.findfirst throws error 3070
body: <p>I'm trying to build an MS Access database.</p>
  
  <p>One of the elements is an autofill in a record management form.</p>
  
  <p>Form layout:</p>
  
  <p><a href="https://i.stack.imgur.com/IjFRX.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/IjFRX.jpg" alt="The layout of the form is like this"></a> </p>
  
  <p>Note that the Battery ID &amp; Model Number is in one table (referred as Table 1), and the Chemistry Type, Spec Voltage, Spec Capacity is stored in another table based on Model Number (referred as Table 2). The two tables are joined in the relationship with a one-to-many relation at the Model Number entry.</p>
  
  <p>What I would like to achieve is to give the user the ability to enter a Battery ID, using <code>afterupdate()</code>, search Table 1, get the Model Number, if the Battery ID entry already exists. Then search Table 2, get the battery model details, if it exists.</p>
  
  <p><a href="https://i.stack.imgur.com/c1TlI.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/c1TlI.jpg" alt="To clarify the flow, here is the flowchart"></a></p>
  
  <p>The "Search Table 1 for matching Battery ID" part worked with the following code:</p>
  
  <pre class="lang-vb prettyprint-override"><code>Private Sub txtBatteryID_AfterUpdate()
      'Set the path to the Battery Records table
      Set RS = CurrentDb.OpenRecordset("Batteries for Portable Radios", dbOpenDynaset)
  
      'do a findfirst search for the Battery ID, using value from textbox txtBatteryID
      RS.FindFirst "[Battery ID]=" &amp; txtBatteryID
  
      'If no matching record, leave the other fields empty
      If RS.NoMatch Then
          cmbModelNumber.Value = Null
          cmbChemistryType.Value = Null
          txtSpecVoltage.Value = Null
          txtSpecCapacity.Value = Null
      'If there is a matching record, then, grab the model number
      Else
          cmbModelNumber.Value = RS("Model Number")
          'as there is an existing record with model number, run a search on the model number and grab the model info
          Call cmbModelNumber_AfterUpdate
      End If
      'close recordset
      RS.Close
      'clear recordset path
      Set RS = Nothing
  End Sub
  </code></pre>
  
  <p>For the "Search Table 2 for matching Model Number" part, I thought it would be the same structure:</p>
  
  <pre class="lang-vb prettyprint-override"><code>Private Sub cmbModelNumber_AfterUpdate()
      'Set the path to the Model Records table
      Set ModelRS = CurrentDb.OpenRecordset("tblInfoBatteryModelByModel#", dbOpenDynaset)
  
      'do a findfirst search for the Model Number, using value from combobox cmbModelNumber
      ModelRS.FindFirst "[Model Number]=" &amp; cmbModelNumber
  
      'If no matching record, leave the other fields empty
      If ModelRS.NoMatch Then
          cmbChemistryType.Value = Null
          txtSpecVoltage.Value = Null
          txtSpecCapacity.Value = Null
       'If there is a matching record, then, grab the Model Info
      Else
          cmbChemistryType.Value = ModelRS("Chemistry Type")
          txtSpecVoltage.Value = ModelRS("Spec Voltage (V)")
          txtSpecCapacity.Value = ModelRS("Spec Capacity (mAh)")
      End If
      'close recordset
      ModelRS.Close
      'clear recordset path
      Set ModelRS = Nothing
  
  End Sub
  </code></pre>
  
  <p>except this time when I type in an existing Battery ID or Model Number, it throws a 3070 error.</p>
  
  <p>Error message:</p>
  
  <p><a href="https://i.stack.imgur.com/yO5On.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/yO5On.jpg" alt="Error Message"></a></p>
  
  <p>Problematic line:</p>
  
  <p><a href="https://i.stack.imgur.com/3BiMR.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/3BiMR.jpg" alt="Problematic Line"></a>  </p>
  
  <p>I have no clue why this wouldn't take the value.</p>