Set two conditions for Find Method in ADO

Question

I have an ADO recordset and want to retrieve some matching fields in my form. I should set two criteria for the recordset and I use the AND keyword with FOR method, I tried a lot of formats but I get error when I run it.
The code is as follow:

Private Sub cmdDetailControl_Click()
'Create the necessary recordset and connections
Dim cn As ADODB.Connection
Dim rsInventoryControl As ADODB.Recordset
Dim rsInventoryAssigned As ADODB.Recordset
Dim strSQLInventory As String
Dim strSQLAssigned As String

    'set the connection and recordsets  
    Set cn = New ADODB.Connection  
    Set rsInventoryControl = New ADODB.Recordset  
    Set rsInventoryAssigned = New ADODB.Recordset  
      
    'Create and build recordsets  
    strSQLInventory = "SELECT tblInventory.InventoryID, tblInventoryCategory.CategoryName, tblInventory.InventoryName, tblInventory.InventoryUnit, Sum(tblInventoryTransactionDetail.QtyAdded) AS SumOfQtyAdded, Sum(tblInventoryTransactionDetail.QtyDeducted) AS SumOfQtyDeducted, tblInventoryTransaction.StockID, [SumOfQtyAdded]-[SumOfQtyDeducted] AS Balance " & _  
    "FROM tblInventoryTransaction INNER JOIN ((tblInventory INNER JOIN tblInventoryTransactionDetail ON tblInventory.InventoryID = tblInventoryTransactionDetail.InventoryID) INNER JOIN tblInventoryCategory ON tblInventory.InventoryCategoryID = tblInventoryCategory.InventoryCategoryID) ON tblInventoryTransaction.InventoryTransactionID = tblInventoryTransactionDetail.InventoryTransactionID " & _  
    "GROUP BY tblInventory.InventoryID, tblInventoryCategory.CategoryName, tblInventory.InventoryName, tblInventory.InventoryUnit, tblInventoryTransaction.StockID;"  
    With rsInventoryControl  
    .CursorType = adOpenForwardOnly  
    'open the recordset based on SQL statement using the existing connection  
    .Open strSQLInventory, CurrentProject.Connection  
    End With  
      
    'find the record based on the information in form and subform  
    rsInventoryControl.Find "[InventoryID]= " & Me!frmInventoryPermissionDetailSubform.Form!cboInventoryID & " And [StockID] = " & Me.StockID  
      
     If rsInventoryControl.EOF Then  
        MsgBox "I don't find a record with this Specification", vbOKOnly + vbExclamation, "No Record"  
    Else  
        MsgBox "Paper Inventory: " & rsInventoryControl!Balance & vbCrLf & _  
        " Paper code: " & rsInventoryControl!InventoryID & vbCrLf & _  
        " Stock ID: " & rsInventoryControl!StockID  
    End If  
      
      
      
End Sub  

Answer 1

Okay, I could be wrong since I don't use ADO, but according to this article:

find-method-ado

It says:

"Only a single-column name may be specified in criteria. This method does not support multi-column searches."

So, as a troubleshooting step, try searching for one column only just to see if the error goes away.

Hope that helps...

Answer 2

Hi. Which line is highlighted when you go to debug?

Answer 3

Dear thedbguy,

Line 17.