Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Search Function (Acess 2000)

    How can you create a search function on a form? What I want is the user will enter in a value, which then fires a queryDef and then populate the unbound text boxes. The result will always be only one record.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search Function (Acess 2000)

    Is there a compelling reason you want to do this with unbound text boxes? It's very easy using a bound form; for instance, one of the options offered by the Combo Box wizard is to find a record based on the value entered/selected in the combo box.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search Function (Acess 2000)

    I could use bound boxes, no problem. Since there are 1000's of records, a drop down box would not be efficent.

  4. #4
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search Function (Acess 2000)

    Furthermore, the value they are searching for is not the PK.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search Function (Acess 2000)

    One idea would be to use two or three combo boxes to limit the choices in a few steps.

    But is is also possible to use a text box to search. Here is some code using DAO; it needs a reference to the Microsoft DAO 3.6 Object Library (in the Visual Basic Editor, select Tools/References... to set a reference). The example uses a text box txtSearchName; as the user types in it, the OnChange event searches for the first record in which the LastName field starts with the text entered in the text box. It can easily be adapted to search for an exact match, or to search for a numeric field.

    Private Sub txtSearchName_Change()
    Dim rst As DAO.Recordset
    Dim strSearch As String
    On Error GoTo Err_Sub
    If [txtSearchName].Text = "" Then
    strSearch = "[LastName] is Null"
    Else
    strSearch = "[LastName] Like '" & [txtSearchName].Text & "*'"
    End If
    Set rst = Me.RecordsetClone
    rst.FindFirst strSearch
    If rst.NoMatch Then
    Beep
    Else
    If Me.Dirty Then RunCommand acCmdSaveRecord
    Me.Bookmark = rst.Bookmark
    [txtSearchName].SelStart = Len([txtSearchName].Text)
    End If
    Exit_Sub:
    Set rst = Nothing
    Exit Sub
    Err_Sub:
    MsgBox Err.Description, vbExclamation
    Resume Exit_Sub
    End Sub

    Note the use of quotes in the search string:<pre>strSearch = "[LastName] Like '" & [txtSearchName].Text & "*'"</pre>

    Of course, you will need to substitute the control and field names you use to make this code work.

  6. #6
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search Function (Acess 2000)

    OK . I was hoping to just use QueryDef's but that is OK. I am using ADO Object, so I will have to use the correct syntax for ADO. Thanks for the help

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Search Function (Acess 2000)

    Before you start getting excited about using a bound form with ADO, let me warn you that forms bound to ADO recordsets are not updateable in MDBs. If you're going to use ADO, then you'll have to use an unbound form unless you only want to view the data but not change it.
    Charlotte

  8. #8
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search Function (Acess 2000)

    OK, I will use your advice and change to DAO Objects. <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Search Function (Acess 2000)

    As long as the field is indexed it should be relatively quick, it does not need to be the PK.

    Hans approach to limit the choices is a speedy workaround, I have been forced to use this in the past on FoxPro tables.

    Pat

Posting Permissions

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