Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Oct 2005
    Location
    Kansas City, Missouri
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    search box to display multiple records (2000)

    Hi all,

    I have added a name search combo box to my contacts form and a company search combo box. The name search works perfect. It pulls up the exact record based on the last name seach. The company search box is displaying "to specific". In other words, it is displaying the exact record I search on. To back up a little bit and explain...I would like to base the search results for all records matching a particular company to be displayed. However its pulling up a single record tied to the ContactID and not CompanyName.

    I created the search box using the wizard and here is the code in the after update event:

    Private Sub CompanySearch_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ContactID] = " & Str(Nz(Me![CompanySearch], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End Sub


    Im thinking it involves a wild character in there somewhere but not sure where...or if... Any help would be most appreciated.

    Thanks

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

    Re: search box to display multiple records (2000)

    Are there several records with a different ContactID but the same CompanyName?

  3. #3
    Lounger
    Join Date
    Oct 2005
    Location
    Kansas City, Missouri
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: search box to display multiple records (2000)

    The contactID is the primary key, so we would have 1 contact with one address/company but serveral different contacts could be listed under the same company. So if I was to look up Adaptive Ecosystems, Inc. I would see there are 5 contacts in my database with that company.

    Shirley

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

    Re: search box to display multiple records (2000)

    In that case, ContactID should't be in the row source of the combo box, just the company name, or perhaps a company ID if there is one.

    The FindFirst method used in the code makes the form jump to the first record that matches the value of the combo box. You can change the code to filter the form instead of jumping to a record:

    Private Sub CompanySearch_AfterUpdate()
    Me.Filter = "[CompanyName] = " & Chr(34) & Me.CompanySearch & Chr(34)
    Me.FilterOn = True
    End Sub

Posting Permissions

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