Search Sting & Message Box replies

[huv123]

I have a code for a free text search form which will return records in another window when the search button is clicked.

Private Sub cmdSearch_Click()

    If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
        MsgBox "You must select a field to search."
        
    ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
        MsgBox "You must enter a search string."
        
    Else
        'Generate search criteria
        GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
        
        'Filter frmRhinitis based on search criteria
        Form_frmRhinitis.RecordSource = "select * from tblBaseline where " & GCriteria
        Form_frmRhinitis.Caption = "Customers (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
        
        MsgBox "Results have been filtered."
     
        
        End If
    
End Sub

However, even if no records match the criteria the window will return a "Results have been filtered" but return a blank form. How do I include code that will return a "Match cannot be found" MsgBox if the search string isnt found?


Thanks

 

[Remou]

Hi
Maybe:

If Me.Recordset.BOF Then
  MsgBox "No records"
Else
  MsgBox "Results have been filtered."
End If

 

[huv123]

How do I integrate it to this part of the code?

Private Sub cmdSearch_Click()

    If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
        MsgBox "You must select a field to search."
        
    ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
        MsgBox "You must enter a search string."
        
    Else
        'Generate search criteria
        GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
        
        'Filter frmRhinitis based on search criteria
        Form_frmRhinitis.RecordSource = "select * from tblBaseline where " & GCriteria
        Form_frmRhinitis.Caption = "Customers (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"

I cant seem to get the If and Else placements right..?

 

[Remou]

Put the whole snippet in instead of your line:
[tt]MsgBox "Results have been filtered."[/tt]

 

[huv123]

Yeah It doesnt work.. Im getting an error message: Object variable or With block variable not set. Hmm? Any ideas?

 

[Remou]

Nope. Can you post all of your code as it stands at present and indicate the line that is causing the problem?

 

[huv123]

that is all the code that relates to the search form specifically..

If I include your code:

Private Sub cmdSearch_Click()

    If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
        MsgBox "You must select a field to search."
        
    ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
        MsgBox "You must enter a search string."
        
    Else
    
        'Generate search criteria
        GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
        
        'Filter frmRhinitis based on search criteria
        Form_frmRhinitis.RecordSource = "select * from tblBaseline where " & GCriteria
        Form_frmRhinitis.Caption = "Customers (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
        
     If Me.Recordset.BOF Then
        MsgBox "No records"

Else
   
   MsgBox "Results have been filtered."

End If
    
End Sub

WHen I debug/compile, the end sub is highlighted and the error message "block if without end if" appears.

 

[Remou]

You are indeed missing an End If:

Private Sub cmdSearch_Click()

    If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
        MsgBox "You must select a field to search."
        
    ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
        MsgBox "You must enter a search string."
        
    Else
    
        'Generate search criteria
        GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
        
        'Filter frmRhinitis based on search criteria
        Form_frmRhinitis.RecordSource = "select * from tblBaseline where " & GCriteria
        Form_frmRhinitis.Caption = "Customers (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
        
     If Me.Recordset.BOF Then
        MsgBox "No records"
     Else
        MsgBox "Results have been filtered."
     [red]End If[/red]

End If
    
End Sub

 

[huv123]

I get an error message now that says "Object variable or With block variable not set" Boy! You have to be a patient person to be in this business...

 

[Remou]

So what line is it whinging about?

I can't seem to recreate the problem ...

I just read your post again and this line:
If Me.Recordset.BOF Then
Should read
If Forms!frmRhinitis.Recordset.BOF Then
But I don't think that will help.

 

[TheAceMan1]

huv123 . . .

Perhaps this . . . The code requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK.

[blue]Private Sub cmdSearch_Click()
   Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
   
   If Trim(cboSearchField & "") = "" Then
       MsgBox "You must select a field to search."
   ElseIf Trim(txtSearchString & "") = "" Then
       MsgBox "You must enter a search string."
   Else
      'Generate SQL
      Set db = CurrentDb
      SQL = "SELECT * FROM tblBaseline " & _
            "WHERE " & cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
      Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
      
      If rst.BOF Then
         MsgBox "Match cannot be found!"
      Else
         'Filter frmRhinitis based on SQL
         Form_frmRhinitis.RecordSource = SQL
         Form_frmRhinitis.Caption = "Customers (" & cboSearchField.Value & _
                                    " contains '*" & txtSearchString & "*')"
         MsgBox "Results have been filtered."
      End If
      
      Set rst = Nothing
      Set db = Nothing
   End If
   
   Call enShowAll
    
End Sub[/blue]

See Ya! . . . . . .

 

[huv123]

You woulsnt believe it but it works - I think I would marry you both if i believed in polygamy

 

[patrichek]

Hi,
Can someone explain to me how to get this code to work in a db, I need a search form to search contacts for my users. I'm a novice and this is my first workings with code.
any help is appreciated!

 

[Remou]

Hi patrichek
Might be better to start your own thread. But for starters, create a form, put a textbox called cboSearchField on it, attach a table (tblBaseline in the above example), add a command button, cmdSearch, and a click event for the button, paste in the code above. Save the form (frmRhinitis in the above example). Have fun.

 

[patrichek]

Hi,
thanks for responding!
ok, how to attach a table? and what is cmdSearch?
P

 

[Remou]

Hi
Please start another thread. You can reference this one, but it is not quite fair to tack on.

 

[TheAceMan1]

patrichek . . . . .

I agree with [blue]Remou[/blue], but for a different reason . . . . . [purple]you miss out on maximim exposure to the forum![/purple]

See Ya! . . . . . .