New to programming in MS access 2007...

[DarrenReeder]

Hello, i am new to progrmaming in MS access 2007 although i have some basic programming in C++, Java, Pawn and some web design languages (PHP, xHTML, CSS, Javascript).

for my database i need to make it so that when i enter the Product Name from a dropdown menu it gets the product ID from another table.. (so, I choose product name "shoe" then it queries the "qry_Products" table for the product with the name as "shoe" and then gets the ID.."PA01" for example... and puts it into the Product_ID in the original table where i chose the name from the drop down menu... )



Im pretty sure i need programming in access to do this or maybe even macros?... Ive already made a query which can do this for all my records although i have to run it manually and this causes problems..

-thanks

[pbaldy]

You shouldn't need code. You can include both fields in the combo box row source. It's fairly common to have the combo display the name but actually save the ID.

[DarrenReeder]

So , would it be better to have the dropdown box to say "shoe" for example but then change that field to the foreign key?

[pbaldy]

Generally speaking when working with related tables, you save the key value. In this situation, you might have a products table with info about products and a sales table with info about each sale. In the sales table, you would only store the product ID, not its name. In that situation, a combo might have both ID and name in the row source, with ID hidden but the bound column. That means the user will see the name, but the ID is what gets saved to the sales table.

[DarrenReeder]

So, it will say "shoe" in the drop down menu but once it is selected it will say "PA01" ?? (for example)...

OR would you still see the word "shoe" on that field?? i understand what your saying and it sounds like its what i need to do for normalization..

[pbaldy]

The user will always see "shoe" in that scenario, but that's just one way to do it. You could display both to the user while the combo is dropped down, and either one of the values when it isn't. It depends on your specific needs.