Results 1 to 3 of 3
  1. #1
    RLehrbass is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2015
    Posts
    102

    Not sure how to word the code line i need to show msgBox if no match is found.

    This is from my search button.
    I need to add a line of code that will bring up a msgbox saying "No records match the search criteria" if no match is found in the record set. then close frmSearch.
    I not quite sure how to word it though???
    My frmSearch has 2 selections: cboSearchField and txtSearchString and a search button.
    Here is my existing 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 Members based on search criteria
    Form_frmMember.RecordSource = "select * from tblMember where " & GCriteria
    Form_frmMember.Caption = "Members (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"

    'Close frmSearch
    DoCmd.Close acForm, "frmSearch"

    MsgBox "Results have been filtered."

    End If

    End Sub

  2. #2
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,845
    try

    Code:
    Private Sub cmdSearch_Click()
    
        If nz(cboSearchField,"")="" Then
            MsgBox "You must select a field to search."
    
        ElseIf nz(txtSearchString,"") = "" Then
            MsgBox "You must enter a search string."
        
        Else
    
            'Generate search criteria
            GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
    
            if dcount("*","tblMember",GCriteria)=0 then
    
                msgbox "No records match the search criteria"
    
           else
    
                'Filter Members based on search criteria
                Form_frmMember.RecordSource = "select * from tblMember where " & GCriteria
                Form_frmMember.Caption = "Members (" & cboSearchField & " contains '*" & txtSearchString & "*')"
    
                'Close frmSearch
                DoCmd.Close acForm, "frmSearch"
    
                MsgBox "Results have been filtered."
    
            End if
        End If
    
     End Sub

  3. #3
    RLehrbass is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2015
    Posts
    102
    Works Great.
    Thank You

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 07-27-2015, 06:53 AM
  2. Replies: 4
    Last Post: 11-20-2014, 01:05 PM
  3. Replies: 8
    Last Post: 04-14-2013, 01:33 PM
  4. Replies: 8
    Last Post: 11-04-2011, 06:52 AM
  5. MsgBox for no records found
    By Lxmanager in forum Forms
    Replies: 4
    Last Post: 11-02-2010, 12:31 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums