Showing how many results have been found in the message box.

[CarysW]

I have a search form which has a seperate search box. Once the search has been performed users get a Message box which informs them: 'Results have been filtered'. Is there a way I can inform users of how many results there are?

Code is below

Private Sub cmdOKSearch_Click()
   'Error handling
On Error GoTo cmdOK_Error

   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 frmSearchA based on search criteria
        Form_frmSearchA.RecordSource = "select * from qrySearchF where " & GCriteria
        Form_frmSearchA.Caption = "qrySearchF (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
        
        'Close frmSearch
        DoCmd.Close acForm, "frmSearchBox"
        
        MsgBox "Results have been filtered."
Exit Sub
   End If
'Error Handling
cmdOK_Error:
 If Err.Number = 2501 Then
        Resume Next
    Else
        MsgBox "Please record the following Error Number and Contact the Administrator", vbOKOnly, "Error Recording."
        MsgBox "Error " & Err.Number & ": " & Err.Description
        Resume Next
    End If
    
End Sub

 

[DCrake]

Before you set the recordsource if you do a

Cnt = Nz(DCount("*","qrySearchF",GCriteria),0)

MsgBox Cnt & " record(s) found",vbInformation+vbOkOnly,"Search Complete"

David

 

[CarysW]

Before you set the recordsource if you do a

Cnt = Nz(DCount("*","qrySearchF",GCriteria),0)

MsgBox Cnt & " record(s) found",vbInformation+vbOkOnly,"Search Complete"

David

Thankyou very much.

 

[namliam]

Use the form's recordsetclone and/or use
"select Count(*) from qrySearchF where " & GCriteria

if you prefer to do a seperate query.

the recordsetclone is the prefered method though, look it up in the access help.