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,418
    Provided Answers: 7
    You need a end if somewhere
    hope this help

    See clear as mud


    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 based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  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 07: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
  •