How to filter subform in datasheet view by textbox? #likeoperator #whereclause

Question

I try to filter a subform in datasheet view depending on an optionframe by a textbox on the main form when pushing the search button. I tried 2 methods so far:

1) altering the select query the subform is based on using a like operator in the where clause -> does not work (problem with where clause)

2) changing the filter on the subform filter event by using the like operator -> does not work (Error: "type mismatch"; commented out in the following code)

I'm thankful for any hint/advice!

here is my code of the search button on-click event:

Private Sub btnSearch_Click()
Dim strSubQry, strFilter, SearchField As String
On Error GoTo Err_btnSearch_Click
SearchField = Me.txtSearchBox
If IsNull(Me.txtSearchBox) Or Me.txtSearchBox = "" Then
  MsgBox "Please enter keyword before searching!", vbOKOnly
  Me.txtSearchBox.SetFocus
Else
  Select Case Me.FrameSearchOptions
   Case Is = 1 'Order No Intern
    strSubQry = " SELECT tblShipment.ShipID, tblOrder.OrderNoIntern, tblOrder.CustomerName, tblOrder.CustomerPO, tblOrder.CustomerPN, tblShipment.ShipQTY, " & _
    " FROM tblOrder INNER JOIN tblShipment " & _
    " ON tblOrder.OrderID = tblShipment.OrderID_FK " & _
    " WHERE  ((tblOrder.[OrderNoIntern] Like " & SearchField & "*""));"

    ' strFilter = "[OrderNoIntern]" Like " & Chr(34) & SearchField & " * " & Chr(34)"

   Case Is = 2 'Customer Name
    strSubQry = " SELECT tblShipment.ShipID, tblOrder.OrderNoIntern, tblOrder.CustomerName, tblOrder.CustomerPO, tblOrder.CustomerPN, tblShipment.ShipQTY, " & _
    " FROM tblOrder INNER JOIN tblShipment " & _
    " ON tblOrder.OrderID = tblShipment.OrderID_FK " & _
    " WHERE  ((tblOrder.[CustomerName] Like " & SearchField & "*""));"

    ' strFilter = "[CustomerName]" Like "& Chr(34) & SearchField & " * " & Chr(34)"

   Case Is = 3 'Customer PO
    strSubQry = " SELECT tblShipment.ShipID, tblOrder.OrderNoIntern, tblOrder.CustomerName, tblOrder.CustomerPO, tblOrder.CustomerPN, tblShipment.ShipQTY, " & _
    " FROM tblOrder INNER JOIN tblShipment " & _
    " ON tblOrder.OrderID = tblShipment.OrderID_FK " & _
    " WHERE  ((tblOrder.[CustomerPO] Like " & SearchField & "*""));"

    ' strFilter = "[CustomerPO]" Like " & Chr(34) & SearchField & " * " & Chr(34)"

    Case Is = 4 'Customer P/N
     strSubQry = " SELECT tblShipment.ShipID, tblOrder.OrderNoIntern, tblOrder.CustomerName, tblOrder.CustomerPO, tblOrder.CustomerPN, tblShipment.ShipQTY, " & _
     " FROM tblOrder INNER JOIN tblShipment " & _
     " ON tblOrder.OrderID = tblShipment.OrderID_FK " & _
     " WHERE  ((tblOrder.[CustomerPN] Like " & SearchField & "*""));"

     ' strFilter = "[CustomerPN]" Like " & Chr(34) & SearchField & " * " & Chr(34)"

 End Select
End If
Me.sfrmShipmentsDS.Form.RecordSource = strSubQry
Me.sfrmShipmentsDS.Requery

'Me.sfrmShipmentsDS.Form.Filter = strFilter
'Me.sfrmShipmentsDS.Form.FilterOn = True
Exit_btnSearch_Click:
    Exit Sub
Err_btnSearch_Click:
    MsgBox Error$
    Resume Exit_btnSearch_Click
End Sub

Minty Minty · Accepted Answer · 2017-07-25T10:33:25

You are searching for string values but not escaping them correctly so this line

   " WHERE  ((tblOrder.[OrderNoIntern] Like " & SearchField & "*""));"

Should be

   " WHERE  tblOrder.[OrderNoIntern] Like '" & SearchField & "*';"

You can lose the brackets unless you are grouping multiple AND and OR where clauses which you aren't. Your commented out lines are almost right but again not constructed quite correctly.

How to filter subform in datasheet view by textbox? #likeoperator #whereclause

1 Answers