Multiple Combo Box Filtering

[Prufrock]

I am developing a large database for our organisation to store client information. All clients have a Unique Record Number (URNumber). Trouble is that a lot of staff will not remember that. So hence my dilemma. I have a preview form that provides the snapshot of data they need instantly from the database. If they select the URNumber from a combo box (which does display first and last name) they could scroll forever and find the person that way but they won't. If they know the URNumber and start typing it of course it autocompletes and when they tab out all the relevant data shows in the preview form.

I have put two other combo boxes next to URNumber. They provide lists of Last Name and First Name. What I would like is a situation where they will usually know the last name. If they type that in it autocompletes. Then I want the first name combo to filter and only show the first names relevant to that last name. Once that is selected then the URNumber should autoupdate to correct number.

Now there may be far more efficient ways to achieve this. I am open to suggestions. Can I get the combo boxes filtering each other or is there a far more efficient way to cater for staff who will not know the urnumber and want to select by last name (and of course there will be duplicates at times so hence first name as well.)

Thanks

 

[Pampers]

You can also have a multicolumn combobox, showing lastname, firstname, urnumber....

To have one combo filtering the other, you can use the first combo box to set the criterium for the second (use query builder on the second combo and then refer to the first combo in the criterium field)



Pampers
Keeping it simple can be complicated