Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2011
    Posts
    4

    Question Unanswered: Tweaking my search facility

    Hi all,

    I am using a search facility in my database (which I got from this website somewhere!)

    In “frm_contacts” form, a button opens the “frm_search” form.

    On this form there is a combo box (cboSearchField) and a keyword search box (txtSearchString). There is a button (cmdSearch) that has the following OnEvent 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 frm_Contacts based on search criteria
    Form_frm_contacts.RecordSource = "select * from Contacts where " & GCriteria
    Form_frm_contacts.Caption = "Contacts (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"

    'Close frmSearch
    DoCmd.Close acForm, "frm_Search"

    MsgBox "Results have been filtered."

    End If

    What I need to do is:

    If there are no records found, a return to the frm_search form so that other data can be searched for.

    I also need to make a standalone version of the form that is reached from the Switchboard. This is so I can search without going via the frm_contacts form.

    When I run the code as it is, it just disappears and no form opens.

    All help is appreciated.

    Thanks

    Leo

  2. #2
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    If there are no records found, a return to the frm_search form so that other data can be searched for.
    Replace:

    Code:
    'Close frmSearch
    DoCmd.Close acForm, "frm_Search"
    
    MsgBox "Results have been filtered."
    With:

    Code:
    If (RecordsetClone.RecordCount > 0) Then
    
        'Close frmSearch
        DoCmd.Close acForm, "frm_Search"
    
        MsgBox "Results have been filtered."
    
    Else
    
        DoCmd.Close acForm, "frm_Search"
        DoCmd.OpenForm ("frm_Search")
    
    End If

    Let me know if that's what you're after...
    Looking for the perfect beer...

  3. #3
    Join Date
    Aug 2011
    Posts
    4

    Question

    Thanks for your quick reply.

    When I run this, I receive the error message:

    Compile error: Block if without end if.

    Any ideas?

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,469
    Provided Answers: 10
    You need a end if somewhere
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  5. #5
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Sounds to me that you replaced too much...

    I said replace:

    Code:
    'Close frmSearch
    DoCmd.Close acForm, "frm_Search"
    
    MsgBox "Results have been filtered."
    I assume you replaced:

    Code:
    'Close frmSearch
    DoCmd.Close acForm, "frm_Search"
    
    MsgBox "Results have been filtered."
    
    End If
    The code I supplied should work fine, just make sure it looks like:

    Code:
    If (RecordsetClone.RecordCount > 0) Then
    
        'Close frmSearch
        DoCmd.Close acForm, "frm_Search"
    
        MsgBox "Results have been filtered."
    
    Else
    
        DoCmd.Close acForm, "frm_Search"
        DoCmd.OpenForm ("frm_Search")
    
    End If
    
    End If
    Looking for the perfect beer...

  6. #6
    Join Date
    Aug 2011
    Posts
    4

    Question

    Thanks for your reply

    I then received the Error 7591 message but I corrected it by editing the control source for the frm_search.

    However, when there are no results, frm_search still closes and returns to frm_contacts (with no information in it) and displays the "results have been filtered" dialog.
    Last edited by lsmcal1984; 08-26-11 at 06:12.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •