I am having a hard time finding the answer to what I think should be an easy problem.
I have a Products table which contains Product_id and Description. I have two fields called, you guessed it, Product ID and Product Description. I have made a drop-down list for Products listing all of my products that my end user can choose from. When they choose a product, it automatically fills in the appropriate product number in the Product Number field. All is good.
However, upon watching my end user use the program I find that they have memorized the product numbers and prefer to tab into the Product ID field and fill in the product by typing just the product number then hitting enter expecting the product description to update.
So, how can I have a vice-versa situation? Have a drop-down in the Product ID field listing Product numbers (I will use auto complete) that when a number is chosen or filled in the correct product is filled in for the Product Description Field and then the other way around. When a product is chosen in the Product Description field pull-down, the correct product number is filled in to the Product ID field.
Having two relationships seems to kill the functionality.