title: ADO recordset datatype mismatch on MoveNext method
body: <p>I have an issue which I seem to be unable to solve after extensive Googling.  Since a week one of my functions written in VBA gives a "Run-time error '-2147467259 (80004005)': Data type mismatch in criteria expression." </p>
  
  <p>The line the error occurs is "rstBom.MoveNext".  I seem to lack the knowledge (I'm no schooled vba programmer) to understand how a skip in a recordset can cause a data type mismatch...</p>
  
  <pre><code>Public Function Packing_PL_BOM()
  
  Dim rst As New ADODB.Recordset
  Dim rstIN As New ADODB.Recordset
  Dim rstBom As New ADODB.Recordset
  Dim rstSC As New ADODB.Recordset
  
  CurrentProject.Connection.Execute "delete from tblPacking_private_label_base"
  
  rst.Open "qryPacking_Private_label_stock", CurrentProject.Connection, adOpenDynamic, adLockReadOnly
  rstIN.Open "tblPacking_private_label_base", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
  
  If Not rst.EOF Then
      Do Until rst.EOF
          rstBom.Open "select * from ZBOM_XLS where val(Component) = " &amp; rst.Fields("matnr").Value, CurrentProject.Connection, adOpenDynamic, adLockReadOnly
           If Not rstBom.EOF Then
              Do Until rstBom.EOF
                  rstIN.AddNew
                  rstIN.Fields("matnr").Value = rst.Fields("matnr").Value
                  rstIN.Fields("material_description").Value = rst.Fields("material_description").Value
                  rstIN.Fields("Value").Value = rst.Fields("Value").Value
                  rstIN.Fields("description").Value = rst.Fields("description").Value
                  rstIN.Fields("uom").Value = rst.Fields("uom").Value
                  rstIN.Fields("Stock_pack_PC").Value = rst.Fields("SumOfUnrestricted").Value
                  rstIN.Fields("Packed_matnr").Value = rstBom.Fields("Material").Value
                  rstIN.Fields("Packed_description").Value = rstBom.Fields("Material Description").Value
  
                  Select Case rstBom.Fields("Alternative BOM").Value
                      Case "1"
                          rstSC.Open "select * from qryMaterial_Class_SC_Values where matn = " &amp; rstBom.Fields("material").Value &amp; " and [Supply Chain] = 'PRODUCTION'", CurrentProject.Connection, adOpenDynamic, adLockReadOnly
                          If Not rstSC.EOF Then
                              rstIN.Fields("BOM_found").Value = "X"
  
                          Else
                              rstIN.Fields("BOM_found").Value = "DEL"
  
                          End If
                          rstSC.Close
  
                      Case "4"
                          rstSC.Open "select * from qryMaterial_Class_SC_Values where matn = " &amp; rstBom.Fields("material").Value &amp; " and [Supply Chain] = 'PINGUIN'", CurrentProject.Connection, adOpenDynamic, adLockReadOnly
                          If Not rstSC.EOF Then
                              rstIN.Fields("BOM_found").Value = "X"
                          Else
                              rstIN.Fields("BOM_found").Value = "DEL"
  
                          End If
                          rstSC.Close
  
                      Case "5"
                          rstSC.Open "select * from qryMaterial_Class_SC_Values where matn = " &amp; rstBom.Fields("material").Value &amp; " and [Supply Chain] = 'SIVAFROST'", CurrentProject.Connection, adOpenDynamic, adLockReadOnly
                          If Not rstSC.EOF Then
                              rstIN.Fields("BOM_found").Value = "X"
  
                          Else
                              rstIN.Fields("BOM_found").Value = "DEL"
  
                          End If
                          rstSC.Close
  
                      Case "6"
                           rstSC.Open "select * from qryMaterial_Class_SC_Values where matn = " &amp; rstBom.Fields("material").Value &amp; " and [Supply Chain] = 'DIRAMAR'", CurrentProject.Connection, adOpenDynamic, adLockReadOnly
                           If Not rstSC.EOF Then
                              rstIN.Fields("BOM_found").Value = "X"
  
                           Else
                              rstIN.Fields("BOM_found").Value = "DEL"
  
                           End If
                           rstSC.Close
  
                       Case "7"
                          'rstIN.Fields("BOM_found").Value = "COPACK"
                          rstSC.Open "select * from qryMaterial_Class_SC_Values where matn = " &amp; rstBom.Fields("material").Value &amp; " and [Supply Chain] = 'COPACK'", CurrentProject.Connection, adOpenDynamic, adLockReadOnly
                          If Not rstSC.EOF Then
                              rstIN.Fields("BOM_found").Value = "X"
                          Else
                              rstIN.Fields("BOM_found").Value = "DEL"
  
  
                          End If
                          rstSC.Close
  
                      Case "8"
                          'rstIN.Fields("BOM_found").Value = "YUBE"
                          rstSC.Open "select * from qryMaterial_Class_SC_Values where matn = " &amp; rstBom.Fields("material").Value &amp; " and [Supply Chain] = 'YUBE'", CurrentProject.Connection, adOpenDynamic, adLockReadOnly
                          If Not rstSC.EOF Then
                              rstIN.Fields("BOM_found").Value = "X"
                          Else
                              rstIN.Fields("BOM_found").Value = "DEL"
  
  
                          End If
                          rstSC.Close
  
                      Case "9"
                          'rstIN.Fields("BOM_found").Value = "POLAND"
                          rstSC.Open "select * from qryMaterial_Class_SC_Values where matn = " &amp; rstBom.Fields("material").Value &amp; " and [Supply Chain] = 'POLAND'", CurrentProject.Connection, adOpenDynamic, adLockReadOnly
                          If Not rstSC.EOF Then
                              rstIN.Fields("BOM_found").Value = "X"
                          Else
                              rstIN.Fields("BOM_found").Value = "DEL"
  
  
                          End If
                          rstSC.Close
  
                      Case Else
                          rstIN.Fields("BOM_found").Value = "DEL"
  
                  End Select
  
  
                  rstIN.Update
  
                  rstBom.MoveNext
  
              Loop
  
          End If 
          rstBom.Close
      rst.MoveNext
      Loop
  End If
  
  CurrentProject.Connection.Execute "delete from tblPacking_private_label_base where BOM_Found = 'DEL'"
  End function
  </code></pre>
  
  <p>The strange thing is it gives the error twice, I click debug, the third time it runs perfectly fine.</p>
  
  <p>Any help would be deeply appreciated!</p>