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

    Unanswered: Search Function help

    If anyone could point me in the right direction on this I would really appreciate it, I've been searching the net for days and have found only one website that shows me exactly what I want to do:

    MS Access: Create a form where you can search any text field in a table in Access 2003/XP/2000/97

    Naturally I would just download their example database, but it is unrecognized by Access 97.

    Can anyone help me on this? The link describes what I'd like to do exactly. I am just unsure on the VBA code, etc. to filter one form with the inputs on the second?

  2. #2
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    If you've got the second form setup already, and nothing you search for is ever going to require a ' or a ", you'd assign some vba to the submit button to build a new SQL statement to use as the recordset for the first form.

    Off the top of my head, it would look something like:

    Code:
    Private Sub searchButton_Click()
    
    Dim strSQL As String
    
    If ((Len(Me.selectFieldToSearchName.Value & vbNullString) > 0) Or _
        (Len(Me.enterSearchTextBoxName.Value & vbNullString) > 0)) Then
        strSQL = "SELECT * " & _
                 "FROM yourTableName " & _
                 "WHERE [" & Me.selectFieldToSearchName.Value & "] LIKE '*" & _
                  Me.enterSearchTextBoxName.Value & "*'"
    
        Forms("yourFirstFormName").RecordSource = strSQL
    Else
        DoCmd.Close
    End If
    
    End Sub
    Replacing:
    searchButton - With the name of your 'Search' button on the second form
    yourTableName - With the name of the table your referencing
    selectFieldToSearchName - With the name of the combo box on your search form
    enterSearchTextBoxName - With the name of the text box on your search form
    yourFirstFormName - With the name of the first form, the one with all the records displayed that you wish to narrow down

    You'll also have to ensure that the 'OnClick' event of the search button (found in its properties menu) is set to '[Event Precedure]', which should be the only available option anyway.


    Any probs, give us a shout... Although it's a bank holiday on Monday, and I don't usually check the site if I'm not at work.
    Last edited by kez1304; 08-26-11 at 11:58.
    Looking for the perfect beer...

  3. #3
    Join Date
    Aug 2011
    Posts
    3
    ^^Worked! Thanks.

    If the fields were blank and I wanted to prevent "Enter Parameter Value" from popping up, how would I go about that?

  4. #4
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    I edited the above post, if either of the search fields are blank, the form will close...

    Is that what you wanted to happen if it's not valid? If you just want it to do nothing, just delete:

    Code:
    Else
        DoCmd.Close
    From the code above, and it will be like the Search button does nothing.
    Looking for the perfect beer...

  5. #5
    Join Date
    Aug 2011
    Posts
    3
    That's Perfect Thanks

  6. #6
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    No worries mate.

    Happy to help.
    Looking for the perfect beer...

Posting Permissions

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