Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to find a record by typing the first letters

    How to find a record by typing
    the first letters

    I have a table called Customers and a form called frmCustomers and i find
    a customer on the form through its ID number for examplle
    Dim strCustomerID As String
    Dim strBookmark As String
    strBookmark = Me.Bookmark
    strCustomerID = InputBox("Please enter Customer number ")
    Me.RecordsetClone.FindFirst "CustomerID = " & strCustomerID
    If Me.RecordsetClone.NoMatch Then
    MsgBox "CustomerID " & strCustomerID & " Not Found!!"
    Me.Bookmark = strBookmark
    Else
    Me.Bookmark = Me.RecordsetClone.Bookmark
    End If
    However i want to find the customer through writing the first letters of its CompanyName and choose the right customer from a dropped list of the CompanyNames with the closest letters.Is it posible?

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

    Re: How to find a record by typing the first letters

    You can't do it with an inputbox, but the matching feature is built into Access comboboxes. Put an unbound combobox on your form. Set its limit to list and autoexpand properties to true. Make its rowsource a saved query that will return a list of CustomerIds. In the AfterUpdate event of the combobox, put your code to find the matching record, except use something like this:

    <pre>Me.RecordsetClone.FindFirst "CustomerID = " & cboCustomerID</pre>


    so that you're telling it to find the first that matches the value in the combobox.
    Charlotte

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to find a record by typing the first letters

    You may already have everything working, but check out this <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=acc&Number=6857&page=&v iew=&sb=&vc=1#Post6857>post</A> for another great solution. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Indiana, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to find a record by typing the first letters

    You can also do what Charlotte recommends, but in Row Source, put this SQL:

    SELECT DISTINCTROW tblCustomer.strCustomerID, tblCustomer.strCompanyName FROM tblYourTable ORDER BY tblCustomer.strCompanyName;

    The dropdown list will let you type in the first few letters and will go to the first Company Name that matches the letters in the list. Then just click on the Customer Name you need and it will open that record.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to find a record by typing the first letters

    Using a combo box with the limittolist set true could be a way to go. However if you have a large number of entries then be prepared for performance problems.
    Could the company name be duplicated? (Company XYZ in town A and town [img]/forums/images/smilies/cool.gif[/img].
    Would you want to provide the search based on either ID or Company name? If so you could use 2 combo boxs to help the user select the customer by responding to the box the user performs a selection in.

Posting Permissions

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