silly popup list - value list question.

[Steve S]

I have a database that goes a little like this.

table A is all my Suppliers

table B is all the Products

relationship is from PK_Supplier_ID on table A to FK_Supplier_ID on table B

on a layout for table B(products) I have a field that uses a pop-up list. The value list it refers to is one set to lookup values in the field PK_Supplier_ID on table A. It also displays and sorts from the field Supplier_Name also on Table A.

so. when I pull down the popup list I see something like

21 Abe's Hardware

132 Bananas Goods

65 Carrie's Cars

423 Donald's Doors

and so on. It has the supplier ID (which is what the relationship is based on) and the supplier name, but it is sorted by the name, as I specified in the valuelist.

heres the problem. I want to be able to enter the field, and hit the B key to jump to the B's, or the S key to jump to the S's. As it is now, it will work fine if I hit a number key, because the Supplier Id field is first and is always a number. But I would be happy not even having that number shown, or even happier having the order reveresed and the number showing up second.

Now, I can creat a couple To's with the relationship based on the supplier_name fields, but this I do not want to do. Data Entry is often innacurate and I want room to make revisions. The name may change, the ID never will.

Any ideas? am I missing something obvious?

[BrentHedden]

If you are baseing a relationship off of what is selected from this value list, I would highly suggest keeping that number in the list. Basing key fields off of real-world data is typically a bad idea, unless you can absolutely be positive that there wont be any two "Donald's Doors". But like you said, data entry is "often inaccurate".

I beleive that if you view a pull-down based on two values, entering anything from the keyboard will "jump" according to the first value only, just like you mentioned. And once you select which one you want, the field is set to the first value (the ID# in this case), not the second. So switching the two around in the value list would do nothing.

This may be more than what you want to do, but here is one idea - make the serial id# partially based off of the name. For example, the ID# for "Donald's Doors" would be DON00042 The number portion could be based off of some other field, maybe the telephone #. There is a very, very, very slim possibility that two records would have the same ID#. Even if that does happen, there are ways around it. Now you can use the Pull-down keyboard jumps in a better way.

[Steve S]

Thanks Brent. a couple things been thinking.

You suggestion is a good one, but for me no good for two reasons. This data has been in use and is still used outside of the database. Changing the existing Supplier_ID info would be a big task. Second, though your suggestion will nearly promise no duplicate values in my PK, it doesnt prevent the PK from changing if a user changes the name of the Supplier_Name.

And, yes you are right, that if the field order was reversed in the value list, that it would still enter the number value into the feild. But that would be fine with me. I'd be happy with it putting a 21 in the field as long as I can select it by hitting A for Abe's Hardware. I'd have the related fields below it showing the name and any other info anyway.

I'm just thinking that this shouldnt be so tricky. But, thats the way of these things

thanks again!