Search through textbox (1 Viewer)

[azhar2006]

Hello guys .
You have created a form based on a query. And put a text box in it for the purpose of conducting a search in the query records. I used this code that my dear brother (arnelgp) helped me write, but the code does not work. I don't know where the problem is
Thank you very much

Private Sub TypeDown(strSearch As String)
On Error Resume Next
    
    Dim StrSQL As String
        
    If strSearch = "" Then
        StrSQL = "QurMastr"
    Else
        StrSQL = "SELECT * FROM QurMastr " & _
                 " WHERE FullName Like '" & strSearch & "*' " & " OR bookNumber Like '" & strSearch & "*' " _
                 & " OR Result Like '" & strSearch & "*' " & " OR Success Like '" & strSearch & "*' "
    End If
    Me.frmMastr.RecordSource = StrSQL
    Me.frmMastr.Requery
    Me.txtSearch.SetFocus
    
End Sub

Private Sub txtSearch_AfterUpdate()
    On Error Resume Next

    Me.Requery
    TypeDown IIf(IsNull(Me.txtSearch.Text), "", Me.txtSearch.Text)

End Sub

 

[GaP42]

Can you describe what happens when you enter text to search (strSearch)?
StrSQL - "QurMastr" is not a SQL statement. Is it a table or a Query itself?

 

[arnelgp]

upload your db so we can see where is wrong.

 

[azhar2006]

Can you describe what happens when you enter text to search (strSearch)?
StrSQL - "QurMastr" is not a SQL statement. Is it a table or a Query itself?

Thank you for replying
Nothing happens. Yes, it is a query based on a table

 

[GaP42]

Thank you for replying
Nothing happens. Yes, it is a query based on a table

So the first step is: change StrSQL = QurMastr to StrSQL = "SELECT * FROM QurMastr; "

As ArnelGP suggests - upload a copy of the db

 

[Gasman]

And put a debug.print strSQL into the code and look to see what is produced.

You know, simple debugging methods.

Also no need for a requery if you change the recordsource.

 

[azhar2006]

This is database file.

 

[cheekybuddha]

Private Sub TypeDown(strSearch As String)
On Error Resume Next
    
    Dim StrSQL As String
        
    If strSearch = "" Then
        StrSQL = "QurMastr"
    Else
        StrSQL = "SELECT * FROM QurMastr " & _
                 " WHERE FullName Like '" & strSearch & "*' " & " OR bookNumber Like '" & strSearch & "*' " _
                 & " OR Result Like '" & strSearch & "*' " & " OR Success Like '" & strSearch & "*' "
    End If
    Me.frmMastr.RecordSource = StrSQL
    Me.txtSearch.SetFocus
    
End Sub

Private Sub txtSearch_AfterUpdate()
    On Error Resume Next

    TypeDown Nz(Me.txtSearch, "")

End Sub

 

[MarkK]

This is how I would do that search...

Private Const SQL_SEARCH As String = _
    "SELECT * FROM tbLinvestigation " & _
    "WHERE FullName Like p0 " & _
        "OR bookNumber Like p0 " & _
        "OR Result Like p0 " & _
        "OR Success Like p0 "

Private qdf_ As DAO.QueryDef

Private Property Get qdfSearch() As DAO.QueryDef
    If qdf_ Is Nothing Then Set qdf_ = CurrentDb.CreateQueryDef("", SQL_SEARCH)
    Set qdfSearch = qdf_
End Property

Private Sub txtSearch_Change()
    With qdfSearch
        .Parameters(0) = "*" & Me.txtSearch.Text & "*"
        Set Me.Recordset = .OpenRecordset
    End With
End Sub

 

[azhar2006]

This is how I would do that search...

Private Const SQL_SEARCH As String = _
    "SELECT * FROM tbLinvestigation " & _
    "WHERE FullName Like p0 " & _
        "OR bookNumber Like p0 " & _
        "OR Result Like p0 " & _
        "OR Success Like p0 "

Private qdf_ As DAO.QueryDef

Private Property Get qdfSearch() As DAO.QueryDef
    If qdf_ Is Nothing Then Set qdf_ = CurrentDb.CreateQueryDef("", SQL_SEARCH)
    Set qdfSearch = qdf_
End Property

Private Sub txtSearch_Change()
    With qdfSearch
        .Parameters(0) = "*" & Me.txtSearch.Text & "*"
        Set Me.Recordset = .OpenRecordset
    End With
End Sub

thank you

MarkK dear​

It works fine, but why did you put the event in on Private Sub txtSearch_Change() ?

Private Sub txtSearch_Change()

    With qdfSearch

        .Parameters(0) = "*" & Me.txtSearch.Text & "*"

        Set Me.Recordset = .OpenRecordset

    End With

End Sub

 

[MarkK]

why did you put the event in on Private Sub txtSearch_Change()

Then it updates for every keystroke the user enters, but obviously, feel free to do whatever you want.