How to find a value in excel spreadsheet then add to a access query

[Darren Smith]

Try to find a value in the column in excel (In this case, "Rear Door - NS - See Drawing") then use it in the access database query??
The error says "Command text was not set for the command object"

Private Sub SetIDLengthofRearDoorPoleHandelPostion()

   

    Dim iRow As Integer
    Dim qry As String
    Dim rs As Object: Set rs = OpenConAndGetRS(qry)

   
   

  With ThisWorkbook.Worksheets("Job Card Master")
  
  Select Case Me.Body_Type.Value
    Case ("Tippa with STD Cage")
    qry = "SELECT * FROM [IDAndData] " & _
    " WHERE [Vehicle]='" & Model_Type.Text & "'" & _
    " AND [FramesWidth&Height]='" & Gantry_Height_Width.Text & "'" & _
    " AND [Material/Part]='" & .Range("E1:F" & .UsedRange.Rows.Count).Find("Rear Door - NS - See Drawing", LookIn:=xlValues, lookat:=xlWhole).Value
    End Select
   
    iRow = .Range("E1:F" & .UsedRange.Rows.Count).Find("TC - TL - THTT", LookIn:=xlValues, lookat:=xlWhole).Row
      If Not (rs.BOF Or rs.EOF) Then
            Do While Not rs.EOF
                .Cells(iRow, 5) = rs.Fields("LengthofRearDoorPole&HandelPostion").Value
                iRow = iRow + 1
                rs.MoveNext
            Loop
        End If
        rs.Close: Set rs = Nothing
    End With

End Sub

 

[NoSparks]

If using qry prior to qry being constructed isn't the problem,
maybe try a variable = the range.find part and just use the variable in construction of the qry string.
(my 2010 doesn't accept what you have as a range for the .find)

 

[Darren Smith]

Thank you that`s sorted it