Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Go to records as user types (Office 2002)

    Is it possible to set a text box as a sort of filter, so as a user types in, say, a person's last name, it will go to that record on the form?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

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

    Re: Go to records as user types (Office 2002)

    See <post#=452798>post 452798</post#>

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Go to records as user types (Office 2002)

    Hans,
    Worked like a charm. Throwing a wrench in the gears, is there a way to hold that filter and apply even more search criteria? Say my first filter was for last name, now i want to add first name?

    Jeremy
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Go to records as user types (Office 2002)

    Do you have a unique identifier for people, such as a Person ID?

    To achieve what you describe, I would use a combo box which has PersonID in the first column, with the column width set to 0, and the second column displaying a concatenation of surname and firstname , with a space or comma between them.

    In the afterupdate event find the record based on the hidden identifier, rather than the surname or first name.
    Regards
    John



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

    Re: Go to records as user types (Office 2002)

    In the specific situation you mention, I would use John Hutchison's suggestion. If the fields aren't related directly, you could use two text boxes and assemble a where-condition string based on both, connected with AND, in the On change event of each of them.

    Note: you must refer to the .Text property of the text box that has the focus, and to the .Value property of the other text box.

  6. #6
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Go to records as user types (Office 2002)

    John's idea would work beautifully, however I have over 1000 people with loads of personal info. It's hard to scroll through 1000 names to find the one I want, you'd be amazed at how many doubles there are. Plus, if somebody is a Smith II or Smith IV, I'd like to just put smith, then go to Joe or John, etc. I'll fiddionle around with your recommendation. I hope I get it right.

    R/
    Jeremy
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Go to records as user types (Office 2002)

    If you type into a combo box it automatically takes you to the first matching record as you type. So there should not be a need for much scrolling.

    So if you type Smith it will take you to the first smith, then you can scroll through the smiths if you need to.
    Regards
    John



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

    Re: Go to records as user types (Office 2002)

    See MSKB articles ACC2000: How to Synchronize Two Combo Boxes on a Form and ACC2000: How to Create Synchronized Combo Boxes, or do a search for cascading combo boxes in this forum.

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Go to records as user types (Office 2002)

    I found a form I have used that does something close to what you describe.
    Regards
    John



  10. #10
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Go to records as user types (Office 2002)

    Gentlemen,
    Thank you very much for your help. Hans, I'm still trying to assemble the line of code to have two separate text boxes, but the combo box works great. Thanks again

    Jeremy
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Go to records as user types (Office 2002)

    Actually, I've used that approach on tables of people with up to 30,000 records, and while it's not recommended, it does seem to work reasonably well. The down side is that you have to type the full last name before you can begin to restrict based on the first name. With big tables, you might well have over a 1000 Smiths to look through. In those cases we use a form that contains a criteria for the Last Name and one for the first name, and let people put however much in they want. We've used that successfully with 100K plus record tables.
    Wendell

Posting Permissions

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