Search Button is not working

[Sunnylei]

Hi
I created a search command button. But it's not working for me

Private Sub cmSearch_Click()
    Dim qdf As DAO.querydef
    Dim LSQL  As String
    Dim LSearchString As String
    If Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
        MsgBox "You must enter a search string."
    Else
    Set qdf = CurrentDb.QueryDefs("querySearch")
        LSearchString = txtSearchString
        'Filter results based on search string
        LSQL = "select * from Products"
        LSQL = LSQL & " where Descriptions LIKE '*" & LSearchString & "*'"
      Debug.Print LSQL
 
 
      qdf.sql = LSQL
            If DCount("*", "querysearch", "Descriptions Like '*" & LSearchString & "*'") > 0 Then
                   DoCmd.OpenQuery "querySearch"
            Else
                MsgBox "There are no records with Search string " & vbCrLf & vbTab & "--->" & LSearchString & "<---"
            End If
        'Clear search string
        'txtSearchString = ""
        'MsgBox "Results have been filtered.  All Descriptions containing " & LSearchString & "."
         End If
       End Sub
 
End Sub

Can anyone help?

 

[vbaInet]

Are you going to explain what the code does and what you're trying to do or you want us to read your code?

 

[Sunnylei]

Are you going to explain what the code does and what you're trying to do or you want us to read your code?

I'm trying to search product by any words in Descriptions field. I use SQL create a query. However, the code seems to missing something. So it's not working for me. Can you have a look what's wrong with it. Thanks

 

[vbaInet]

Sunnylei, search the forum for Search Form if you want to learn more.

Private Sub cmSearch_Click()
    Dim strSearch As String
    
    If Len(txtSearchString & vbNullString) = 0 Then
        MsgBox "You must enter a search string."
        Exit Sub
    End If
    
    strSearch = Me.txtSearchString
    
    Me.Filter = "Descriptions LIKE " & Chr(34) & "*" & LSearchString & "*" & Chr(34)
    Me.FilterOn = True
    
    If Me.RecordSource.RecordCount = 0 Then
        MsgBox "There are no records with Search string " & vbCrLf & vbTab & "--->" & strSearch & "<---"
    End If
    
    Me.txtSearchString = vbNullString
End Sub

 

[Sunnylei]

Sunnylei, search the forum for Search Form if you want to learn more.

Private Sub cmSearch_Click()
    Dim strSearch As String
 
    If Len(txtSearchString & vbNullString) = 0 Then
        MsgBox "You must enter a search string."
        Exit Sub
    End If
 
    strSearch = Me.txtSearchString
 
    Me.Filter = "Descriptions LIKE " & Chr(34) & "*" & LSearchString & "*" & Chr(34)
    Me.FilterOn = True
 
    If Me.RecordSource.RecordCount = 0 Then
        MsgBox "There are no records with Search string " & vbCrLf & vbTab & "--->" & strSearch & "<---"
    End If
 
    Me.txtSearchString = vbNullString
End Sub

Hi
I have tried your code, however, I got message from system for

 If Me.RecordSource.RecordCount = 0 Then

"Compile error, Invalid qualifier"
If I need to define 'RecordSource.Record Count'?

Thanks

 

[vbaInet]

Me.Recordset.RecordCount

 

[Sunnylei]

Me.Recordset.RecordCount

Thanks it works now!

 

[vbaInet]

You're welcome!

 

[Sunnylei]

You're welcome!

Sorry I still have question ie how to make Recordset updatable, since I added in the code in search, then there is a problem incurred ie I have a Combox, which I set it as Category List. When I change item, there is a message box pop up: 'This Recordset is not updable' (please see attached database).

 

[vbaInet]

* What does the combo box do? Do you use it for searching or for displaying records?
* Can you edit the records in the query directly?

 

[Sunnylei]

* What does the combo box do? Do you use it for searching or for displaying records?
* Can you edit the records in the query directly?

I use it for displaying records. I don't query for it, but I can edit it in table. However, when I click on dropdown arrow, the message shows me it's not updatable.

 

[vbaInet]

* Check the Recordset Type property of the form and make sure it's set to Dynaset.
* Ensure the Allow Edits property of the form is set to Yes.

 

[Sunnylei]

* Check the Recordset Type property of the form and make sure it's set to Dynaset.
* Ensure the Allow Edits property of the form is set to Yes.

I checked. It's caused by Combo-box wizard wasn't running
properly. Now I have solved the problem.

Thanks

 

[vbaInet]

Good to know!