Quick Way to Find All Records with same last name

[dbs_help]

I have designed a data entry form in MS Access 2003. One function I find frustrating is when I want to find a particular record while using this entry form. If I use the Edit>Find facility, for instance, looking for someone with the last name "Jones", eg, I must page through all the Joneses in the database by using either the "Next" button or by pressing the "Enter" key. This works but if I pass by the Jones I am looking for, I have to go all the way back to the first record and then slowly repeat the above process to find the Jones record I was looking for in the first place.

I am wondering if there is some VBA code somewhere that would pop up a table, for example, of all the Jones records in my database. Then I could simply click on the Jones' record of interest in this pop up table and go directly to this record in my data entry form.

Thanks in advance for any help you can give me.

 

[wware]

I have one really simple trick I like to use. One of the choices for the Combobox wizard is "I want the combo box to look up the values in a table or query." Super easy to throw one of these on the form and it gives a lot of bang for the buck.

Make sure the query in the combobox's rowsource sorts by last name so you will see all the Joneses together. You probably want the combobox's "Autoexpand" property set to "yes" and use "List Rows" to display a lot of rows at once for ease of use.

See below link - scan down to "The Friendly Combo Box way to find"

http://custom-software.biz/AccessWizard/Nov05.htm

 

[dbs_help]

Quick way to find a name in a form

Absolutely loved your suggestion about the combo box idea!! Of course, you know you created a monster with that suggestion. I would now like to be able to click in the drop-down record of choice and by doing that automatically go to the full display of that person in the form view. Is that possible?

Thanks again!

 

[wware]

Not sure I understand you. It should work that way automatically. That's exactly what it does - loads the record onto the form (assuming you have a regular form with the table as its recordsource.) I've thrown it onto forms and it just works beautifully. Have you tried it yet? Perhaps you are already have the functionality you seek.

 

[dbs_help]

Easy Find Method

I have loaded a combo box on a form that has as its recordsource the table where all the records are contained. When I want to find someone, I type the last name of that person in the combo box and click on the down arrow to its right to display all the people with that last name.

When I double-click on the last name from the drop-down list nothing happens?? The form contents do not change.

 

[wware]

Oops, I think I gave you the wrong Wizard option to choose. You want

"Find a record on my form based on the value I selected in my combo box"

not

"I want the combo box to look up the values in a table or query."

My bad. My bad. Try this option. It should give you everything you want. Sorry for the confusion. Wendy

 

[dbs_help]

Quick Way to Find All Records with Same Last Name

Thanks for the feedback. You were right on with making a different choice in the combo wizard. Is there a way when you type the last name into the combo control to have the drop down list automatically sorted alphabetically or does one have to write some VBA code?

Thanks again

 

[wware]

Glad it is working for you. Add an "order by" clause to the row source property of the combo box. Wendy

 

[dbs_help]

Quick Way to Find All Records with Same Last Name

Thanks, Wendy. You're the best!