Go to records as user types (Office 2002)

Topic

New Reply

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?

Reply | Quote

Replies

See post 452798

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote